Macro to copy selected columns, convert to number and paste to new tab

[Greed]

Hi all,

I need a macro that will copy selected columns (from 2 different worksheets), paste to a new tab and convert the existing formulas to values (I need the conversion done as I have another autofilter macro that I need to run and it won't work unless the formulas are converted to statuic values).
Background: This macro will be run by very basic excel users on a monthly basis (showing monthly totals [sheet 1] and annual totals [sheet 2] together). Each user only wants to see certain columns in their report, so I want to set up macro assigned buttons for them that will show only the columns they want to see in the new tab that is created.
Example: User1 only wants to see columns that show "Acting" hours, so the end result will be to pull columns [E:F, H:I, J:K, AD:AH] [from both sheet1 and sheet2] to a new sheet3
User2 only wants to see "Tech" hours, so the end result will be to pull columns [Y:AC] [from both sheet1 and sheet2] to a new sheet3
I don't need separate macros - just one will do and I can alter it to filter for different requests
TIA
Work book attached - note: the values in this workbook have already been converted from forumlas as they would normally be taken from another workbook, but it gets complicated

[jomili]

Assuming the listing of folks is the same on both sheets (not rearranged) this will work:

Please Login or Register  to view this content.

[Greed]

Yes, that works great! Now that I am looking at the 'pasted' version, I have a couple of questions....
What command would I use to also copy row1 from [sheet1-Oct 2011]?
Is there an alternate command I could use that would change the sheet referenced as [sheet1-Oct 2011] to insead reference "Current Sheet" (as in the sheet the user is looking at)? This would save me from rewriting the macro 12 times over - every year....
Thanks again!

[jomili]

For the "Current" sheet, you would use the code like this:

Please Login or Register  to view this content.

To copy the top row from the activesheet, something like this would work:

Please Login or Register  to view this content.

[Greed]

Do I have this first bit of code right? It is not copying row1 or any of the columns from the "active sheet", but is getting columns (A:B) and all of the columns from ("Annual 2011")....thanks

Please Login or Register  to view this content.

[jomili]

Well, yes and no. If you walked through the code you would see the problem.

You first add a sheet. Adding a sheet makes that sheet the Active sheet, so then you're copying from a blank sheet to itself (ie "Acting" is also the Activesheet). Now, if we straightened that out, you would still have a problem, because you first copy Row1 onto your Acting sheet. Then you copy various columns, which include row 1 (that's the top of the column), so you're pasting over the row1 you originally pasted in.

Change your code like this and see if it does the trick:

Please Login or Register  to view this content.

[Greed]

Ahh, I see now - thanks for all your help!

[jomili]

You're very welcome.