Hi All ,
Can someone see where my code is going wrong? I m Stuck!
I think this is the line it doesnt like, but im not 100%...
For Each ws In Workbooks(filetoopen).Sheets
Thanks
Can someone see where my code is going wrong? I m Stuck!
I think this is the line it doesnt like, but im not 100%...
For Each ws In Workbooks(filetoopen).Sheets
Thanks
Code:
Private Sub Workbook_Open()
MsgBox "Please select a Sample tracking list to report on"
Do
filetoopen = Application _
.GetOpenFilename("Workbooks (*.XLS), *.XLS")
If filetoopen <> 0 Then
Workbooks.Open Filename:=filetoopen
procedure2 (filetoopen)
Exit Sub
Else
MsgBox "You didnt select a file, Please try again"
End If
Loop
End Sub
Sub procedure2(filetoopen)
Dim ws As Worksheet
Dim LastR As Range
Dim rg As Range
Dim error1 As String
'grabs all the data from the worksheet and puts it in sheet1 of the template
For Each ws In Workbooks(filetoopen).Sheets
Set LastR = Workbooks("SampleTracking1").Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1)
If IsEmpty(Range("A1")) Then Set LastR = Range("A1")
ws.Range("AG2:AG3000").Value = ws.Name
ws.Range("A1:AG3000").Copy
LastR.PasteSpecial xlPasteValues
LastR.PasteSpecial xlPasteFormats
Next
end sub