Help Copying/transfering Data from one Workbook to ThisWorkbook

[Jester07]

Hi,

I've just started trying VBA in excel and am trying to source financial information somewhat less painlessly than manual entry.

I download the information in excel format and want to copy certain aspects of relevant workbook into a master workbook.

When I run the VBA code, I get the following error: "Type mismatch error"

Please Login or Register  to view this content.

I tried altering the code to the one below simply copy and patse but get a "9 Subscript out of range error"

Please Login or Register  to view this content.

I'm not too sure where I'm going wrong and have looked at VBA code texts in my University Library and didn't get anywhere with it

Any help is greatly appreciated!

[protonLeah]

ASXCode is already a string value and does not need the double quotes.
The Cells property requires two integers specifying the row and column numbers of a cell, for example: Cells(9,13)
The Range property requires the name (a string value) of a cell. In this case: "A40"

Thus: ThisWorkbook.Sheets(" & ASXCode & ").Cells("A40").Value = BalanceSheet
should be:
ThisWorkbook.Sheets(ASXCode).Range("A40").Value = BalanceSheet

However(!) You delcared BalanceSheet as a String variable, but then you try to assign an array to it, which is an error. BalanceSheet must be declared as type Variant in order to contain a group of cells (array). Then, in order to write the data to the new location, you specify the location and size of the area to receive the data:

Sheets(ASXCode).Range("A40").Resize(rowsize:=39, columnsize:=12).Value = BalanceSheet

[Jester07]

I'll try this tonight and reply how it goes

Thanks for going through this protonLeah, very useful and much appreciated

[Jester07]

Many thanks pL, managed to get this working based on what you had noted and a few modifications.