VBA copy multiple columns in one file and pasting in another

[Holley]

Hello all! Many many thanks for all of your past help. Reaching out again as I continue to find a roadblock when going between files.

The text file generates from a weekly report and the name will change each time. I run the code from the imported text file and the code works for the first copy, but when switching back to the original, it crashes when going to the original window. I receive a Run-time error '9'" Subscript out of range. This name (textfile.txt) comes from the text file that is imported. I have tried to rename the window, but it ends up renaming all.

Sub Copy_Original()
'
' Copy_Original Macro
'

'
    Range("A1:L1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Blank whole report.xlsm").Activate
    Range("A2").Select
    ActiveSheet.Paste
    Windows("textfile.txt").Activate
    Range("M1322").Select
    Selection.End(xlUp).Select
    Range("M1:T1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Blank whole report.xlsm").Activate
    Range("P2").Select
    ActiveSheet.Paste
    Application.Left = 238.75
    Application.Top = 98.5
End Sub

Someone may have a better suggestion than how I have it. But what I need is for all the data in columns A-L in spreadsheet 1 to be pasted into cell A2 in spreadsheet 2. And all the data on Spreadsheet 1 in columns M-T to be pasted into Spreadsheet 2 in P2.


Any help would be appreciated!!

 

[Alex Blakenburg]

At the moment you are pretty dependant on the Text file being the activeworkbook and that the sheet you want to copy it to is the active sheet in the other workbook.
I have assumed the code is in the workbook that you are copying it to.

Sub Copy_Original_Mod()
'
    Dim wbTxt As Workbook, wbXL As Workbook
    Dim shTxt As Worksheet, shXL As Worksheet
    Dim lastRowTxt As Long
    
    Set wbTxt = ActiveWorkbook
    Set shTxt = wbTxt.ActiveSheet
    
    Set wbXL = ThisWorkbook             ' Assuming Blank whole report.xlsm contains the code
    Set shXL = wbXL.ActiveSheet         ' A named sheet would be better
    
    lastRowTxt = shTxt.Range("A" & Rows.Count).End(xlUp).Row
    
    shTxt.Range("A1:L" & lastRowTxt).Copy Destination:=shXL.Range("A2")
    shTxt.Range("M1:T" & lastRowTxt).Copy Destination:=shXL.Range("P2")
    
    Application.CutCopyMode = False

End Sub

 

[Holley]

At the moment you are pretty dependant on the Text file being the activeworkbook and that the sheet you want to copy it to is the active sheet in the other workbook.
I have assumed the code is in the workbook that you are copying it to.

Sub Copy_Original_Mod()
'
    Dim wbTxt As Workbook, wbXL As Workbook
    Dim shTxt As Worksheet, shXL As Worksheet
    Dim lastRowTxt As Long
   
    Set wbTxt = ActiveWorkbook
    Set shTxt = wbTxt.ActiveSheet
   
    Set wbXL = ThisWorkbook             ' Assuming Blank whole report.xlsm contains the code
    Set shXL = wbXL.ActiveSheet         ' A named sheet would be better
   
    lastRowTxt = shTxt.Range("A" & Rows.Count).End(xlUp).Row
   
    shTxt.Range("A1:L" & lastRowTxt).Copy Destination:=shXL.Range("A2")
    shTxt.Range("M1:T" & lastRowTxt).Copy Destination:=shXL.Range("P2")
   
    Application.CutCopyMode = False

End Sub

You're correct about the assumptions! Thank you so very much for this!!

 

[Alex Blakenburg]

You're welcome. Glad I could help.