NUTSUECOW
11-26-2018, 01:41 PM
I need to run this series of events 40 times. after each time it runs, range J1 needs to go down to J2. And the paste portion at the bottom needs to move from M30 to M40. Repeating to J3, and M50 ect..... 40 times. I'm not sure if I could use the match somehow to help with this. Having trouble wrapping my head around this. any help would be appreciated.
23271
Sub Copy_Paste()
'
' OT_REFRESH_EXPORT Macro
'
Worksheets("Data").Activate
If Range("J1").Value = "Package1" Then
Range("$P$2").Select
ActiveCell.Offset(, 1).Resize(1, 10).Copy
ElseIf Range("J1").Value = "Package2" Then
Range("$P$4").Select
ActiveCell.Offset(, 1).Resize(1, 10).Copy
ElseIf Range("J1").Value = "Package3" Then
Range("$P$6").Select
ActiveCell.Offset(, 1).Resize(1, 10).Copy
ElseIf Range("J1").Value = "Package4" Then
Range("$P$8").Select
ActiveCell.Offset(, 1).Resize(1, 10).Copy
ElseIf Range("J1").Value = "Package5" Then
Range("$P$10").Select
ActiveCell.Offset(, 1).Resize(1, 10).Copy
ElseIf Range("J1").Value = "Package6" Then
Range("$P$12").Select
ActiveCell.Offset(, 1).Resize(1, 10).Copy
ElseIf Range("J1").Value = "Package7" Then
Range("$P$14").Select
ActiveCell.Offset(, 1).Resize(1, 10).Copy
ElseIf Range("J1").Value = "Package8" Then
Range("$P$16").Select
ActiveCell.Offset(, 1).Resize(1, 10).Copy
ElseIf Range("J1").Value = "Package9" Then
Range("$P$18").Select
ActiveCell.Offset(, 1).Resize(1, 10).Copy
ElseIf Range("J1").Value = "Package10" Then
Range("$P$20").Select
ActiveCell.Offset(, 1).Resize(1, 10).Copy
ElseIf Range("J1").Value = "Package11" Then
Range("$P$22").Select
ActiveCell.Offset(, 1).Resize(1, 10).Copy
ElseIf Range("J1").Value = "Package12" Then
Range("$P$24").Select
ActiveCell.Offset(, 1).Resize(1, 10).Copy
ElseIf Range("J1").Value = "Package13" Then
Range("$P$26").Select
ActiveCell.Offset(, 1).Resize(1, 10).Copy
End If
Range("M30").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
END SUB
23271
Sub Copy_Paste()
'
' OT_REFRESH_EXPORT Macro
'
Worksheets("Data").Activate
If Range("J1").Value = "Package1" Then
Range("$P$2").Select
ActiveCell.Offset(, 1).Resize(1, 10).Copy
ElseIf Range("J1").Value = "Package2" Then
Range("$P$4").Select
ActiveCell.Offset(, 1).Resize(1, 10).Copy
ElseIf Range("J1").Value = "Package3" Then
Range("$P$6").Select
ActiveCell.Offset(, 1).Resize(1, 10).Copy
ElseIf Range("J1").Value = "Package4" Then
Range("$P$8").Select
ActiveCell.Offset(, 1).Resize(1, 10).Copy
ElseIf Range("J1").Value = "Package5" Then
Range("$P$10").Select
ActiveCell.Offset(, 1).Resize(1, 10).Copy
ElseIf Range("J1").Value = "Package6" Then
Range("$P$12").Select
ActiveCell.Offset(, 1).Resize(1, 10).Copy
ElseIf Range("J1").Value = "Package7" Then
Range("$P$14").Select
ActiveCell.Offset(, 1).Resize(1, 10).Copy
ElseIf Range("J1").Value = "Package8" Then
Range("$P$16").Select
ActiveCell.Offset(, 1).Resize(1, 10).Copy
ElseIf Range("J1").Value = "Package9" Then
Range("$P$18").Select
ActiveCell.Offset(, 1).Resize(1, 10).Copy
ElseIf Range("J1").Value = "Package10" Then
Range("$P$20").Select
ActiveCell.Offset(, 1).Resize(1, 10).Copy
ElseIf Range("J1").Value = "Package11" Then
Range("$P$22").Select
ActiveCell.Offset(, 1).Resize(1, 10).Copy
ElseIf Range("J1").Value = "Package12" Then
Range("$P$24").Select
ActiveCell.Offset(, 1).Resize(1, 10).Copy
ElseIf Range("J1").Value = "Package13" Then
Range("$P$26").Select
ActiveCell.Offset(, 1).Resize(1, 10).Copy
End If
Range("M30").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
END SUB