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

**Greed** · 12-06-2011, 11:12 AM

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** · 12-06-2011, 02:23 PM

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

Sub CopyColumns()

    Worksheets.Add().Name = "Acting"
    Sheets("sheet2-Annual").Columns("A:B").Copy Destination:=Sheets("Acting").Columns("A:B")
    Sheets("sheet1-Oct 2011").Columns("E").Copy Destination:=Sheets("Acting").Columns("C")
    Sheets("Acting").Columns("C").Value = Sheets("sheet1-Oct 2011").Columns("E").Value
    Sheets("sheet2-Annual").Columns("E").Copy Destination:=Sheets("Acting").Columns("D")
    Sheets("Acting").Columns("D").Value = Sheets("sheet2-Annual").Columns("E").Value
    Sheets("sheet1-Oct 2011").Columns("F").Copy Destination:=Sheets("Acting").Columns("E")
    Sheets("Acting").Columns("E").Value = Sheets("sheet1-Oct 2011").Columns("F").Value
    Sheets("sheet2-Annual").Columns("F").Copy Destination:=Sheets("Acting").Columns("F")
    Sheets("Acting").Columns("F").Value = Sheets("sheet2-Annual").Columns("F").Value
    Sheets("sheet1-Oct 2011").Columns("H").Copy Destination:=Sheets("Acting").Columns("G")
    Sheets("Acting").Columns("G").Value = Sheets("sheet1-Oct 2011").Columns("H").Value
    Sheets("sheet2-Annual").Columns("H").Copy Destination:=Sheets("Acting").Columns("H")
    Sheets("Acting").Columns("H").Value = Sheets("sheet2-Annual").Columns("H").Value
    Sheets("sheet1-Oct 2011").Columns("I").Copy Destination:=Sheets("Acting").Columns("I")
    Sheets("Acting").Columns("I").Value = Sheets("sheet1-Oct 2011").Columns("I").Value
    Sheets("sheet2-Annual").Columns("I").Copy Destination:=Sheets("Acting").Columns("J")
    Sheets("Acting").Columns("J").Value = Sheets("sheet2-Annual").Columns("I").Value
    Sheets("sheet1-Oct 2011").Columns("J").Copy Destination:=Sheets("Acting").Columns("K")
    Sheets("Acting").Columns("K").Value = Sheets("sheet1-Oct 2011").Columns("J").Value
    Sheets("sheet2-Annual").Columns("J").Copy Destination:=Sheets("Acting").Columns("L")
    Sheets("Acting").Columns("L").Value = Sheets("sheet2-Annual").Columns("J").Value
    Sheets("sheet1-Oct 2011").Columns("K").Copy Destination:=Sheets("Acting").Columns("M")
    Sheets("Acting").Columns("M").Value = Sheets("sheet1-Oct 2011").Columns("K").Value
    Sheets("sheet2-Annual").Columns("K").Copy Destination:=Sheets("Acting").Columns("N")
    Sheets("Acting").Columns("N").Value = Sheets("sheet2-Annual").Columns("K").Value
    Sheets("sheet1-Oct 2011").Columns("AD").Copy Destination:=Sheets("Acting").Columns("O")
    Sheets("Acting").Columns("O").Value = Sheets("sheet1-Oct 2011").Columns("AD").Value
    Sheets("sheet2-Annual").Columns("AD").Copy Destination:=Sheets("Acting").Columns("P")
    Sheets("Acting").Columns("P").Value = Sheets("sheet2-Annual").Columns("AD").Value
    Sheets("sheet1-Oct 2011").Columns("AE").Copy Destination:=Sheets("Acting").Columns("Q")
    Sheets("Acting").Columns("Q").Value = Sheets("sheet1-Oct 2011").Columns("AE").Value
    Sheets("sheet2-Annual").Columns("AE").Copy Destination:=Sheets("Acting").Columns("R")
    Sheets("Acting").Columns("R").Value = Sheets("sheet2-Annual").Columns("AE").Value
    Sheets("sheet1-Oct 2011").Columns("AF").Copy Destination:=Sheets("Acting").Columns("S")
    Sheets("Acting").Columns("S").Value = Sheets("sheet1-Oct 2011").Columns("AF").Value
    Sheets("sheet2-Annual").Columns("AF").Copy Destination:=Sheets("Acting").Columns("T")
    Sheets("Acting").Columns("T").Value = Sheets("sheet2-Annual").Columns("AF").Value
    Sheets("sheet1-Oct 2011").Columns("AG").Copy Destination:=Sheets("Acting").Columns("U")
    Sheets("Acting").Columns("U").Value = Sheets("sheet1-Oct 2011").Columns("AG").Value
    Sheets("sheet2-Annual").Columns("AG").Copy Destination:=Sheets("Acting").Columns("V")
    Sheets("Acting").Columns("V").Value = Sheets("sheet2-Annual").Columns("AG").Value
    Sheets("sheet1-Oct 2011").Columns("AH").Copy Destination:=Sheets("Acting").Columns("W")
    Sheets("Acting").Columns("W").Value = Sheets("sheet1-Oct 2011").Columns("AH").Value
    Sheets("sheet2-Annual").Columns("AH").Copy Destination:=Sheets("Acting").Columns("X")
    Sheets("Acting").Columns("X").Value = Sheets("sheet2-Annual").Columns("AH").Value

End Sub

**Greed** · 12-06-2011, 03:24 PM

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** · 12-06-2011, 03:31 PM

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

Sheets("Acting").Columns("C").Value = Activesheet.Columns("E").Value

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

ActiveSheet.Rows(1).copy Destination:=Sheets("Acting").Rows(1)

**Greed** · 12-06-2011, 04:13 PM

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

    Worksheets.Add().Name = "Acting"
    ActiveSheet.Rows(1).Copy Destination:=Sheets("Acting").Rows(1)
    Sheets("Annual 2011").Columns("A:B").Copy Destination:=Sheets("Acting").Columns("A:B")
    ActiveSheet.Columns("E").Copy Destination:=Sheets("Acting").Columns("C")
    Sheets("Acting").Columns("C").Value = ActiveSheet.Columns("E").Value
    Sheets("Annual 2011").Columns("E").Copy Destination:=Sheets("Acting").Columns("D")
    Sheets("Acting").Columns("D").Value = Sheets("Annual 2011").Columns("E").Value
    ActiveSheet.Columns("F").Copy Destination:=Sheets("Acting").Columns("E")

**jomili** · 12-06-2011, 04:48 PM

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:

Worksheets.Add().Name = "Acting"
    Sheets("Oct 2011").activate
    Sheets("Annual 2011").Columns("A:B").Copy Destination:=Sheets("Acting").Columns("A:B")
    ActiveSheet.Columns("E").Copy Destination:=Sheets("Acting").Columns("C")
    Sheets("Acting").Columns("C").Value = ActiveSheet.Columns("E").Value
    Sheets("Annual 2011").Columns("E").Copy Destination:=Sheets("Acting").Columns("D")
    Sheets("Acting").Columns("D").Value = Sheets("Annual 2011").Columns("E").Value
    ActiveSheet.Columns("F").Copy Destination:=Sheets("Acting").Columns("E")
    ActiveSheet.Rows(1).Copy Destination:=Sheets("Acting").Rows(1)