goobers
08-07-2009, 09:45 AM
In a previous post (http://www.vbaexpress.com/forum/showthread.php?t=27941) 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:
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
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.
So, I created this post to address my thoughts on the third file. Here is the previous code mdmackillop had provided:
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
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.