Macro to copy a specified range to a variable range
 

I have a spreadsheet with data in Sheet 2 Range (A4:K6) that I want to
copy to Sheet 2 Range (A7:K*) (* being the variable that will change
each time the macro is run. The * is determined by the number of rows
of data in Sheet 1). I have accomplished this task by using loops, but
it takes too long to complete the macro. I am now trying to have the
data copied to the specified range in one paste.

Sean

Macro to copy a specified range to a variable range
 

Hi Sean,

I'm afraid I'm not quite sure what your question is. If you're asking how
you can pass a dynamic range address then:

'Your loop to determine last row - Last row = X

Dim sRange As String

sRange = "A7:K" & X

Wks.("Sheet2").Range(sRange)......

If you're asking how to find the last row then you probably need to post
another question telling people on what basis you 'find' the last row (ie
blank "" or a particular value etc.)

Hope it's the first!

Best regards

John



"SWT" wrote in message
ups.com...
I have a spreadsheet with data in Sheet 2 Range (A4:K6) that I want to
copy to Sheet 2 Range (A7:K*) (* being the variable that will change
each time the macro is run. The * is determined by the number of rows
of data in Sheet 1). I have accomplished this task by using loops, but
it takes too long to complete the macro. I am now trying to have the
data copied to the specified range in one paste.

Sean

Macro to copy a specified range to a variable range
 

To determine how big the range will be for pasting the copied cells, I
hope to use a cell in sheet 2 to return the number of rows in Sheet 1's
data. (using counta on Sheet 1's column C data). I then have to take
the result of that counta and multiply it by 3 and then plus 3, so if
there are 100 rows of data in Sheet 1 column C, the cell in Sheet 2
with the counta formula would return 303.

So, how do i get the X variable in your above suggestion to return the
value of 303, or whatever the counta formula returns?, or better yet,
is there a way to use VBA to get the 303 vs. using a counta function
within sheet 2

Sean

Macro to copy a specified range to a variable range
 

Thaks, this is the final macro I used which works as I had hoped.

Sub QB_Format()
'
' QB_Format Macro
' Macro recorded 10/19/2005 by SWT
' Copies rows 4 through 6 and pastes to varying rows

x = Range("Count").Value
sRange = "A7:K" & x
Range("A4:K6").Copy Destination:=Range(sRange)

End Sub

Macro to copy a specified range to a variable range
 

I revised the macro so that i didn't have to have the counta function
in the sheet:

Sub QB_Format()
'
' QB_Format Macro
' Macro recorded 10/19/2005 by SWT
' Copies rows 4 through 6 and pastes to varying rows
R = WorksheetFunction.CountA(Range("M_Amount"))
S = (R * 3) + 3
sRange = "A7:K" & S
Range("A4:K6").Copy Destination:=Range(sRange)