Runtime-Error '9' For Each WS In Workbooks 1

[IknowMe]

I have a Sub which creates a workbook and populates the worksheets

Set NewWB = Workbooks.Add
NewWB.SaveAs "C:\" & "Test" & Format(Now, "mm_dd_yyyy") & ".xls"

I have another Sub which runs through the worksheets, I'll add some error checking to make sure the referenced workbook is still open (pesky users), but assume it is open for purpose of question.

For Each WS In Workbooks("Test" & Format(Now, "mm_dd_yyyy") & ".xls").Worksheets
[COLOR=green]'above line throws run time error when I try to run[/color]
   For iAllRows = 1 To WS.UsedRange.Rows.Count

I had this working yesterday but also had the referenced workbook created/opened when running. I think it may have to do with nonexistence of referenced workbook but I'm not sure. Any ideas?


Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[PHV]

You may have NewWB defined as Global and then:
For Each WS In NewWB.Worksheets

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[IknowMe]

Definately has to do with the existence of the workbook. When I create the workbook manually close it and run the run time error goes away. Problem is that the Workbook doesn't exist unitl the previous sub creates it. So I'm still looking for a fix.

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[IknowMe]

Thanks for the tip PHV, you got me thinking on the right path.

I ended up switching from Sub to Function and returned the WB to pass to the following Sub. Working great now.




Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[IknowMe]

Working great in theory however,

I now get Run Time error 91 with the following

Function MakeWB(iReportType As Integer) As Workbook

Dim SomeWB As WorkBook
  
  Set SomeWB = Workbooks.Add
  ScrapeEons = SomeWB
    
End Function

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[PHV]

Why not simply this ?
Function MakeWB(iReportType As Integer) As Workbook
Set MakeWB = Workbooks.Add
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[IknowMe]

Omitted red, problem solved thanks again

Sub Main()
Dim MyWB As Workbook
Set MyWB = MakeWB(1)
End Sub
Function MakeWB(iReportType As Integer) As Workbook

Set MakeWB = Workbooks.Add

End Function



Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.