Excel Marcos

[Debi]

I am hoping someone here can help me out. I need to know
how to make my macro work for numerous files that have
different cell ranges.

Here is the problem:

File 1 has a range of (A2:A956)
File 2 has a range of (A2:A1200)
File 3 has a range of (A2:A1500)

All 3 files use the same marco, which was written to look
for the end of the data in the column (Shift:Ctrl:down
arrow). Problem is that it assigned the A2:A956 in the
Visual Basic file. So how do I change the marco to have
it find the end of the data on each of these 3 sheets???

Thanks a ton in advance for any help you can be with this.

Debi

 

[Jack Schitt]

As a general approach, I would be inclined to use a named range in each
workbook to refer to the desired range in that specific workbook. Call it
the same name in each workbook, and then refer to the range in the Macro
only by its name.

 

[Guest]

Problem is that I am dragging and dropping information
into the cells, ie copy cell A2 to A3:953(or whatever the
end of cell range is)and I don't think this will solve the
issue, will it???

 

[Jack Schitt]

It may be possible even so.
Say the range is called MyName and refers to
=OFFSET(A2,0,0,COUNTA($A:$A),1)
or something similar (may need to adjust the depth for header rows etc)

 

[Guest]

if you want to goto the last row:
Range("A65536").end(xlup).select
will put you on the last row
Range("A65536").end(xlup).offset(1,0)select
will put you one row below the last row.

 

[Jack Schitt]

Oops, that should read
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)

(You won't want to make the anchor cell reference a relative address in this
instance)

 

[Guest]

The data is being imported from another software query and
it changes each month and with each of the 15 queries. So
if I add to the marco to rename the column to a range name
will that work? Or can I just use the offset formula
without changing the column to a range??

 

[Jack Schitt]

I'm afraid you lost me there. Care to attempt rewording?

 

[Guest]

No, what I was trying to say was that the data that is
imported chances each month, and between the different
reports that use the macro. So I want to make the copy
paste statement defined as opposed to changing the column
to a range.

 

[Guest]

That doesn't work.

Here is the marco that I am trying to convert,
Range("C22").Select
Selection.Copy
Range("C3:C17").Select
ActiveSheet.Paste

I want the C17 to be what ever the last row is in the
spreadsheet. But there is no data in C for it to make
that determination, so it needs to look at column A to
determine the last row.

So how would I write that????

 

[JE McGimpsey]

One way:

Range("C22").Copy Range("C3" & _
Range("A" & Rows.Count).End(xlUp).Row)

Note that you almost never need to select a range in order to use it.
Using the range object directly makes your code smaller, faster, and,
IMHO, easier to maintain.

 

[Guest]

I think that might work.

Thanks so much!!!!

-----Original Message-----
One way:

Range("C22").Copy Range("C3" & _
Range("A" & Rows.Count).End(xlUp).Row)

Note that you almost never need to select a range in order to use it.
Using the range object directly makes your code smaller, faster, and,
IMHO, easier to maintain.



.