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"

Sub RenameSheetGetData()

Dim DataTarget As Workbook
Dim ASXCode As String
Dim rng As Range
Dim BalanceSheet As String



'Set rng i.e. the Range as SheetxCell for entering ASXCode as cell C1 in the ACTIVE SHEET

Set rng = ActiveSheet.Range("C1")

'Define ASXcode as the value in cell C1 of the ACTIVE Sheet

ASXCode = rng.Value

'Name activeworksheet after cell C1 (ASXCode)

ActiveSheet.Name = ActiveSheet.Range("C1")

Application.Wait (Now + TimeValue("0:00:01"))

'Define data record and open file:
Set DataTarget = Workbooks.Open("C:\Users\...\" & ASXCode & ".xml")

'Grab data from datatarget workbook:
BalanceSheet = DataTarget.Sheets("Balance Sheet").Range("C1:N39").Value

ThisWorkbook.Sheets(" & ASXCode & ").Cells("A40").Value = BalanceSheet

Application.DisplayAlerts = False

'Close DataTarget:
DataTarget.Close

Application.DisplayAlerts = True

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

Sub RenameSheetGetData()

Dim DataTarget As Workbook
Dim ASXCode As String
Dim rng As Range
Dim BalanceSheet As String



'Set rng i.e. the Range as SheetxCell for entering ASXCode as cell C1 in the ACTIVE SHEET

Set rng = ActiveSheet.Range("C1")

'Define ASXcode as the value in cell C1 of the ACTIVE Sheet

ASXCode = rng.Value

'Name activeworksheet after cell C1 (ASXCode)

ActiveSheet.Name = ActiveSheet.Range("C1")

Application.Wait (Now + TimeValue("0:00:01"))

'Define data record and open file:
Set DataTarget = Workbooks.Open("C:\Users\...\" & ASXCode & ".xml")

'Grab data from datatarget workbook:
BalanceSheet = DataTarget.Sheets("Balance Sheet").Range("C1:N39").Value

ThisWorkbook.Sheets(" & ASXCode & ").Cells("A40").Value = BalanceSheet

Application.DisplayAlerts = False

'Close DataTarget:
DataTarget.Close

Application.DisplayAlerts = True

End Sub
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!