Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing target sheet name wihtin a macro
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 -- Youre help is appreciated. Jim |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing target sheet name wihtin a macro
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 -- Regards, Tom Ogilvy "Jim G" wrote in message ... 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 -- Youre help is appreciated. Jim |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing target sheet name wihtin a macro
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 "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Jim G" wrote in message ... 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 -- Youre help is appreciated. Jim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Macro values to new sheet | New Users to Excel | |||
Closing File Error | Excel Discussion (Misc queries) | |||
Help with macro looping and color query function | Excel Discussion (Misc queries) | |||
Using a macro how do I group every sheet within a book? | Excel Discussion (Misc queries) | |||
Macro for changing print settings | Excel Discussion (Misc queries) |