Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA help to copy variable range | Excel Discussion (Misc queries) | |||
How to copy&paste a variable range rows and colums | Excel Discussion (Misc queries) | |||
Locating variable range to copy | New Users to Excel | |||
Copy only Visible Cells of a Variable Range... | Excel Programming | |||
Copy/paste range variable between workbooks | Excel Programming |