Referencing 2nd workbook problem

[Aerowolf]

Here's my code:

Dim nm As String, nbr As String, due As String, ord As String
nbr = ActiveCell.Offset(0, 1).Range("A1")
due = ActiveCell.Offset(0, 9).Range("A1")
ord = ActiveCell.Offset(0, 13).Range("A1")
nm = "X:\WOVEN PRODUCTION\CRIMP & WEAVE INST & ROUTINGS\" & ActiveCell & ".xls"
Workbooks.Open nm, 3, False
With Workbooks(nm)
Range("R2").Value = nbr
Range("M5").Value = due
Range("H7").Value = ord
End With

I'm getting a subscript out of range error on the With Workbooks(nm) line. Help!

 

[Loomah]

Hi
Try this slight rework of your code

[b]Dim nm As Workbook[/b]
Dim nbr As String, due As String, ord As String
    nbr = ActiveCell.Offset(0, 1).Range("A1")
    due = ActiveCell.Offset(0, 9).Range("A1")
    ord = ActiveCell.Offset(0, 13).Range("A1")
    [b]Set nm = Workbooks.Open("X:\WOVEN PRODUCTION\CRIMP & WEAVE INST & ROUTINGS\" & ActiveCell & ".xls")
    With nm[/b]
        Range("R2").Value = nbr
        Range("M5").Value = due
        Range("H7").Value = ord
    End With

I'd just like to point out that you'll probably need to specify the worksheet within your "With" construct and also add the dot(.) before each Range otherwise the active sheet of the active workbook will be assumed by xl.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

I think that loomah wanted to say...

    With nm
        .Range("R2").Value = nbr
        .Range("M5").Value = due
        .Range("H7").Value = ord
    End With

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[Loomah]

Certainly did Skip!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Aerowolf]

OK...so here is what I have right now:

Dim nm As Workbook, nbr As String, due As String, ord As String
nbr = ActiveCell.Offset(0, 1).Range("A1")
due = ActiveCell.Offset(0, 9).Range("A1")
ord = ActiveCell.Offset(0, 13).Range("A1")
Set nm = Workbooks.Open("X:\WOVEN PRODUCTION\CRIMP & WEAVE INST & ROUTINGS\" & ActiveCell & ".xls", 3, False)
With nm
.Range("R2").Value = nbr
.Range("M5").Value = due
.Range("H7").Value = ord
End With

Now I'm getting this error:

'Object doesn't support this property or method' at the first .Range line

Thanks for all the help.

 

[Loomah]

With nm[b].Worksheets("YourWorksheetName")[/b]
    .Range("R2").Value = nbr
    .Range("M5").Value = due
    .Range("H7").Value = ord
End With

is the other bit I mentioned - probably a little too vaguely!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

oops...

need the SHEET reference...

    With ActiveSheet
        .Range("R2").Value = nbr
        .Range("M5").Value = due
        .Range("H7").Value = ord
    End With

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[Aerowolf]

Thanks very much guys...got it working!

Edwin