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

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" <[email protected]> wrote in message
news:[email protected]...
>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
>

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

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

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)