easy way to copy RangeX to RangeY

[aaron.kempf]

hey since you guys spend all your time running around copying data from
one worksheet to another

im just curious.

Is there an easy way to automate this?

Like-- without coding; is there an easy way to say 'take this range and
push it here' whenever i push a button?

I know vba like the back of my hand. I just want to find a non-coding
solution for a Jr Spreadsheet friend.

if there isn't an easy way to do this; I'd like to find out if MS has
considered adding something like this in a future version of Excel?

Call them Action Queries if you want-- i dont care.. Just allow you to
grab a range and push it here; Grab a range and push it there.. but
make sure that you're allowed to grab

A:F and push it to A and G:H

you know what i mean?

this kinda stuff is EASY in access. I just dont want to write 20 pages
of code to do this.

-Aaron

 

[Zack Barresse]

Hi Aaron,

Something like this maybe? ...

1) Select Range to receive data (assuming equal in size to the range being
'pushed')
2) Press = sign
3) Select (with mouse or keyboard) entire (equal) range being 'pushed'
4) Press Ctrl + Enter, to confirm for entire range.

Note that if you are doing this between two workbooks the default is using
absolute references, but if same workbook the references will be relative.

Hope I understood you correctly.

 

[Bruno Campanini]

this kinda stuff is EASY in access. I just dont want to write 20 pages
of code to do this.

-Aaron

-----------------------------
Sub Button43_Click()
Dim SourceRange As Range, TargetRange As Range
Set SourceRange = [F165:F175]
Set TargetRange = [G165:G175]
TargetRange = SourceRange.Value
End Sub

 

[aaron.kempf]

nah.. i want to automate that.

I dont want to copy and paste.

I want to write somethign.. just like a little append query in Access
to move data from one point to another.

And then when I need to do this again; i just call that procedure
again.

Because I'm sick and tired of copying this by hand.


And I want to take stuff from one range and split it-- effectively put
the first 4 columns in one range and the next 2 columns in another
range.

But I dont want to do this in VBA. I mean-- Access gives you queries
that you write once and then run multiple times.

I'm just tired of copying and pasting stuff by hand. I mean-- why
doesn't Excel have somethign that looks JUST LIKE append queries that
will take stuff from one range and push it into another?

it's super easy to write append queries-- it's all drag and drop.

I just want to get some clue as to how you guys use that app. I mean..
cutting and pasting is too error prone to do this same type of thign
over and over and over again.

I'm fine with writing vba to do this.

I just want somethign more robust-- maybe just a procedure that takes
one range and writes it to another. Maybe that's all i need to write.

I'd just prefer to get some grasp of how you super-spreadsheet-folk do
this

-Aaron

 

[Zack Barresse]

Well, as Bruno posted, it's easy enough with VBA. Either that or a few
clicks of your mouse. It's actually NOT that difficult, at least not as
difficult as you are portraying. You just need to be clear on how you want
to do it. First you say 'no VBA', then you say 'VBA ok'. It gets
confusing.

 

[Biff]

Don't even waste your time mucking around with Excel.

Use a database!

Biff

 

[JE McGimpsey]

Never feed the trolls...

 

[aaron.kempf]

well i'd like to do something that is no VB.

but if there isn't anything obvious to help to copy cells around; then
i'll have to program.

I just think that it's funny that you guys think that Excel is even a
useable functions.

copy and paste away until you break your wrists I dont care

 

[Harlan Grove]

(e-mail address removed) wrote...

well i'd like to do something that is no VB.

but if there isn't anything obvious to help to copy cells around; then
i'll have to program.

I just think that it's funny that you guys think that Excel is even a
useable functions.

copy and paste away until you break your wrists I dont care

Just for the heck of it, if you're doing data entry in Access, is there
any way to make [Ctrl]+C enter Chicago, IL, [Ctrl]+L enter Los Angeles,
CA and [Ctrl]+N enter New York, NY without macros/VBA?

 

[aaron.kempf]

yeah it's called a combobox; you dont need the CTRL

and yes-- MACROS-- as in multiple choice-- can do AUTOKEYS whatever you
want.

 

[Harlan Grove]

(e-mail address removed) wrote...

yeah it's called a combobox; you dont need the CTRL

Gee, just like data validation lists in Excel?

And if you have hundreds of possible cities, you get to scroll through
those lovely comboboxes? And if you start by typing an L, it'll take
you to Las Vegas before Los Angeles? So you'd need to type most of Los
Angeles before it'll successfully autocomplete? And God help you if one
of the cities in your list happens to be Los Angeles, AZ?

More cluelessness from you. I meant what I wrote - shortcut keys.

and yes-- MACROS-- as in multiple choice-- can do AUTOKEYS whatever you
want.

How? Details.

 

[aaron.kempf]

god harlan

