Adrianz
06-25-2007, 03:40 PM
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
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.
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
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.