Copy ranges from one workbook to another

Question

I have a workbook with three sheets. RC,RI,RCB. I need To write VBA code, which will copy ranges from this workbook and will paste to a newly created workbook.

In this code, everything Works except sheet RCB. Debug turns on on line 12

Sub Budget()
With Workbooks.Add
ActiveSheet.Name = "RC"
ThisWorkbook.Worksheets("RC").Range("A:D").Copy .Worksheets("RC").Range("A1")
ThisWorkbook.Worksheets("RC").Range("E:G").Copy .Worksheets("RC").Range("E1")
Sheets.Add
ActiveSheet.Name = "RI"
ThisWorkbook.Worksheets("RI").Range("A:E").Copy .Worksheets("RI").Range("A1")
ThisWorkbook.Worksheets("RI").Range("G:G").Copy .Worksheets("RI").Range("G1")
Sheets.Add
ActiveSheet.Name = "RCB"
ThisWorkbook.Worksheets("RCB").Range("A:C").Copy .Worksheets("RCB").Range("A1")
ThisWorkbook.Worksheets("RCB").Range("E:E").Copy .Worksheets("RCB").Range("G1")
.SaveAs "C:\Users\lomid\Desktop\4finansi3"
.Close
End With
End Sub

I think it has to be about active sheet and workbook. this is an error it gives me: "Run-time error 9: subscript out of range"

OP is copying data from ThisWorkbook-sheets to sheets created in a new Workbook. Workbooks.Add not only creates a new workbook, but sets it active. The code could be written (much) better, but this is not the issue. I did a quick test and it worked for me - assuming that the original workbook has all three sheets. — FunThomas
@SJR - Ohhh, okay I see. This is confusing to read to me...I'd suggest OP instead declare the workbooks properly instead... — BruceWayne

GMalc GMalc · Accepted Answer · 2019-03-07T19:25:24

You can dispense with all the copy/paste. When you use SaveAs to save your current workbook, the original workbook will close without saving and the new workbook will be open. From what i can tell from your question you want to keep only certain columns. I like to delete the columns you don't want and do a SaveAs to a new workbook name. The code below is much cleaner. Use Application.DisplayAlerts = False so you don't get the The following features cannot be saved in macro-free workbooks: prompt.

Application.DisplayAlerts = False
    With ThisWorkbook
        .Sheets("RC").Range("H:Z").EntireColumn.Delete
        .Sheets("RI").Range("H:Z, F:F").EntireColumn.Delete
        .Sheets("RCB").Range("F:Z, D:D").EntireColumn.Delete

        .SaveAs "C:\Users\lomid\Desktop\4finansi3.xlsx"
    End With
Application.DisplayAlerts = True

Copy ranges from one workbook to another

1 Answers