How to copy range from one workbook to another workbook

Question

I would like to copy a range from two workbooks that I open from Outlook with extensions xls and xlsx. Into specific sheets of another excel workbook.

Below is the code I have. In bold is where my code breaks with a message "subscript out of range" ThisWorkbook.Worksheets("Sheet13").Range("A:AR").Paste

FYI the workbooks are opened prior to me running the script. The intended destination workbook is "Tag Up Workbook v4.0.0.xlsm"

Sub easyUpdate()
Application.ScreenUpdating = False
    'Remove formula from last row resolve and queue
    Dim rng As Range, cell As Range, R As Range
    Set rng = ThisWorkbook.Worksheets("Slide 1 Chart 1").Range("C:C").SpecialCells(xlCellTypeVisible)

    For Each R In rng
        If R.Value <> "" Then
            Set cell = R
        End If
    Next R

    cell.Value = cell.Value
    
        Set rng = ThisWorkbook.Worksheets("Slide 1 Chart 1").Range("D:D").SpecialCells(xlCellTypeVisible)

    For Each R In rng
        If R.Value <> "" Then
            Set cell = R
        End If
    Next R

    cell.Value = cell.Value
    
'clear source sheets and move data from current to previous source
ThisWorkbook.Worksheets("Previous Day Incident Source").Range("A:AR").ClearContents
Sheets("CCDR Source").Cells.Clear
Sheets("Handle Times Source").Cells.Clear
Sheets("Agent Detail Source").Cells.Clear
Sheets("Current NSF Request Source").Cells.Clear
ThisWorkbook.Worksheets("Tasks INC_REQ Source").Range("A:Q").ClearContents
Sheets("Queue Delta Source").Cells.Clear

ThisWorkbook.Worksheets("Current Incident Source").Range("A:AR").Copy Destination:=ThisWorkbook.Worksheets("Previous Day Incident Source").Range("A:AR")
ThisWorkbook.Worksheets("Current Incident Source").Range("A:AR").Copy
ThisWorkbook.Worksheets("Previous Day Incident Source").activate
ThisWorkbook.Worksheets("Previous Day Incident Source").Range("A:AR").Select
ThisWorkbook.Worksheets("Previous Day Incident Source").Paste

ThisWorkbook.Worksheets("Current Incident Source").Range("A:AR").ClearContents

'Copy range to clipboard
Workbooks("Tag Up Incident Source Dump.xlsx").Worksheets("Page 1").Range("A:AR").Copy
'PasteSpecial to paste values, formulas, formats, etc.
ThisWorkbook.Worksheets("Sheet13").Range("A:AR").Paste

'Copy range to clipboard
Workbooks("Tag Up CCDR Abandon Call Report.xls").Worksheets("Tag Up CCDR Abandon Call Report").Range("A:R").Copy
'PasteSpecial to paste values, formulas, formats, etc.
ThisWorkbook.Worksheets("Sheet10").Range("A:R").Pastets, etc.
ThisWorkbook.Worksheets("Sheet10").Range("A:R").Paste

That means ThisWorkbook does not have a sheet named "Sheet13" — BigBen
In VBA it says Sheet13 but has the name of the sheet. With your statement, I assume I should use the actual sheet name correct? — yeahprettimuch
You can use either ThisWorkbook.Sheets("theactualsheetname"), or just Sheet13 (no ThisWorkbook). "yoursheetname" would be the worksheet's name, and "Sheet13" is the codename. — BigBen

yeahprettimuch yeahprettimuch · Accepted Answer · 2020-08-05T21:33:50

With help from BigBen "You can use either ThisWorkbook.Sheets("theactualsheetname"), or just Sheet13 (no ThisWorkbook). "yoursheetname" would be the worksheet's name, and "Sheet13" is the codename. – BigBen 1 hour ago"

Copy range to clipboard Workbooks("Tag Up Incident Source Dump.xlsx").Worksheets("Page 1").Range("A:AR").Copy 'PasteSpecial to paste values, formulas, formats, etc. ThisWorkbook.Worksheets("Current Incident Source").activate ThisWorkbook.Worksheets("Current Incident Source").Range("A:AR").Select ThisWorkbook.Worksheets("Current Incident Source").Paste –

How to copy range from one workbook to another workbook

1 Answers