Solved: change sheet

[cronid]

I have a workbook with two worksheets: accruals and Dec08_payments. I am positioned on the accruals worksheet and trying to execute the code below to put a value on the Dec_payments worksheet, which needs to be a variable. The value is put on the accruals worksheet not on Dec08_payments.

What am I doing wrong?

Option Explicit
Sub MatchToMonth()
'
Dim Sheetname As String
Sheetname = InputBox("Enter tab name of month to compare.")
MsgBox "tab month to be compared is " & Sheetname

Sheets(Sheetname).Select
ActiveSheet.Name = Sheetname

Cells(1, 20).Value = "Post W/H tax Accrual (CCY)"
End Sub

[nst1107]

Where do you get your error?

[mdmackillop]

Your code works for me.
Tidied version
Option Explicit
Sub MatchToMonth()
'
Dim Sheetname As String
Sheetname = InputBox("Enter tab name of month to compare.")
MsgBox "tab month to be compared is " & Sheetname

With Sheets(Sheetname)
.Activate
.Cells(1, 20).Value = "Post W/H tax Accrual (CCY)"
End With
End Sub

[georgiboy]

I am guessing that your cell range should be...

Cells(20, 1).Value = "Post W/H tax Accrual (CCY)"

rather than

Cells(1, 20).Value = "Post W/H tax Accrual (CCY)"

but only a guess. It works fine for me.

[nst1107]

If your code is in a module, not a sheet, using Cells() without a worksheet object will do nothing. (Look at the change mdmackillop made.)

[cronid]

Md,

Your code works for me as does the verison below. But my original version does not work when I execute it from the other worksheet. The Sheets Select and ActiveSheet statements don't seem to be effective. Although when I execute my original version the literal value appears on the accruals tab but when the program finishes it is on the Dec08_payments tab.

When you say my code works for you, are you executing it from another worksheet?

Thanks.

[cronid]

nst,

My code is not in the modules section; it is in Sheet1, the payments worksheet. I can qualify every cell reference, if I have to do it. But I thought that the Sheets(Sheetname).select statement would position me on that tab (worksheet) and remove the need to continue qualifying cell references in my code.

Is that my mistake? Is that not true?

[nst1107]

You're right. My bad.

[mdmackillop]

If your code is in the Sheet module, without qualifying, the Range will be on that sheet. If the code is in a Standard module, the Range will be in the active sheet. When working between sheets, it is best to qualify, which allows the code to be run from any location. By qualifying both source and destination ranges, the values can be transferred without the need to activate any sheet.

[cronid]

Md,

I understand your comment that "When working between sheets, it is best to qualify, which allows the code to be run from any location. By qualifying both source and destination ranges, the values can be transferred without the need to activate any sheet."

But I don't understand why my statement Sheets(Sheetname).Select doesn't activate that sheet in this example. Isn't that what it's supposed to do? Do you understand why it doesn't work?

[mdmackillop]

Because the code is in a Sheet module, Sheet1 is the default "parent" of the range. So this will write x to whatever sheet whose module contains the code.
Sub Test()
Sheets(2).Activate
Range("A1") = "x"
End Sub