Move data from one excel worksheet to another with a macro

[bshaggy6]

Hello,

I am very new when it comes to building macros. And am not really familiar with how to build one. But im trying to make one that will automatically copy data from one sheet labeled "Report Calculator" and push it to another sheet in the same workbook labeled by dates. My sheets are labelled "1", "2","3" etc with each number representing the day in the month....Is it also possible to add a filter to the macro so that it will push the data to the worksheets based on dates? My apologies if what I'm asking is confusing. The cells with Data that need to be moved are A3:D3, A7:D7, A11:C11, A15:B15, and C18. I want the values in these cells to move to the same exact cells except on a different Worksheet. Thanks in advance for any help

 

[bshaggy6]

Ok. I think for the time being I am going to leave it as is. But i will definitley save that code just in case. I appreciate all of your help!

 

[bshaggy6]

Hi again Gary,

So the macro works extremely well and does exactly how you said but there is one modification that my boss wants me to make to it. Instead of having it push the data automotically to whatever the day is, they want to be selective as to which day it goes in. For auditing purposes, some reports that come in on a saturday lets say, need to be reported as having come in on that day. But chances of us being in the office on a saturday are highly unlikely.

What I'm asking is, can we revise the macro to allow the user to choose what date to send the data to? I appreciate any help in advance. Thanks!

 

[bshaggy6]

I realized it might be easier to instead have the macro do this: In cell I9 on the "Report Calculator Tab" I would put a number between 1 and 31. Whichever number I put, it would push the data to that tab in those same cells that the macro already sends them too. Could you help me with that?

 

[Gary McMaster]

Try the following with the destination work sheet name in cell "I9" of the worksheet named "Report Calculator". If I9 is empty or contains an illegal name it should create a new sheet "Sheetx<x>" named the same way the system always names new sheets.

Code that has changed is noted in the comments. You can delete any comments at will. Comments are highlighted in green (by default) in the VBA IDE.

Public Sub Test()

'Code modified from post #2
Dim oCell As Range
Dim oTarget As Range
Dim oDay As Worksheet
Dim oActive As Worksheet
'Dim sToday As String ' No longer used per post #13

'sToday = Format(Now, "dd") 'Removed per post #13 to get name from cell

'Set oActive = ActiveSheet 'Modified per post #13 to use sheet name given by original poster
Set oActive = ThisWorkbook.Worksheets("Report Calculator")

Set oTarget = Union(Range("A3:D3"), Range("A7:D7"), Range("A11:C11"), Range("A15:B15"), Range("C18"))

On Error Resume Next
    'Set oDay = Worksheets(sToday) ' Modified to following line per post #13
    Set oDay = Worksheets(oActive.Range("I9").Text) ' Get sheet name from Cell I9
    If Err Then
        ThisWorkbook.Worksheets.Add
        Set oDay = ActiveSheet
        'oDay.Name = sToday
        oDay.Name = oActive.Range("I9").Text ' Modified from previous line per post #13
        oActive.Activate
        Err.Clear
    End If
On Error GoTo 0

For Each oCell In oTarget
    oDay.Range(oCell.Address) = oCell.Value
Next oCell

'oTarget.ClearContents 'Removed per post #4

End Sub

</x>

 

[bshaggy6]

Thank you Gary McMaster! Works like a charm!