Move data from one excel worksheet to another with a macro

bshaggy6

Board Regular
Joined
Jun 16, 2015
Messages
56
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
 
Last edited:

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Gary McMaster

Well-known Member
Joined
Feb 8, 2009
Messages
1,977
Hi bshaggy,

Welcome to Mr. Excel!

The procedure below can be shortened considerably if you create all 31 sheets (numbered 1 thru 31) ahead of time. Most of the "On Error" stuff is to handle the error generated if one of the needed sheets does not exist. As it is, it will create the "numbered sheet" if it does not already exist. Run the procedure from your "Report Calculator" sheet.

In a standard module:
Code:
Public Sub Test()

Dim oCell As Range
Dim oTarget As Range
Dim oDay As Worksheet
Dim oActive As Worksheet
Dim sToday As String

sToday = Format(Now, "dd")

Set oActive = ActiveSheet

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)
    If Err Then
        ThisWorkbook.Worksheets.Add
        Set oDay = ActiveSheet
        oDay.Name = sToday
        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

End Sub
 

bshaggy6

Board Regular
Joined
Jun 16, 2015
Messages
56
Hi Gary,

I appreciate you taking the time to help me. When I run the code from the Report Calculator sheet, it turns the values on the report calculator sheet all to 0. Let me try and be more specific on the issue. On the first sheet is a template where employees can copy and paste results from cells A3:AN9999 lets say. On sheet 2 labeled "Report Calculator", I made a table with the countif function to take data from sheet 1 and count the necessary values that my boss wants. I then created 31 other sheets labeled 1-31. Since these reports come in daily, I need a macro that will take the results from the "Report Calculator" sheet, which autopopulates data from the countif formula, and move it to sheet "1" if its from say june 1, or sheet "2" if the report is from june 2. Is there a way to build a macro that does that and then references other cells where someone would enter the date in and then the macro will route the data from the report calculator sheet to the numbered sheet based on what date is inputed in those cells? FYI I made a spot for someone to enter the day of the month in cell H5 on the Report calculator tab. I know this is alot to ask. So i appreciate any help you can give me. Thanks again.
 

Gary McMaster

Well-known Member
Joined
Feb 8, 2009
Messages
1,977
I believe I mis-interpreted "push it to another sheet" as move. This code is copying A3:D3, A7:D7, A11:C11, A15:B15, and C18 to another sheet and then clearing the contents of A3:D3, A7:D7, A11:C11, A15:B15, and C18 on the original sheet. If you have formulas there it should leave them alone.

Try commenting (put an apostrophe in front of) the last line as in the following and see if that works:

Code:
'oTarget.ClearContents

If it works then you can delete the above line.
 

bshaggy6

Board Regular
Joined
Jun 16, 2015
Messages
56

ADVERTISEMENT

Ok so now the data isn't clearing from the report calculator which is good. But it isnt moving the data to any other sheets. The thing is, I dont want to move the data to all 31 sheets. Ideally, i want the report to come in on that day, have it calculate in the report calculator sheet and then run a macro that can move it to sheet X based on what day it is. Is it possible to insert a tool from the developer tab that can make the macro move to a specific sheet based on the date? Thanks for your help.
 

Gary McMaster

Well-known Member
Joined
Feb 8, 2009
Messages
1,977
It should be copying A3:D3, A7:D7, A11:C11, A15:B15, and C18 from "Report Calculator" (which must be active when you run the procedure) to a sheet named "16" because today is the 16th day of the month. If a sheet named "16" does not exist it should create it. It will copy to sheet "17" tomorrow ...
 

bshaggy6

Board Regular
Joined
Jun 16, 2015
Messages
56

ADVERTISEMENT

Thank you Gary! I will try again tomorrow at work. One more quick question is how would i link a button to the macro so that i could just click it from the workbook instead of having to go into my macros and run it?
 

Gary McMaster

Well-known Member
Joined
Feb 8, 2009
Messages
1,977
how would i link a button to the macro so that i could just click it from the workbook instead of having to go into my macros and run it?

Put a command button on the worksheet. In the "Click event" of that button key in the name of the procedure, "Test". This assumes that you have not changed the name of the procedure and have placed it in a standard module. You may also see the code to invoke a procedure written as "Call Test"

You could also use one of the shapes for a button. Insert a shape, right click on that shape and then select "Assign Macro" from the pop menu. The name of the procedure should appear in the "Assign Macro" dialog box. Select the procedure you want to run when the shape is clicked from the list.
 

Gary McMaster

Well-known Member
Joined
Feb 8, 2009
Messages
1,977
It's good to hear it worked for you. Thanks for the feedback.

As I mentioned in post #2, you don't need a lot of this sample code if you can guarantee that all 31 sheets will always exist and will never be renamed. Below is the same sample with the stuff you don't need commented if you can be sure all sheets will always exist. You can delete all of the lines preceded by an apostrophe or "Rem" (Remark). Rem and apostrophe both indicate a non executable comment.

Code:
Public Sub Test()

Dim oCell As Range
Dim oTarget As Range
Dim oDay As Worksheet
'Dim oActive As Worksheet
Dim sToday As String

sToday = Format(Now, "dd") ' Get the 1 or 2 digits representing the day number (1 to 31) from the system date

'Set oActive = ActiveSheet

Set oTarget = Union(Range("A3:D3"), Range("A7:D7"), Range("A11:C11"), Range("A15:B15"), Range("C18")) ' Combine ranges to be copied into single range

'On Error Resume Next
    Set oDay = Worksheets(sToday) 'Reference existing worksheet named 1 thru 31 depending on day of the month in variable sToday
'    If Err Then
'        ThisWorkbook.Worksheets.Add
Rem        Set oDay = ActiveSheet
Rem        oDay.Name = sToday
'        oActive.Activate
'        Err.Clear
'    End If
'On Error GoTo 0

For Each oCell In oTarget ' Visit each cell in the combined range
    oDay.Range(oCell.Address) = oCell.Value ' Copy the value in each cell of the combined range to the same cell address on the sheet named the same as today's date
Next oCell ' Continue looping through previous two lines until all cells in the combined range have been processed

'oTarget.ClearContents 'Removed per post #4

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,335
Messages
5,595,573
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top