0

Help please.

I've recorded code that uses data from a source list, copies this data and pastes it into a new worksheet copied from a master sheet.

I want the code to then do the same thing but for the next row of data creating a new sheet etc whilst keeping the previous intact,

source list

copied sheet from mastersheet where data should enter

Sub Macro22() ' ' Macro22 Macro '

'

Sheets("Procurement Plan Master").Select
Sheets("Procurement Plan Master").Copy After:=Sheets(5)
Sheets("Procurement Evaluation Tool").Select
Range("C9").Select
ActiveCell.FormulaR1C1 = "Intrusive Fire Damper Survey"
Sheets("Procurement Plan Master (2)").Select
Sheets("Procurement Plan Master (2)").Name = "Intrusive Fire Damper Survey"
Sheets("Procurement Evaluation Tool").Select
Range("A9").Select
Selection.Copy
Sheets("Procurement Evaluation Tool").Select
Range("B9").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Intrusive Fire Damper Survey").Select
Range("M5").Select
ActiveSheet.Paste Link:=True
Sheets("Procurement Evaluation Tool").Select
Range("D9").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Procurement Evaluation Tool").Select
Range("E9").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Intrusive Fire Damper Survey").Select
Range("J5").Select
ActiveSheet.Paste Link:=True
Selection.Font.Bold = False
Sheets("Procurement Evaluation Tool").Select
Range("H9").Select
Selection.Copy
Sheets("Intrusive Fire Damper Survey").Select
Range("Q8").Select
ActiveSheet.Paste Link:=True
Sheets("Procurement Evaluation Tool").Select
Range("I9").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Intrusive Fire Damper Survey").Select
Range("O6").Select
ActiveSheet.Paste Link:=True
Sheets("Procurement Evaluation Tool").Select
Range("J9").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Intrusive Fire Damper Survey").Select
Range("P6").Select
ActiveSheet.Paste Link:=True
Sheets("Procurement Evaluation Tool").Select
Range("L9").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Intrusive Fire Damper Survey").Select
Range("O18").Select
ActiveSheet.Paste Link:=True
Sheets("Procurement Evaluation Tool").Select
Range("M9").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Intrusive Fire Damper Survey").Select
Range("P18").Select
ActiveSheet.Paste Link:=True
Sheets("Procurement Evaluation Tool").Select
Range("N9").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Intrusive Fire Damper Survey").Select
Range("Q6").Select
ActiveSheet.Paste Link:=True
Sheets("Procurement Evaluation Tool").Select
Range("O9").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Intrusive Fire Damper Survey").Select
Range("Q18").Select
ActiveSheet.Paste Link:=True
Sheets("Procurement Evaluation Tool").Select
Range("P9").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Intrusive Fire Damper Survey").Select
Range("P11").Select
ActiveSheet.Paste Link:=True
Sheets("Procurement Evaluation Tool").Select
Range("Q9").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Intrusive Fire Damper Survey").Select
Range("O11").Select
ActiveSheet.Paste Link:=True
Sheets("Procurement Evaluation Tool").Select
Range("S9").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Intrusive Fire Damper Survey").Select
Range("Q11").Select
ActiveSheet.Paste Link:=True
Sheets("Procurement Evaluation Tool").Select
Range("T9").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Intrusive Fire Damper Survey").Select
Range("O23").Select
ActiveSheet.Paste Link:=True
Sheets("Procurement Evaluation Tool").Select
Range("U9").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Intrusive Fire Damper Survey").Select
Range("P23").Select
ActiveSheet.Paste Link:=True
Sheets("Procurement Evaluation Tool").Select
Range("V9").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Intrusive Fire Damper Survey").Select
Range("Q23").Select
ActiveSheet.Paste Link:=True
Sheets("Procurement Evaluation Tool").Select

End Sub

Joe Clay
  • 33,401
  • 4
  • 85
  • 85
Newey
  • 1
  • You want to look into [loops](https://trumpexcel.com/vba-loops/) and [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Tim Stack Jun 25 '19 at 13:40
  • Thanks for the comment. I've looked at Loop but cant get see how this repeats. All I need is to be able to get the code above to apply to the next row then the next row etc etc – Newey Jun 26 '19 at 08:55
  • How about a `For i =... To ...` loop with i as the row number? – Tim Stack Jun 26 '19 at 08:56
  • I've spent time looking at Loop code but sadly found nothing that works. I want the loop to go to the next row and then create a new sheet as the code above does but without deleting the sheets created previously. This will continue row to row. – Newey Jun 26 '19 at 11:48

0 Answers0