data validation lists in Excel AREN'T as powerful.

i mean.. seriously

I want to do this in excel:

have a form, where I can choose a product cat, a vendor, and a type.. i
can choose one or two without choosing the 3rd, i can choose 2 and 3
without choosing the 3rd.

I can filter in 1, 2 and 3 and only return records that contain the
word 'ANCHOR'.

I mean-- Excel isn't even a real program.

It's for kids.
Product Category
Vendor
Product Type
Product Name

 

[Harlan Grove]

(e-mail address removed) wrote...

data validation lists in Excel AREN'T as powerful.

....

I'll stipulate that.

However, my original question was how to configure shortcut keys for
entering specific cities: Chicago, IL, Los Angeles, CA and New York,
NY, which, for reasons that must be a mystery to you, are rather more
frequetly needed that other city names in the US. You suggested a
combobox, and I pointed out that they'd be much LESS efficient for the
PRECISE task I stated.

So it takes programming in Access, too?

 

[aaron.kempf]

shortcut keys??

it's called a combobox
and it's much better in Access than it is in excel

programming? In Access?

Nothing requires programming in Access; i can do this with a MACRO--
again a macro in excel is all multiple choice-- definitely not
programming

Macros in Excel are similiar to Modules in Access.

Macros in access dont take any programming; and you can write a macro
called autokeys that will be a global keyboad handler.. if you wanted


and again-- for the record; it is EASY to bind a combobox in Access to
respond to various other things

you can have a combo box that will only include a certain state; only
contain certain cities-- you can bind a query to whatever you want to.

I just wish that excel had some apparatus for REAL data entry. Because
as it is; Excel is the harbringer of dirty data.

Excel is the root of all evil.

All i want is a non-programming way to copy cells from A5:C6 to K23:N69

yes.. that is from 3 columns to 4 columns


-aaron

 

[aaron.kempf]

and just for the record; you can always filter the combo box to say
whatever you want.. or you could ORDER THEM by a certain thing (so
making Chicago the first C for example)

that is just a simple order by clause


'oh, no i want Los Angeles to be the first L'
shit kids come on

i just dont understand how you people actually use this excel program;
what a PITA

 

[Harlan Grove]

(e-mail address removed) wrote...

shortcut keys??

it's called a combobox
and it's much better in Access than it is in excel

....

Obviously you can't understand the advantages of key combinations like
[Ctrl]+C for Chicago, IL, [Ctrl]+L for Los Angeles, CA and [Ctrl]+N for
New York, NY. Structuring a combo box to make these, respectively, the
first C, L or N wouldn't obvious to the average Excel or Access user.
The necessary ORDER BY clause would look like what?

Macros in access dont take any programming; and you can write a macro
called autokeys that will be a global keyboad handler.. if you wanted

....

Recorded macros in Excel (or Word) often don't require user
programming.

you can have a combo box that will only include a certain state; only
contain certain cities-- you can bind a query to whatever you want to.

....

The goal would be to include ALL cities, but make it as fast as
possible to enter the most frequently occurring cities.

All i want is a non-programming way to copy cells from A5:C6 to K23:N69

yes.. that is from 3 columns to 4 columns

So how would 3 columns become 4 columns? Or 6 cells become 188 cells?
Your question makes as little sense as most of your raving. But FTHOI,
if I had one table of 3 fields and 2 records, without giving you any
other specs, how would I INSERT it into another table of 4 fields so
that it produced 47 records presumably with no NULL cells?

 

[aaron.kempf]

i could insert 3 columns into 4 columns with an append query.. it's
easy

i dont think that having new york to be the first N-- i dont think that
would be that counterintuitive



i would just have a table named cities

CITYID, CITYNAME, SORTORDER
1, Seattle SE
2, Portland PO
3, Las Vegas LA
4, Los Angeles L_
5, New York N_
6, Chicago C_
7, Chatanooga CH

Then I would just

Select CityName

From Cities

ORDER BY SORTORDER, CITYNAME

That wouldn't take me more than a couple of minutes to write-- and that
way when people hit C it would first select Chicago.. then if they
started typing CHA it would change to chatanooga

I'm sure that there are better solutions than that.. but you get the
general idea

i just dont think that excel is as poweful as Access.

and I really dont care for Excel vba -- -- for a bunch of reasons

a) it is WAYYYYYYYYYYYYYYYYYYYYYY too easy to have 100 pages of excel
vba
b) it is WAYYYYYYYYYYYYYYYYYYYYYY too complex to have 100 pages of
excel vba

i just want more power from excel; i just dont think that it has the
functionality that it needs for me to use it on a daily basis.

i'm just tired of cutting and pasting.

and i mean.. people use excel for data entry all the time. that just
drives me crazy.. i mean-- you can't validate numbers, you can't DO
Anything with the numbers. Excel is just CRAP.

