Run-time 9 - Subscript out of range

[azraeana]

Hello!

I have an active sheet that is named the current date. I want to put into it the value of a specific cell from the worksheet named 2013. Specifically it is going to row 6, column 8 of this sheet.

When I look at the VBA Project list, I see my worksheet as Sheet50 (2013). When I click on it the (name) property is Sheet50 but the Name property is 2013. When I look at the worksheets in Excel the tab has been renamed to 2013, so I know the sheet exists. Here are the things I have tried.

[FONT=Menlo] [/FONT][FONT=Menlo]ActiveSheet[/FONT][FONT=Menlo].[/FONT][FONT=Menlo]Range[/FONT][FONT=Menlo]([/FONT][FONT=Menlo]Cells[/FONT][FONT=Menlo]([/FONT][FONT=Menlo]n[/FONT][FONT=Menlo],[/FONT][FONT=Menlo] [/FONT][FONT=Menlo]j[/FONT][FONT=Menlo])).[/FONT][FONT=Menlo]Value[/FONT][FONT=Menlo] [/FONT][COLOR=#000000][FONT=Menlo][B]=[/B][/FONT][/COLOR][FONT=Menlo] [/FONT][FONT=Menlo]Worksheets[/FONT][FONT=Menlo]([/FONT][FONT=Menlo]Str[/FONT][FONT=Menlo]([/FONT][FONT=Menlo]intYear[/FONT][FONT=Menlo])).[/FONT][FONT=Menlo]Range[/FONT][FONT=Menlo]([/FONT][FONT=Menlo]Cells[/FONT][FONT=Menlo]([/FONT][FONT=Menlo]n[/FONT][FONT=Menlo],[/FONT][FONT=Menlo] [/FONT][FONT=Menlo]([/FONT][COLOR=#009999][FONT=Menlo]4[/FONT][/COLOR][FONT=Menlo] [/FONT][COLOR=#000000][FONT=Menlo][B]+[/B][/FONT][/COLOR][FONT=Menlo] [/FONT][FONT=Menlo]intMonth[/FONT][FONT=Menlo]))).[/FONT][FONT=Menlo]Value[/FONT]

[COLOR=#000000][FONT=Menlo][B]Dim[/B][/FONT][/COLOR][FONT=Menlo] [/FONT][FONT=Menlo]wb[/FONT][FONT=Menlo] [/FONT][COLOR=#000000][FONT=Menlo][B]As[/B][/FONT][/COLOR][FONT=Menlo] [/FONT][FONT=Menlo]Workbook[/FONT][/FONT][/COLOR]
[COLOR=#333333][FONT=Arial][COLOR=#000000][FONT=Menlo][B]Set[/B][/FONT][/COLOR][FONT=Menlo] [/FONT][FONT=Menlo]wb[/FONT][FONT=Menlo] [/FONT][COLOR=#000000][FONT=Menlo][B]=[/B][/FONT][/COLOR][FONT=Menlo] [/FONT][FONT=Menlo]ThisWorkbook

'where sheetName is the currentDate
Dim ws as Worksheet
set ws = wb.worksheets(sheetName)
Dim intYear as Integer
'where base year is 2013 and i is 0 on this first loop through
intYear = baseYear +i

'where n = 6 and j = 4
ws.Range(Cells(n,j)).Value = Worksheets(Str(intYear)).Range(Cells(n,(4+intMonth))).Value

[/FONT]

I've also tried:

ActiveSheet.Range(Cells(n,j)).Value = wb.Sheets(Str(intYear)).Range(Cells(n, (4+intmonth))).value

I've actually probably tried even more ways to get this to work but it always gives me the same error. Any help would be appreciated!

 

[azraeana]

Not sure why my code became not spaced but for verification purposes I do have spaces between the DIM of the workbook and the set of the wb.

 

[Norie]

Where do you get the error?

PS It should just be Cells(...) not Range(Cells(...)).

 

[FormR]

Hi - you should also be using CStr and not Str.

So something like:

ActiveSheet.Cells(n, j).Value = Worksheets(CStr(intYear)).Cells(n, (4 + intMonth)).Value

 

[azraeana]

I've tried Cells as well. Here it is with just Cells and it still breaks on this line.

ActiveSheet.Cells(n, j).Value = wb.Sheets(Str(intYear)).Cells(n, (4 + intMonth)).Value

 

[azraeana]

Hi - you should also be using CStr and not Str.

So something like:

ActiveSheet.Cells(n, j).Value = Worksheets(CStr(intYear)).Cells(n, (4 + intMonth)).Value

Thank you! That was it! I just looked up the difference between the two - I didn't realize that there was a CStr. Thanks again!