Type Mismatch Error when getting data from another workbook

[Tony Zappal]

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


"Tony Zappal" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Tony Zappal]

Thanks Sharad,

I'll make the necessary changes.

Thanks.
Tony.

"Sharad Naik" wrote:

> 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
>
>
> "Tony Zappal" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>
>