Excel - Subscript out of range 2

[srogers]

Hi-
I got the Subscript out of range error on this code:

Sub FillInValue()
Dim Num As Integer
Num = Application.InputBox("Please Enter the Period&quot
Worksheets("WEEKONE&quot.Range("C10&quot.Value = Workbooks("202forecast.xls&quot.Worksheets(Num).Range("C5&quot.Value
End Sub

I have one sheet for each period, so based on the period number they put in, the value from a cell on that sheet should get filled in on another workbook. I'll be doing that for a bunch of cells.

I clicked on help and looked at the possible reasons for the error. I'm not doing an array so I'm guessing it thinks the collection item doesn't exist? I have 13 sheets and put in a number like 2 or 5 and I get the error. It highlights this line "Worksheets("WEEKONE&quot....."

Any ideas you'd like to share?
Thank you -

 

[SkipVought]

Hi,

My guess is it's Worksheets(Num) -- Num is > Workbooks("202forecast.xls&quot.Worksheets.Count

It could be any of the Workbook or Worksheet objects do not exist based on the Index/Name.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[TonyJollans]

Hi srogers,

The obvious candidate here is Worksheets("WEEKONE&quot. Do you have such a sheet in the Active workbook?

Enjoy,
Tony

 

[srogers]

I've confirmed things that work (my names and numbers are right) - but don't know what's left that could be wrong.

This works:

Sub FillInValue()
Dim Num As Integer
Num = Application.InputBox("Please Enter the Period&quot
Worksheets("WEEKONE&quot.Range("C10&quot.Value = Worksheets(Num).Range("C10&quot.Value
End Sub

The difference is that here I am using only one workbook.
I wasn't sure I could use a number input by the user to affect another workbook but I found out I can - because this works:

Sub FillInValue()
Dim Num As Integer
Num = Application.InputBox("Please Enter the Period&quot
Workbooks("202forecast.xls&quot.Worksheets(Num).Activate
End Sub

(I can see that whatever number I enter activates that worksheet).
I think this confirms that I have the workbook names right and I'm choosing worksheet numbers that exist.
But I don't know what's left that could be wrong.
Unless the error is misleading? Maybe I can't refer to a worksheet with a variable and still use the .value?

 

[srogers]

Hi Tony-
Well, this will sound stupid - but how do I make sure it is the active workbook?

That worksheet(WEEKONE) is in the workbook(PeriodXX) with the VBA in it.

Do I need to add a workbook.activate line?
Thanks-

 

[TonyJollans]

Hi srogers,

You could activate it OR (better) you could qualify the reference ..

Workbooks("PeriodXX&quot.Worksheets("WEekone&quot...

Enjoy,
Tony

 

[srogers]

I'm feeling smarter already - 'cause I tried that.

Sub FillInValue()
Dim Num As Integer
Num = Application.InputBox("Please Enter the Period&quot
Workbooks("PeriodXXInProcess&quot.Worksheets("WEEKONE&quot.Range("C10&quot.Value = Workbooks("202forecast.xls&quot.Worksheets(Num).Range("C5&quot.Value
End Sub

Then I thought maybe it didn't like using a variable so I tried putting in the exact sheet name:

Sub FillInValue()
Dim Num As Integer
Num = Application.InputBox("Please Enter the Period&quot
Workbooks("PeriodXXInProcess&quot.Worksheets("WEEKONE&quot.Range("C10&quot.Value = Workbooks("202forecast.xls&quot.Worksheets("Period 1").Range("C5&quot.Value
End Sub

But I still get the subscript out of range error.

I was just trying to save myself some lines of code. I have 200 or so cells to populate for each of 13 periods.

I appreciate the efforts -

 

[RobBroekhuis]

Or you can use ThisWorkbook, which always refers to the workbook containing the VBA code that's running.


Rob

 

[SkipVought]

???

Workbooks("PeriodXXInProcess&quot

How 'bout

Workbooks("PeriodXXInProcess.xls&quot

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[srogers]

Wowee - I had never heard of that but I just tried it and it works!

The problem line looks like this now:
ThisWorkbook.Worksheets("WEEKONE&quot.Range("C10&quot.Value = Workbooks("202forecast.xls&quot.Worksheets(Num).Range("C5&quot.Value

I don't understand why that worked when giving it the workbook name didn't work.

But ROB! Thank you.

 

[srogers]

Ok duh -
never mind not understanding now

Is it ok to give you a star even tho' it was such a silly mistake?



Thanks