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....
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