Changing target sheet name wihtin a macro

[Guest]

I have a macro that copies the total value of a kist that changes daily. The
result is copied to the active cell in another sheet as a value only.

I start a new sheet each month and need to change the target sheet name by
editing the macro each month. How do I have the macro recognise the current
sheet as well as the current cell?

Sheets("OS CHQS").Select
Range("A49").Select
Selection.Copy
Sheets("Jan05").Select
ActiveCell.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

 

[Tom Ogilvy]

Sheets("OS CHQS").Select
Range("A49").Select
Selection.Copy
Sheets(format(Date,"mmmyy")).Select
ActiveCell.Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, _
Transpose:=False

 

[Guest]

Tom,
I tried this and the macro has no visible effect other than to place the
cursor in cell A2 of the current sheet.

I'm not sure what the 'Sheets(format(Date,"mmmyy"))' actually does.

The problem I think I need solved is, how do I set the active sheet and cell
as the one into which I want the information copied into from the source
sheet without needing to edit the sheet name in the macro to the new month.

Note also that the old month sheet will be used in the first couple of days
of the new month to clsoe out the previous months transactions. So if
"mmmyy" is intended to use teh default system date information, it won't work
on these days.

Thanks
Jim


--
Jim

Sheets("OS CHQS").Select
Range("A49").Select
Selection.Copy
Sheets(format(Date,"mmmyy")).Select
ActiveCell.Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, _
Transpose:=False