EXCEL IS CRAP
EXCEL IS CRAP
EXCEL IS CRAP
EXCEL IS CRAP

just a bigger excel mess every time i open it

and keyboard shortcuts are about 100 times more powerful in Access than
in Excel.

I'm sorry that you guys think that access is
soooooooooooooooooooooooooooooooooooo hard.

it's simple math tho

Dim You as ExcelDork
You.NumberCrunchingAbility = 10
You.HourlyRate = $12
You.TakeClass "Access1"
You.TakeClass "Access2"
You.TakeClass "Access3"

Debug.print You.NumberCrunchingAbility
= 10,000

Debug.print You.HourlyRate
= $40

my first computer job; they sat down 30 of us kids and taught us all
Access queries in about 20 minutes.

I mean-- DUH GUYS lose the training wheels

Excel just FAILED to meet my needs.

I dont want to copy and paste; and i dont want 100,000 copies of
similiar formulas.

I mean-- can't they give me a way to say 'try this relative formula for
this whole column' rather than 65k rows of the exact same thing (well,
it's not the exact same thing; and that's the problem).

 

[Harlan Grove]

(e-mail address removed) wrote...

i could insert 3 columns into 4 columns with an append query.. it's
easy

....

I don't doubt that it's easy, but it's ill-defined without specs.
without specs, entering NULL into each field in the destination table
is every bit as meaningful as entering the concatenation of the 3
fields in the source table into each of the 4 fields in the destination
table. Only one-to-one mappings with very similar field names are
obvious.

That wouldn't take me more than a couple of minutes to write-- and that
way when people hit C it would first select Chicago.. then if they
started typing CHA it would change to chatanooga

And you'd need a link to this table in every form in which someone
would want to use it. Not a bad thing. But now a complication. What if
there were multiple processing centers. People in Denver might want the
topmost S entry to be Salt Lake City, UT, while people in Boise might
want it to be Seattle, WA. If they use keyboard mapping, they could
customize key combinations which are most useful for their own
situations. Much easier than each of them writing their own table of
personal sort orders.

and I really dont care for Excel vba -- -- for a bunch of reasons

a) it is WAYYYYYYYYYYYYYYYYYYYYYY too easy to have 100 pages of excel
vba
b) it is WAYYYYYYYYYYYYYYYYYYYYYY too complex to have 100 pages of
excel vba

More proof you don't know what you're doing. If you leave Excel's macro
recorder on during a long session, you can get VERY LONG macros. If you
change a single print setting while the macro recorder is on, it
records not only the setting that changed but all the other settings.
It's less than ideal. (Lotus Symphony's keystroke recorder was the last
minimal recorder that shipped with a spreadsheet.)

But real developers only use recorded macros as a starting point, and
they only record short bits at a time. Then they parametrize the
recorded macros and write macros that stitch together the operations
they need to perform in a logical and EFFICIENT manner. If you don't do
that, it's not Excel or VBA that's to blame.

i just want more power from excel; i just dont think that it has the
functionality that it needs for me to use it on a daily basis.

....

Because you may not need the functionality it offers.

and i mean.. people use excel for data entry all the time. that just
drives me crazy.. i mean-- you can't validate numbers, you can't DO
Anything with the numbers. Excel is just CRAP.

Batch validation after entry. Using formulas. Not difficult.

my first computer job; they sat down 30 of us kids and taught us all
Access queries in about 20 minutes.

....

Never had a 'computer job'. All the jobs I've had have involved
computers, but I've never been in MIS/DP/IT, never been exclusively a
programmer, never used only one type of computer (I've always had jobs
that have required some mainframe SAS use/programming).

Excel just FAILED to meet my needs.

And so you believe it fails to meet anyone else's needs?!

I mean-- can't they give me a way to say 'try this relative formula for
this whole column' rather than 65k rows of the exact same thing (well,
it's not the exact same thing; and that's the problem).

Multidimensional modeling software like Lotus Improv (defunct) and
Quantrix (currently available) have been available since the mid 1980s.
They never caught on. Can you figure out why not?


Spreadsheets *are* poor tools to use for canned reports unless the
reports are very simple and require very little data which comes from
outside sources.

Most large companies use some form of OLAP software, but most of those
are definitely not end user tools.

There's a need for ad hoc calculations, and spreadsheets have proven to
be the most efficient tool for them for most computer users. Trouble
comes when ad hoc models become widely and regularly used. Even then,
it's possible to design robust and efficient spreadsheet models, but
few spreadsheet users have the broader programming skills to know how
to do it. That's why there are a lot of poor quality spreadsheet models
in use out in the wide world. But spreadsheets are still very good ad
hoc calculation tools.

To repeat yet again, use the best tool for the task. That doesn't
always mean spreadsheets, but it also doesn't always mean databases.