syfyfan1980
New Member
- Joined
- Apr 28, 2020
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
I am new to writing / recording macros and have had some success with simple macros. My macro below works by coping all the cells in a row on sheet2 to cells on sheet1. I recorded the macro using relative referencing as I need to repeat the macro for the data in rows 2, 3, 4 etc in sheet2. The cells being copied to on sheet1 are not all in the same row but are in different rows and columns making most cut and paste macros useless. To keep things simple, I created a sample spreadsheet that does the same thing as my working spreadsheet. The real spreadsheet has several hundred cells being copied and my sample has 6, but same theory.
Can someone in this group provide me with an example of code that will repeat my relative copy paste macro row by row until there is a blank row in sheet2 ?
Thanks in advance,
Syfyfan1980
The macro below copies my data correctly and when I highlight the first cell in row 2 in sheet2 and run the macro again, it correctly copies the data into the second repeated section on sheet1. I just to automate the re-running of the macro.
================================================
Sub Macro11()
'
' Macro11 Macro
'
'
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Sheets("Sheet2").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveSheet.Paste Link:=True
Sheets("Sheet2").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveCell.Offset(-1, 2).Range("A1").Select
ActiveSheet.Paste Link:=True
Sheets("Sheet2").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste Link:=True
Sheets("Sheet2").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste Link:=True
Sheets("Sheet2").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste Link:=True
Range("B6").Select
End Sub
Can someone in this group provide me with an example of code that will repeat my relative copy paste macro row by row until there is a blank row in sheet2 ?
Thanks in advance,
Syfyfan1980
The macro below copies my data correctly and when I highlight the first cell in row 2 in sheet2 and run the macro again, it correctly copies the data into the second repeated section on sheet1. I just to automate the re-running of the macro.
================================================
Sub Macro11()
'
' Macro11 Macro
'
'
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Sheets("Sheet2").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveSheet.Paste Link:=True
Sheets("Sheet2").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveCell.Offset(-1, 2).Range("A1").Select
ActiveSheet.Paste Link:=True
Sheets("Sheet2").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste Link:=True
Sheets("Sheet2").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste Link:=True
Sheets("Sheet2").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste Link:=True
Range("B6").Select
End Sub