Run-time erro 9

[malibu65k]

I get a Subscript out of range but the file does exist and the name is correct.


Dim wb1 As Workbook

sPath = ActiveWorkbook.Path
sFilename = sPath & "\AMSRRList.XLSX"
Set wb1 = Workbooks(sFilename) <<< error is here


Thanks in advance for any assistance

 

[SkipVought]

what application?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

if your workbook is OPEN in the SAME INSTANCE or Excel, then...

Set wb1 = Workbooks("AMSRRList.XLSX")

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[malibu65k]

I did that first with the file open and it still gave me the same error, that's why i did it the other way with the path. Is there any other way to reference another workbook? I'm trying to copy cells from one workbook to another buy looping through it and taking out matches.

 

[SkipVought]

Your workbooks may NOT be in the same instance of Excel.

Run this

Dim wb as workbook
For each was in workbooks
  Debug.print wb.name 
Next

And observe the workbook names in the immediate window of the vb editor.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[malibu65k]

Runtime error code 91

Object variable or with block variable not set

Dim wb as workbook
For each was in workbooks <<< what is "was"
Debug.print wb.name
Next

 

[SkipVought]

sorry

Dim wb as workbook
For each wb in workbooks
  Debug.print wb.name 
Next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[malibu65k]

All 3 in the same folder that I have open comes up...


Parts Location.xlsm
AMSRRList.XLSX
CHP Facility Inventory.xls

 

[SkipVought]

This has absolutely no direct relationship to the folder in which these workbooks reside.

They are all open in the same instance of Excel.

Therefore, to refer to the workbook in question...

dim wb1 as workbook
Set wb1 = Workbooks("AMSRRList.XLSX")

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[malibu65k]

Works! Thanks!

 

[malibu65k]

Now when I do this...

sPath = ActiveWorkbook.Path
sFilename = "\AMSRRList.XLSX"

Workbooks.Open Filename:=sPath & sFilename

Set wb1 = Workbooks("AMSRRList.XLSX")

rowAMSRRList = 10
rowPartList = 4

Do Until (wb1.Sheets("Mag-26").Cells(rowAMSRRList, 1) = "Totals:") And (wb1.Sheets("Mag-26").Cells(rowAMSRRList + 2, 1) = "")

If wb1.Sheets("Mag-26").Cells(rowAMSRRList, 1).Font.ColorIndex = 3 Then
ActiveWorkbook.Sheets("Part_List").Cells(rowPartList, 1) = wb1.Sheets("Mag-26").Cells(rowAMSRRList, 1) << Error here


I want to open and close a Workbook I'm using from within this Workbook.

 

[SkipVought]

When you open a workbook, then IT becomes the ActiveWorkbook!!!

You must be VERY CAREFUL using ActiveANYTHING.

Chances are, the WORKBOOK where you are running the code iswhat you want to reference, hence, ThisWorkbook...

If wb1.Sheets("Mag-26").Cells(rowAMSRRList, 1).Font.ColorIndex = 3 Then
[b]ThisWorkbook[/b].Sheets("Part_List").Cells(rowPartList, 1) = wb1.Sheets("Mag-26").Cells(rowAMSRRList, 1)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[malibu65k]

Worked!

Thanks!