Type Mismatch Error when getting data from another workbook

[Guest]

Hi,
The below macro gives me a type mismatch error, when i try and run it.
It's run from Workbook1, which is named as variable ControlWorkbook.
It open's up another workbook, variabled cc.
The workbook name and sheet have the same name, hence worbooks and
worksheets both relate to variable cc.

Can someone please help.
Thanks.

----
Public cc As Range
-------------------------------------
Sub CreateReport()
Dim r As Range
Application.ScreenUpdating = False
With Sheets("RecsC")
Set r = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
For Each cc In r
Workbooks.Open Filename:=strSavePath & strMonth & "\" & cc, _
UpdateLinks:=False, ReadOnly:=True
Call GrabInvoiceData
Next cc
End With
Application.ScreenUpdating = True
End Sub
-------------------------------------
Sub GrabInvoiceData()
Dim a As Range
Workbooks(ControlWorkbook).Activate
With Sheets("Report")
Set a = .Range("A" & Rows.Count).End(xlUp).Offset(0, 0)
a = Workbooks(cc).Worksheets(cc).Range("B2").Value
End With
End Sub

 

[Sharad Naik]

1. You did Dim a As Range
Then you assing a cell.Value to it.
This is type mismatch.
Once dimmed as range you need to assing a Range Object to it not a value.
e.g. a = Range("B2")

2. You did cc As Range. Topen the workbook the code needs
a String in the name part not a Range Object.
So in the Workbooks.Open line it should be
& cc.Value, _ not only cc.
Same way instead of WorkBooks(cc) it should be
Workbooks(cc.Value), Worksheets(cc.Value).

You defined cc as Public but please note that
in the For each cc loop, when it comes out of the loop
cc will refer only to the last cell in r, not all cells in r.

Sharad

 

[Guest]

Thanks Sharad,

I'll make the necessary changes.

Thanks.
Tony.