help with code?/

[cprince]

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

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

 

[acw]

Hi

I think the variable filetoopen will contain the full path as well as the filename. You will have to remove that component to use in the workbooks(filetoopen).sheets statement.

Also, shouldn't the workbooks("SampleTracking1") be workbooks("sampletracking1.xls")


Tony

 

[cprince]

Its a Template, so it will actually be called Sampletracking1 when the New template has started.

I will try and refer to only the filename. and see how I go.. Any ideas on how to do that?

 

[acw]

Hi

When it opens it should be the activeworkbook. You should then be able to extract the name (activeworkbook.name). As you use it a couple of times, probably best to put it into a variable.


Tony