data Transpose in VBA loop, choose the part I want to import!

[esther723]

[h=2]Hello I am working for my school project that I need to follow,

Step 1. Copy from original rep this year row (26:27)
Paste Transpose to data sheet > > Column range E9:F39
Paste Transpose to End report sheet > > Column range B11:C41

Step 2: Copy from original rep this year row (36:37)
Paste Transpose to data sheet > > Column range P9:O39
Paste Transpose to End report sheet > > Column range N11:O41

Step 3: Copy from original rep this year row (46:47)
Paste Transpose to data sheet > > Column range AA9:AB39
Paste Transpose to End report sheet > > Column range Z11:AA41

The question part is how to match the data into certain column since I don't need all of the data from the original report. Please help, thank you so so so much!

I hope this explains. Thanks.[/h]

 

[Andrew Poulsom]

If you want a macro, record one while performing the steps manually.

 

[esther723]

Run-time error 9 (subscript out of range) when running macro that I recorded, it shows Sheets("Data").Select as error, do you know how can I fix it? Thank you!

Sub Import()
'
' Import Macro
'


'
    Range("B26:AF27").Select
    Selection.Copy
    
    Sheets("Data").Select
    Range("E9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("Original Rep This Year").Select
  
    Range("B30:AF31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E48").Select
    Sheets("Data").Select
    Range("I9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("Original Rep This Year").Select
    Range("B36:AF37").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    Range("P9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("Original Rep This Year").Select
    
    Range("B40:AF41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    
    Range("T9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
   
    Sheets("Original Rep This Year").Select
    
    Range("B46:AF47").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    
    Range("AA9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
   
    Sheets("Original Rep This Year").Select
    
    Range("B50:AF51").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    
    
    Range("AE9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    
    Sheets("Original Rep This Year").Select
   
    Range("B28:AF28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    Range("K9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("Original Rep This Year").Select
    
    Range("B32:AF32").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    Range("L9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("Original Rep This Year").Select
    
    Range("B38:AF38").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    
    Range("V9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
   
    Sheets("Original Rep This Year").Select
    
    Range("B42:AF42").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    
    Range("W9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    
    Sheets("Original Rep This Year").Select
    
   
    Range("B48:AF48").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    
    Range("AG9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    
    Sheets("Original Rep This Year").Select
    
    Range("B52:AF52").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    
   
    Range("AH9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    
    Sheets("Original Rep Last Year").Select
    Range("B26:AF27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    Range("G9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("Original Rep Last Year").Select


    Range("B36:AF37").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    
    Range("R9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("Original Rep Last Year").Select
    
    Range("B46:AF47").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    
    Range("AC9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    
End Sub

 

[Andrew Poulsom]

Do you have a sheet named data in the active workbook?