I have been banging my head through a wall trying to figure this thing out, so I am not reaching out for help. Here is what I need to do:
I have to copy company names in a range of cells to another worksheet and once pasted, then copy another row and paste it below. The hard part for me here is cycling through the company names until I reach the end. Another difficult part is need to offset by a row for each company for the final paste.
Below is what I have so far
Dim counter As Long
For counter = 1 To Comps - 1
'Comp companies copy
Sheets("Control Page").Select
Range("CompStart").Select
Selection.Copy
'Paste the company name in the B6
Sheets("Competitor Breakout").Select
Range("B6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Copy row 2 where the data resides and paste below
Sheets("Comps Analysis").Select
Range("2:2").Select
Selection.Copy
Range("8:8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Stop loop when complete
If counter = Comps Then
Exit For
End If
Next counter
Bookmarks