VBA to copy a range and place it in a specific sheet based on cell

[tyler797]

Hello, I've searched everywhere but can't quite find the exact solution. Basically I have a month in A1. I want to code a macro that will check A1 and then copy the worksheet, A1-E34 to the specific sheet that equals A1. I have 12 tabs, one for each month ready to be pasted into. For instance if January is in A1, I want the macro to copy the whole sheet and paste (values only, the sheet is live with formulas) to the correct sheet, January. Here is the recorded code I have but right now its set to paste to the RawData sheet. I cant get it to actually be the correct sheet, I just cant figure out how to get the code to look at A1 first.

Thanks for your help.


Sub CopyPaste()

' CopyPaste Macro


Sheets("MirrorCoverSheet").Select
Range("A1:E34").Select
Selection.Copy
Sheets("RawData").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select

 

[Marcelo Branco]

Hi,

Maybe this

Sub CopyPaste()
' CopyPaste Macro
    Dim wkOrigin As Worksheet, wkDest As Worksheet
 
    Set wkOrigin = Sheets("RawData")
    Set wkDest = Sheets(wkOrigin.Range("A1").Text)
    wkOrigin.Range("A1:E34").Copy
    wkDest.Range("A1").PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    wkDest.Activate
    wkDest.Range("A1").Select
    Application.CutCopyMode = False
End Sub

HTH

M.

 

[tyler797]

Hi thanks for responding. Its not quite what I'm trying to do. I dont want the RawData tab anymore its just a random sheet I've been using. I want the code to look at the month in the cell, then pick the matching work sheet and paste everything to that sheet. So month january in A1 will select the whole sheet and paste to worksheet "january"

 

[tyler797]

can anyone help me out? thanks

 

[Marcelo Branco]

I want the code to look at the month in the cell, then pick the matching work sheet and paste everything to that sheet. So month january in A1 will select the whole sheet and paste to worksheet "january"

If you are not using the sheet RawData anymore, in which sheet is A1("January" for example)?

M.

 

[tyler797]

Sorry, I'll provide a bit more information, it might make it easier to solve the problem. This spreadsheet is a forecasting sheet that a user keys in their forecast for product sales for a month. Sheet "Cover Sheet" is where a user keys in a Month, using a series of if statements the sheet pulls information from previous years to help the user make their decision for that month. The "Cover Sheet" is full of useless additional columns and merges to make it look nice, so I have a sheet that pulls the barebone data to a hidden sheet called "MirrorCoverSheet". This sheet I want to take a snapshot of and copy paste values only to the correct monthly tab. I have a macro right now that works perfectly that creates a new sheet, copy pastes, and renames that sheet. However its not useful for me because its a one time deal and if the user decides to update their forecast they cant overwrite that data. The macro is super long and complex. The new one im trying to do will work perfectly and is so compact that if I can figure out how to paste to the correct sheet it can easily just re paste over the data in it.

You probably dont need the rest of this information but I will then have an analyses sheet that will pull the snapshot data with vlookups and compare forecasts to actual.

So the main sheet the macro will be getting its data from is "MirrorCoverSheet". The cell it is looking at is A1. The Sheets to paste values only to are "January", "February", etc. The range is A1:E34, the range does not change.

Thanks again

 

[Marcelo Branco]

Not sure if i fully understand your explanation, but if you need to refer to a sheet whose name is in A1 maybe this
Sheets(Range("A1").Text)

M.