Solved: Adding 2 worksheets to an existing workbook

[goobers]

In a previous post I had mdmackillop give me a lot of help on a looping and exporting issue. Of the 3 files i needed to apply his code to, only 2 were successful. The third file just had too much data and calculations to run through, so I kept receiving the lovely error ""Excel cannot complete this task with available resources"

So, I created this post to address my thoughts on the third file. Here is the previous code mdmackillop had provided:

[VBA]Option Explicit
Sub Macro1()
Dim WB1 As Workbook
Dim WB2 As Workbook
Dim i As Long

Application.ScreenUpdating = False

i = 2
Set WB1 = ActiveWorkbook
WB1.Sheets(1).Cells(3, 2) = i
Sheets(Array("Sheet1", "Sheet2")).Copy
Set WB2 = ActiveWorkbook

For i = 3 To 7
WB1.Sheets(1).Cells(3, 2) = i
WB1.Sheets(Array("Sheet1", "Sheet2")).Copy after:=WB2.Sheets(Sheets.Count)
Next

With WB2
For i = 1 To .Sheets.Count
.Sheets(i).Cells.Copy
.Sheets(i).Range("A1").PasteSpecial xlValues
Application.Goto .Sheets(i).Range("A1")
Next
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub
[/VBA]

Rather than have this third file loop through all of the values "i", I would prefer to manually change the value in the workbook myself, run my calculations, and then have a macro that copies the formats & values from "Sheets1" and "Sheets2" to a new workbook.

Let's say that I want to change the value in my workbook 3 times. Eventually this would lead to creating 6 sheets in 1 workbook (similar to the 12 sheets in 1 workbook from the code above).

Step 1)
So, the first time I run the macro, the code will create a new workbook and add the first 2 sheets.

Step 2)
The second time I run the macro though, I want the two new sheets to be copied into the workbook that was created from Step 1.

My problem: I am having difficulty linking the name of the workbook from Step 1, to the code in Step 2.

[Bob Phillips]

You already have that setup. WB2 refers to the new workbook, so use that in Step 2 as per the original code.

[goobers]

You already have that setup. WB2 refers to the new workbook, so use that in Step 2 as per the original code.

The original code is creating a new workbook each time (let's call this Step 1). Do I need to write a separate procedure that adds the sheets from Step 2 then, since I am manually changing the "i" and not having the the code loop.

[Bob Phillips]

I am not sure that I fully understand, but it sounds that you should mke WB2 a public variable, and create a new procedure to handle Step 2, still using (public) WB2.

[goobers]

I am not sure that I fully understand, but it sounds that you should mke WB2 a public variable, and create a new procedure to handle Step 2, still using (public) WB2.

So I have the original procedure for Step 1. This procedure:

1) creates a new workbook WB2
2) copies Sheets1 and Sheets2 from WB1 to WB2
3) paste special formats/values in the Sheets of WB2

For Step 2 (the next time I update value "i"), I can't use the same code because it will create a new workbook each time. Presumably I'll have to write a separate procedure for Step 2, but I was just curious if there was a way to do it so I'm using the same procedure for both Steps.

[Bob Phillips]

You could, but you would need a flag to check whether WB2 is nothing or not. What would it buy you?

[goobers]

You could, but you would need a flag to check whether WB2 is nothing or not. What would it buy you?

This workbook is being used by non-savvy tech/excel users. The less buttons/macros they have to run, the better off my world would be.

[goobers]

So I created a second procedure calling on the public variables to add the new worksheets from Step 2 to WB2. However, I am receiving this error

"Run Time Error 91. Object Variable or With block variable not set"

when the code steps into this line:

WB1.Sheets(Array("P&L", "Revenue")).Copy after:=WB2.Sheets(Sheets.Count)

At the top of my module, I have set the following public variables:

Public WB1 As Workbook
Public WB2 As Workbook

Here is the full code for my Step 2 procedure:

[VBA]Public Sub Copy2()
Dim i As Long

Application.ScreenUpdating = False

Set WB1 = ActiveWorkbook

WB1.Activate

'Calculates P&L and Revenue tabs based on new US Value
Application.Calculate

WB1.Sheets(Array("P&L", "Revenue")).Copy after:=WB2.Sheets(Sheets.Count)

Set WB2 = ActiveWorkbook

Application.DisplayAlerts = False

For i = 1 To WB2.Sheets.Count

WB2.Sheets(i).Cells.Copy
WB2.Sheets(i).Range("A1").PasteSpecial xlValues
Application.Goto WB2.Sheets(i).Range("A1")

Next

Application.DisplayAlerts = True

Application.CutCopyMode = False
Application.ScreenUpdating = True

WB1.Activate
WB1.Sheets("Dashboard").Select
End Sub
[/VBA]

[Bob Phillips]

Try

[vba]

WB1.Sheets(Array("P&L", "Revenue")).Copy after:=WB2.Sheets(WB2.Sheets.Count)
[/vba]

[Paul_Hossler]

Well, these seem to be redundant, since WB1 would already be active


[vba]
Set WB1 = ActiveWorkbook
WB1.Activate
[/vba]

Since you're getting a 91, on the first line, and WB2 is not Set until the second, are they out of order?

[vba]
WB1.Sheets(Array("P&L", "Revenue")).Copy after:=WB2.Sheets(Sheets.Count)

Set WB2 = ActiveWorkbook
[/vba]

Paul

[goobers]

Try

[vba]

WB1.Sheets(Array("P&L", "Revenue")).Copy after:=WB2.Sheets(WB2.Sheets.Count)
[/vba]

Woohoo, that fixed it! Thanks so much for you help xld.

[goobers]

Well, these seem to be redundant, since WB1 would already be active

[vba]
Set WB1 = ActiveWorkbook
WB1.Activate
[/vba]

Paul

Yes, this was redundant. I have removed it from my procedure. Thanks for pointing that out. I had been copying/pasting code from other procedures and forgot to clean this up.