Macro to cut and paste current month sheet into the relevant month chosen by a drop down

Hi all,

Happy New Year!

I have a speadsheet which captures data weekly. What I need is at the end of the month the user can press a button and it just copies everything from the worksheet called "Current Month" and pastes it into a worsheet called Jan, Feb, March, April, May, June, July, Aug, Sept, Oct, Nov or Dec.
The worksheet to paste into should be selected by the user by way of a dropdown list on the "Instructions" worksheet.
I'm struggling with the choosing of the sheet to paste into.
Any help would be greatly appreciated.

Additionally, the "current Month" worksheet also has graphs on that would need to refer to the data on the Jan...Dec sheet. (This part not so important as I could just copy and paste the data only and point the graphs at the data table).

Many thanks


Scotty

Perhaps something like this could be of use? Not based on a drop down list but in input box. To test run macro "CopyValue".

Macro asks for month name and checks if there is a sheet with this name. If so old values are cleared form sheet and new values pasted in it.

If sheet name don't excists a new sheet is created.

Alf

Originally Posted by Alf

Perhaps something like this could be of use? Not based on a drop down list but in input box. To test run macro "CopyValue".

Macro asks for month name and checks if there is a sheet with this name. If so old values are cleared form sheet and new values pasted in it.

If sheet name don't excists a new sheet is created.

Alf

Thanks Alf - this is superb and just what I'm looking for.

Many thanks

Scotty

You are welcome.

As the macro copies from the "Current Month" most things will look alike but certain special formats are not copied i.e. row height and column with so if you really wish for an exact replica of the "Current Month" sheet one should use the "Copy worksheet" command.

I've written a macro that does this. Work in a similar way as the previous macro i.e. asks for a month name as a starting input and if no sheet has this name it makes a copy of the "Current Month" sheet placed as the last sheet and then renaming it after the input month.

If the month sheet exists macro will first delete this sheet and then add the "new" month sheet as the last sheet. Not to happy about that.

What I would like to solve is how to sort the sheets in "normal" monthly order i.e. "Jan", "Feb", "Mar" . . . . but so far have found out how to do it, still a nice intellectual challenge that probably will keep me occupied for some time.

New macro looks like this:

Please Login or Register  to view this content.

Alf

Ps It seems like you problem is solved so could you please mark you thread "Solved"

To mark your thread solved do the following:
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved

Thanks again Alf - sure you'll figure out!