Trying to get a macro to copy a range......

[craigo]

I am new to Excel Macros (had much experience with old Lotus 123 - not at all relevant!). I am trying to copy a range of formulas (say, a10:d10, sheet1) to another range (say, a10:dxx, sheet2) which may vary in number of rows. When I record the macro, it works great if I am using the same number of rows, but since the data changes, the macro doesn't work properly. I know it was a simple task in Lotus, so it must still be simple. I'm just too simple to get it. When I look at the macro, it almost appears Greek. Any help would be appreciated!
Thanks

 

[jindon]

Hi

Do you want to post your code here?

 

[craigo]

Here it is:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/30/2006 by Craigo
'

'
Range("A10:D10").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A10:D16").Select
ActiveSheet.Paste
End Sub

I need to copy the formula in all the rows created by a query. I can see that modifying the D16 will change the number of rows, but how can I get it to modify D16 according to the number of rows I've received?

Thanks

 

[Datsmart]

You can use a variable in your code to represent the "Number of Rows".
Like:

Sub Macro1()
Variable1 = Range("A1").Value
    Range("A10:D10").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A10:D" & Variable1 & "").Select
    ActiveSheet.Paste
End Sub

Cell A1 would hold the number of Rows you "received".

 

[Datsmart]

Actually, you can shorten your code a lot.

Sub Macro1()
Variable1 = Range("A1").Value
    Range("A10:D10").Copy Destination:=Sheets("Sheet2").Range("A10:D" & Variable1 & "")
End Sub

This does the same copy and paste without selecting anything.

 

[jindon]

craig,
you don't need to specify the same size of range when you paste.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/30/2006 by Craigo
'

'
Activesheet.Range("A10:D10").copy Sheets("Sheet2").Range("A10")
' or
'ActiveSheet.Range("a10").CurrentRegion.Copy Sheets("Sheet2").Range("a10")
End Sub