help with code?/

cprince

Board Regular
Joined
Jul 4, 2006
Messages
67
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

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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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

Board Regular
Joined
Jul 4, 2006
Messages
67
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

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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
 

Watch MrExcel Video

Forum statistics

Threads
1,112,882
Messages
5,543,012
Members
410,583
Latest member
gazz57
Top