monthly tracker of reported info from several sheets to 1!

User avatar
luv2bounce
StarLounger
Posts: 59
Joined: 07 Dec 2010, 12:21

monthly tracker of reported info from several sheets to 1!

Post by luv2bounce »

I tried to search to see if there was a similar post on this, but didn't even know where to begin. So I am just making a new post to explain best I can. Apoplogies in advance if I am duplicating something that is already out there.

We have some management information that is compiled from CRM download of a database. Each month the figures are updated for the overall group and for each business unit as a separate tab in a spreadsheet. The last spreadsheet tab takes 3 key figures for each business unit and tracks them so you can see changes month to month. The group and BU tabs are overwritten with the new data each month (generated from pivot tables). I would like to automate this as it takes ages to do as the cells are not even next to each other to cut and paste. I thought I could set a variable at the top of the page to indicate the current month and hence to copy the data from the other sheets into that column. I then recorded a macro to copy and paste an example month and thought that I would be able to use some form of case statement to use the variable month in cell B1 to tell the macro where to paste the data. But I don't know how to set the case variable to the contents of cell B1.

Below is my work in progress. Can any of the clever people out there help me out? If someone can get me started, I'm sure I can figure the rest out, but I don't know enough about VBA to set it up? You will probably notice a lot of stuff that isn't needed in the code as I built it from recording what I was doing.... :hairout:

Thank you so much for your help in advance!!!!! This place has saved me so many times and is really, really appreciated!!!!!

Code: Select all

Sub EvolutionTracker(mth As String)
'
' EvolutionTracker Macro
' Enables tracking of pipeline changes over time
'

'
'Determine where to copy information for the month being reported
    Select Case mth
        Case October

    Sheets("CPIGroupCharts").Select
    Range("C8").Select
    Selection.Copy
    Sheets("Pipeline Evolution").Select
    Range("J3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("CPIGroupCharts").Select
    Range("C15").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Pipeline Evolution").Select
    Range("J4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("CPIGroupCharts").Select
    Range("C22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Pipeline Evolution").Select
    Range("J5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Formulation").Select
    Range("C8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Pipeline Evolution").Select
    Range("J15").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Formulation").Select
    Range("C15").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Pipeline Evolution").Select
    Range("J16").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Formulation").Select
    Range("C22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Pipeline Evolution").Select
    Range("J17").Select
    Sheets("Electronics").Select
    Range("C8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Pipeline Evolution").Select
    Range("J19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Electronics").Select
    Range("C15").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Pipeline Evolution").Select
    Range("J20").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Electronics").Select
    Range("C22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Pipeline Evolution").Select
    Range("J21").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Photonics").Select
    Range("C8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Pipeline Evolution").Select
    Range("J23").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Photonics").Select
    Range("C15").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Pipeline Evolution").Select
    Range("J24").Select
    Sheets("Photonics").Select
    Range("C22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Pipeline Evolution").Select
    Range("J25").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("MMIC").Select
    ActiveWindow.SmallScroll Down:=-9
    Range("C8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Pipeline Evolution").Select
    Range("J27").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("MMIC").Select
    Range("C15").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Pipeline Evolution").Select
    Range("J28").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("MMIC").Select
    Range("C22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Pipeline Evolution").Select
    Range("J29").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: monthly tracker of reported info from several sheets to 1!

Post by HansV »

Here is a streamlined version of your macro. It could probably be streamlined further, but I don't know what exactly you want to do. Feel free to post back.

Code: Select all

Sub EvolutionTracker()
'
' EvolutionTracker Macro
' Enables tracking of pipeline changes over time
'

'
'Determine where to copy information for the month being reported
    Dim mth As String
    Dim w1 As Worksheet
    Dim w2 As Worksheet
    Dim w3 As Worksheet
    Dim w4 As Worksheet
    Dim w5 As Worksheet
    Dim w6 As Worksheet
    Set w1 = Worksheets("Pipeline Evolution")
    Set w2 = Worksheets("CPIGroupCharts")
    Set w3 = Worksheets("Formulation")
    Set w4 = Worksheets("Electronics")
    Set w5 = Worksheets("Photonics")
    Set w6 = Worksheets("MMIC")
    mth = w1.Range("B1").Value
    Select Case mth
        Case "October"
            w1.Range("J3").Value = w2.Range("C8").Value
            w1.Range("J4").Value = w2.Range("C15").Value
            w1.Range("J5").Value = w2.Range("C22").Value
            w1.Range("J15").Value = w3.Range("C8").Value
            w1.Range("J16").Value = w3.Range("C15").Value
            w1.Range("J17").Value = w3.Range("C22").Value
            w1.Range("J19").Value = w4.Range("C8").Value
            w1.Range("J20").Value = w4.Range("C15").Value
            w1.Range("J21").Value = w4.Range("C22").Value
            w1.Range("J23").Value = w5.Range("C8").Value
            w1.Range("J24").Value = w5.Range("C15").Value
            w1.Range("J25").Value = w5.Range("C22").Value
            w1.Range("J27").Value = w6.Range("C8").Value
            w1.Range("J28").Value = w6.Range("C15").Value
            w1.Range("J29").Value = w6.Range("C22").Value
    End Select
End Sub
Best wishes,
Hans

User avatar
luv2bounce
StarLounger
Posts: 59
Joined: 07 Dec 2010, 12:21

Re: monthly tracker of reported info from several sheets to 1!

Post by luv2bounce »

You really are the best! Thank you so much for your help!!!! I will try it and have a play and when I get really stuck and it all falls apart because I've broken it, I'll let you know! :clapping: :clapping: :clapping: :clapping: What I do love is how much I learn this way so double thanks!

User avatar
luv2bounce
StarLounger
Posts: 59
Joined: 07 Dec 2010, 12:21

Re: monthly tracker of reported info from several sheets to 1!

Post by luv2bounce »

Also - I love how nice and neat it looks!!!!!

User avatar
luv2bounce
StarLounger
Posts: 59
Joined: 07 Dec 2010, 12:21

Re: monthly tracker of reported info from several sheets to 1!

Post by luv2bounce »

It works perfectly!!!!! Thank you so much once again!!!!! :fanfare: :fanfare: :fanfare: :fanfare: :clapping: :cheers:

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: monthly tracker of reported info from several sheets to 1!

Post by HansV »

Glad to hear that! :thumbup:
Best wishes,
Hans