0

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.

giorgi lomidze
  • 9
  • 8
  • Please include ([edit]) in your question which error message you get and in which line. – Pᴇʜ Mar 07 '19 at 09:04
  • Is `woorksheets` a typo only in your question or is it in your code? – FunThomas Mar 07 '19 at 09:08
  • I think "worksheets" is written correctly. I have just checked it and it gave me an error for "worksheet". "worksheets" is in my code. – giorgi lomidze Mar 07 '19 at 09:16
  • If `woorksheets` is a typo, please correct your question. If not - there's your problem. Everything works fine if you change it to `worksheets` – Sam Mar 07 '19 at 09:32
  • 2
    Running your code (with the correction of `woorksheets`) runs fine till `ThisWorkbook.Worksheets("RCB").Range("A:E").Copy .Worksheets("RCB").Range("A1")`. Here you are trying to copy to sheet `RCB`, but you've created and named a sheet called `tempo`. – FAB Mar 07 '19 at 09:35
  • Thank you, guys. I have corrected both Tempo and "woorksheets" and now new workbook is created with sheet RC name is "sheet1" but ranges are copied accordingly. second sheet RI: sheet name is correct and ranges are correct. RCB sheet name is correct but ranges aren't copied. and gives me an error: runtime error 9: Subscript out of range. – giorgi lomidze Mar 07 '19 at 09:54
  • That error usually means a range doesn't exist... can you edit your post with the corrections you did? – FAB Mar 07 '19 at 10:44
  • I have edited. I will check for ranges – giorgi lomidze Mar 07 '19 at 11:35
  • The code is identical with the others, so it should work as long your original sheet is indeed named `"RCB"`, and not `"RCB "` (with an added space, that's not visible to naked eye, but try to rename it). – FAB Mar 07 '19 at 15:11

0 Answers0