PDA

View Full Version : [SOLVED] Is there a way to autofill macros?



roxnoxsox
02-05-2015, 05:34 AM
This may sound like a stupid question but basically-

I have a spreadsheet where if a button is pressed, it will show cell A1 of sheet2 in cell G1 of Sheet1.
The code I'm using to do this is:
Sub CopyData()
Sheets("Sheet2").Range("A1").Copy Sheets("Sheet1").Range("G1")
End Sub


However, I need this to work for looooots of other rows. So for example, on the next row, I need cell A2 of sheet2 to show in cell G2 of Sheet1. Is there a way of doing this without retyping it each time? I realise it's a difficult problem and might not be at all possible but worth a shot!

Bob Phillips
02-05-2015, 06:22 AM
Sub CopyData()
Dim i As Long

With Sheets("Sheet2")

For i = 1 To 99 ' change 99 to your limit

.Cells(i, "A").Copy Worksheets("Sheet1").Cells(i, "G")
Next i
End With
End Sub

snb
02-05-2015, 10:12 AM
You can link the cells in sheet2 to the cells in Sheet1:


Sub M_snb()
Sheet1.Cells(1).CurrentRegion.Copy

Sheet2.Activate
Sheet2.Range("G1").Select
Sheet2.Paste , True
End Sub

roxnoxsox
02-06-2015, 04:37 AM
Ace suggestions, helped a lot :) Thanks!