I need to select a range of cells for copying/pasting
opperations but the range changes everytime new data is
imported into the worksheet. The 2 columns remain the
same but the rows vary. I can calculate the upper left
row number and store that value in a specific cell
location and likewise I can calculate the bottom right
row number and store that value in another specific cell
location, but I don't know how to write the macro code to
assign the values. ex: [range("BA$:CX#").select]
(where "$"= the value in cell AY1 and "#"= the value in
cell AY2. It probably is very simple but I keep getting
syntax errors.

The values are stored in Cells AY1 and AY2. Either syntax will work.

Range("BA" & [AY1] & ":CX" & [AY2]).Copy
Range("BA" & Range("AY1").Value & ":CX" & Range("AY2").Value).Copy

Tested using Excel 97SR2 on Windows 98SE,

HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------

I need to select a range of cells for copying/pasting
opperations but the range changes everytime new data is
imported into the worksheet. The 2 columns remain the
same but the rows vary. I can calculate the upper left
row number and store that value in a specific cell
location and likewise I can calculate the bottom right
row number and store that value in another specific cell
location, but I don't know how to write the macro code to
assign the values. ex: [range("BA$:CX#").select]
(where "$"= the value in cell AY1 and "#"= the value in
cell AY2. It probably is very simple but I keep getting
syntax errors.

Thank you for your response. Your code worked fine as long as there were
no values in ay1 or az1 but as soon as values were enytered and the
macro was run I got a "Run-time error '1004': Method'Range'of
Object'_Global'Failed" error message. Any suggestions? Thanks -- Ray



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

The only way that I could reproduce this error message was by having
one value (or both) greater than 65536 (the maximum number of rows).

HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------
Thank you for your response. Your code worked fine as long as there were
no values in ay1 or az1 but as soon as values were enytered and the
macro was run I got a "Run-time error '1004': Method'Range'of
Object'_Global'Failed" error message. Any suggestions? Thanks -- Ray

My fault, I had the wrong cell typed in the code. Thanks much. It works
great! Ray



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!