Solved: IF/THEN Copy macro

[Adrianz]

[vba]Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
Application.ScreenUpdating = False ' turn off the screen updating
PathName = Range("D3").Value
'Make path selections below
Set wb = Workbooks.Open(PathName)
' the sheet in this workbook to copy to
With ThisWorkbook.Worksheets("Members")
' read data from the source workbook
'the range to copy to in this workbook-name of sheet to copy FROM-range in closed workbook to copy
.Range("A1", "B65536").Formula = wb.Worksheets("Members").Range("A1", "B65536").Formula
End With
With ThisWorkbook.Worksheets("Members")
' read data from the source workbook
'the range to copy to in this workbook-name of sheet to copy FROM-range in closed workbook to copy
.Range("C1", "H65536").Formula = wb.Worksheets("Members").Range("F1", "K65536").Formula
End With
If wb.Sheets.Count > 6 Then
ThisWorkbook.Sheets.Add.Name = "Members6"
ThisWorkbook.Sheets("Members6").Move After:=Sheets(7)
With ThisWorkbook.Worksheets("Members6")
.Range("A1", "B65536").Formula = wb.Worksheets("Members6").Range("A1", "B65536").Formula
End With
With ThisWorkbook.Worksheets("Members6")
.Range("C1", "H65536").Formula = wb.Worksheets("Members5").Range("F1", "K65536").Formula
End With
End If
wb.Close False ' close the source workbook without saving any changes
Set wb = Nothing ' free memory
Sheets("Menu").Select
Range("D8").Select
ActiveCell.Value = "Last Update was:"
Range("F8").Select
Selection.Formula = "=text(now(),""mmm dd yyyy hh:mm"")"
Range("D9").Select
Application.ScreenUpdating = True ' turn on the screen updating
End Sub
[/vba]
The bolded line is producing an error message for some reason...Any ideas why this is going on? The IF segment of my macro checks to see if the workbook I am copying from has more than 6 sheets. If it does my macro is supposed to create a new sheet in the workbook executing the macro and then copy/paste according to the ranges.

[Adrianz]

Edited out

[Adrianz]

Ok, it seems that I'm getting an error because it can't find the worksheet "Members6" in the original file. Turns out the new sheet is only being created in the file being copied from. What should I change "ThisWorkbook" to so that it creates the new sheet within the original file?

[lucas]

I would set it early as you did the one you're opening
[vba]Set wb = Workbooks.Open(PathName) [/vba]

then call it implicitly as you do wb:
[vba]wb2.Worksheets("Members6")[/vba]

[Adrianz]

How would I set the original workbook to Wb2? I don't know the command that refers to the currently open workbook.

Going to try crossposting this, I'll keep you all updated.

[lucas]

In the first of your code where you have this line:
[VBA]Set wb = Workbooks.Open(PathName)
[/VBA]
Add this line just above it:
[VBA]Set wb1 = ActiveWorkbook
[/VBA]

then in your code where you are making this call:
[VBA]With ThisWorkbook.Worksheets("Members6") [/VBA]
change it to this:
[VBA]With wb1.Worksheets("Members6")[/VBA]
get the idea...set it and call it implicitly. I think that will help your problem.

[Adrianz]

[vba]Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
Application.ScreenUpdating = False ' turn off the screen updating
PathName = Range("D3").Value
'Make path selections below
Set wb1 = ActiveWorkbook
Set wb = Workbooks.Open(PathName)
' the sheet in this workbook to copy to
With ThisWorkbook.Worksheets("Members")
' read data from the source workbook
'the range to copy to in this workbook-name of sheet to copy FROM-range in closed workbook to copy
.Range("A1", "B65536").Formula = wb.Worksheets("Members").Range("A1", "B65536").Formula
End With
If wb.Sheets.Count > 6 Then
wb1.Sheets.Add.Name = "Members6"
wb1.Sheets("Members6").Move After:=Sheets(7)
With wb1.Worksheets("Members6")
.Range("A1", "B65536").Formula = wb.Worksheets("Members6").Range("A1", "B65536").Formula
End With
With wb1.Worksheets("Members6")
.Range("C1", "H65536").Formula = wb.Worksheets("Members5").Range("F1", "K65536").Formula
End With
End If
wb.Close False ' close the source workbook without saving any changes
Set wb = Nothing ' free memory
Sheets("Menu").Select
Range("D8").Select
ActiveCell.Value = "Last Update was:"
Range("F8").Select
Selection.Formula = "=text(now(),""mmm dd yyyy hh:mm"")"
Range("D9").Select
Application.ScreenUpdating = True ' turn on the screen updating
End Sub
[/vba]

For some reason it is still creating the new worksheet within the secondary workbook instead of the original one. I don't think simply setting wb1 to the ActiveWorkbook works as my ActiveWorkbook changes throughout the macro. Notice the ActiveWorkbook is the secondary workbook at the time that the new sheet is created.

[lucas]

It's getting convoluted ok....I think all of this should be within one with/endwith statement....
[VBA]If wb.Sheets.Count > 6 Then
wb1.Sheets.Add.Name = "Members6"
wb1.Sheets("Members6").Move After:=Sheets(7)
With wb1.Worksheets("Members6")
.Range("A1", "B65536").Formula = wb.Worksheets("Members6").Range("A1", "B65536").Formula
End With
With wb1.Worksheets("Members6")
.Range("C1", "H65536").Formula = wb.Worksheets("Members5").Range("F1", "K65536").Formula
End With
[/VBA]

[lucas]

You have two of these
[VBA]With wb1.Worksheets("Members6") [/VBA]
and your first If statement is not included in either...

[Adrianz]

I deleted the with statements that follow the If statement. What I found was that a new sheet was created in the original workbook (just like I wanted) and this sheet was moved to the appropriate location. As soon as I add the With statement I start getting the subscript out of range error.

[Norie]

Cross post

[lucas]

Ya know the cross posting usually doesn't bother me too much but I went and looked at the posts and she was posting in both forums at the same time.....Adrianz please read this

[Adrianz]

Sorry bout that, didn't realize I was supposed to combine into one thread, just thought I was supposed to let each forum know whether a solution was reached - won't happen again. Thanks for all the help on this one, I resolved my problem. Here is the final code for that section:
[vba]If wb.Sheets.Count > 6 Then
wb1.Sheets.Add.Name = "Members6"
wb1.Sheets("Members6").Move After:=wb1.Sheets(7)
With wb1.Worksheets("Members6")
.Range("A1", "B65536").Formula = wb.Worksheets("Members6").Range("A1", "B65536").Formula
.Range("C1", "H65536").Formula = wb.Worksheets("Members6").Range("F1", "K65536").Formula
End With
End If[/vba]

[lucas]

Glad you got it worked out Adrianz....it's ok to post in two places but we were duplicating work that was already done....just please be considerate.