Code:
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 SubThe IF section of my macro is supposed to count the number of sheets in a predefined workbook an then if that number exceeds 6, it will copy the data from a sheet in that workbook and paste it into a newly created sheet in my original workbook. I'm running into some problems with defining the original workbook. As you see I set wb1 = ActiveWorkbook (the original workbook) at the beginning of the macro. What is happening is the new sheet is being created in the secondary workbook instead of the main one. I think this is because prior to the creation of the new sheet, the ActiveWorkbook is the secondary workbook, therefore wb1 represents the secondary workbook at the time. What should I change the early line using ActiveWorkbook to so that it holds its value throughout the macro (the original workbook). I don't wish to use any hard path names as these may change when other users use the file.
fyi this is xposted but I will keep it up to date with any developments
