Copy and Paste value if date matches

A

Aunty Jean

Board Regular
Joined
Apr 6, 2005
Messages
55
Sorry, not good at VBA, haven't done it for years now. Have searched threads and tried adapting but nothing working.

Have a workbook with first tab containing a template, the second tab is where data is entered for today and the sum of the items is carried to a third tab for that month
The month sheet has a date for every day in one column and captures the Total value for today in the adjacent column by comparing date (=IF(A2='Stock Check'!$A$2,'Stock Check'!$L$17,0))

Obviously this is blank when the date changes and therefore I need to capture the value for that date in say, column C to retain it's value when date changes.

Have managed to run a macro to copy values from column B to C and retain that value if changed in B but, I only need it to activate on each cell in C when it is today's date and save that value without it being overridden.

The macro would need to be able to be run for each month (separate tabs) and take into consideration some months have less days than others. I was thinking of active cell but got into a mess and looked like I would have to have a macro for each individual date......... So my idea was along the lines of a formula, IF(A2='Stock Check'!$A$2, copy B2 and paste value to C2, but only if B2 empty.. Aware that this can't be done in a formula, just wanted to give an idea of the logic.

Any suggestions welcome
 

Attachments

  • Stock Sheet.JPG
    Stock Sheet.JPG
    42.4 KB · Views: 77

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Hello Jean,

Since you are 'not good at VBA' I have done this in a less succinct way; so it is easier for you to follow. {{IF(A2='Stock Check'!$A$2, copy B2 and paste value to C2, but only if B2 empty}} I assume you meant C2 is empty: but you can change the code to fit. Cells(2, 4) is Cell D2 - the row is the first number.

NB:- You will have to put the number of days of each month into a cell on each months tab - Any cell away from the data, maybe Z1. (It just makes things easier for you to follow)
NB - Stock Check'!$A$2 Need to always be A2 then change If Sheets("Stock Check").Cells(i, 1).Value to If Sheets("Stock Check").Cells(2, 1).Value

The first Macro:-

Excel Formula: 
Sub PasteifEmpty()
Dim i As Integer
i = 1  ' i is the row number that changes by 1 for each loop

For i = 2 To Range("Z1").Value   'loops from A2 to the number of days in cell Z1 (You can change that to anywhere)
    If Cells(i, 1).Value = Sheets("Stock Check").Cells(i, 1).Value Then 'checks the A2 values match
    If Cells(i, 3).Value = "" Then 'checks that C2 is empty (you can replace "" with 0)
    Cells(i, 2).Copy 'Copies B2
    Cells(i, 3).Select 'Selects and pastes values to C2
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End If
    End If
Next i 'Loops through rows 2,3,4,5,...
Application.CutCopyMode = False
End Sub

The Second Macro

VBA Code: 
Sub EachMonth()
Application.ScreenUpdating = False 'Turns off screen updating - runs faster
Sheets("Jan").Select 'selects each tab in turn and then runs the above code
Call PasteifEmpty
Sheets("Feb").Select
Call PasteifEmpty
Sheets("Mar").Select
Call PasteifEmpty
Sheets("April").Select
Call PasteifEmpty
Sheets("May").Select
Call PasteifEmpty
Sheets("June").Select
Call PasteifEmpty
Sheets("July").Select
Call PasteifEmpty
Sheets("Aug").Select
Call PasteifEmpty
Sheets("Sept").Select
Call PasteifEmpty
Sheets("Nov").Select
Call PasteifEmpty
Sheets("Dec").Select
Call PasteifEmpty
Sheets("Sheet1").Select 'selects the original sheet - change the name to fit
Application.ScreenUpdating = True 'Turns on screen updating again
End Sub

You can delete everything in green - they are just comments Excel doesn't read them.

Hopefully you can follow it.

Jamie McMillan
 
Upvote 0
Solution
Jamie, thank you so much. Like your logic with regard to C2 empty as opposed to B2. I didn't think of that! I have slightly adjusted as Sheets are named Jan 2022, Feb 2022 and added number of days to Z1 cell for each tab as suggest (also added in Oct as you missed a month).

Tried added each code as separate module and assumed only second macro needed a button added to Stock Sheet where data/total is. Ran it but nothing appearing in column 3 on Jan 2022. Then added second code to Module 1 and removed Module 2 and ran again, C column still blank. Thought perhaps to run first code then second but still blank. No runtime error so can't think what I am doing wrong?
 
Upvote 0
Hello Jean,

"October was a holiday". :) :) :)

I ran the code and it worked fine here. (It doesn't matter what module they are in; Excel will find the Macro name). The second code only needs a button, that's right.

If it is only, 'but nothing appearing in column 3 on Jan 2022' - the Jan 2022 tab that does not show anything, then check the Z1 value on that sheet - maybe you put the value in Z2: so the Z1 value = 0, there won't be an error message as the code will loop zero times. (I have done similar many times).

Otherwise I can't see anything wrong: the tabs must be named correctly, or you'd get an error. If Feb, Mar, etc. are filling as you wish then the code is running right. So it has to be something in the Jan 2022 tab. (Z1 is the only value that is needed, :) ).

I think it is the Z1 value.

Let me know if that is right. (Also if it is NOT only, Jan 2022, then it will be something different.)

Jamie
 
Upvote 0
Here is the code I have adapted.


VBA Code: 
Sub PasteifEmpty()
Dim i As Integer
i = 1  ' i is the row number that changes by 1 for each loop

For i = 2 To Range("Z1").Value   'loops from A2 to the number of days in cell Z1 (You can change that to anywhere)
    If Cells(i, 1).Value = Sheets("Stock Check").Cells(i, 1).Value Then 'checks the A2 values match
    If Cells(i, 3).Value = "" Then 'checks that C2 is empty (you can replace "" with 0)
    Cells(i, 2).Copy 'Copies B2
    Cells(i, 3).Select 'Selects and pastes values to C2
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End If
    End If
Next i 'Loops through rows 2,3,4,5,...
Application.CutCopyMode = False
End Sub
Sub EachMonth()
Application.ScreenUpdating = False 'Turns off screen updating - runs faster
Sheets("Jan 2022").Select 'selects each tab in turn and then runs the above code
Call PasteifEmpty
Sheets("Feb 2022").Select
Call PasteifEmpty
Sheets("Mar 2022").Select
Call PasteifEmpty
Sheets("Apr 2022").Select
Call PasteifEmpty
Sheets("May 2022").Select
Call PasteifEmpty
Sheets("Jun 2022").Select
Call PasteifEmpty
Sheets("Jul 2022").Select
Call PasteifEmpty
Sheets("Aug 2022").Select
Call PasteifEmpty
Sheets("Sep 2022").Select
Call PasteifEmpty
Sheets("Oct 2022").Select
Call PasteifEmpty
Sheets("Nov 2022").Select
Call PasteifEmpty
Sheets("Dec 2022").Select
Call PasteifEmpty
Sheets("Stock Check").Select 'selects the original sheet - change the name to fit
Application.ScreenUpdating = True 'Turns on screen updating again
End Sub

(Sorry, not sure if I have inserted code correctly)

Here are the screen shots after running the macro. I have ensured the Z1 is a number format at the dates date format. Ensured saved as a macro enabled workbook......

Just wondering if it has anything to do with values in Stock Sheet B2 to B32 is a formula? '=IF(A2='Stock Check'!$A$2,'Stock Check'!$L$17,0)
 

Attachments

  • Screen 1.JPG
    Screen 1.JPG
    169 KB · Views: 56
  • Screen 2.JPG
    Screen 2.JPG
    125.7 KB · Views: 53
Last edited:
Upvote 0
Hello Jean,

The formula shouldn't make a difference, I used random number formulas.

The code you posted works fine.

Is the text in Column C on Jan 2022 set to white???

Does it work right for all the other months, Feb - Dec??? {I have a strange Idea. :)

Jamie
 
Upvote 0
Jamie McMillan said:
Hello Jean,

The formula shouldn't make a difference, I used random number formulas.

The code you posted works fine.

Is the text in Column C on Jan 2022 set to white???

Does it work right for all the other months, Feb - Dec??? {I have a strange Idea. :)

Jamie
Click to expand...
Sorry, checked and no, text not set to white. I can not for the life of me work out why it is not working. Not working on any dates/months?
 
Upvote 0
Aunty Jean said:
Sorry, checked and no, text not set to white. I can not for the life of me work out why it is not working. Not working on any dates/months?
Click to expand...
Hello Jean,

If you create a new file (a test file). Put in the tabs Stock Check, Jan, Feb, Mar, April, May. Put some random data in Columns A & B, on each sheet. (You can just select all the sheets and type it in once. Change some data in each month; so it does not match. Then run the Macros below. They work.

Then when it is working, star changing the data in one month to match what is in your spreadsheet. Then you should track down where it is going wrong. :) (I hope)

VBA Code: 
Sub EachMon()
Application.ScreenUpdating = False 'Turns off screen updating - runs faster
Sheets("Jan").Select 'selects each tab in turn and then runs the above code
Call PasteifEmpty
Sheets("Feb").Select
Call PasteifEmpty
Sheets("Mar").Select
Call PasteifEmpty
Sheets("April").Select
Call PasteifEmpty
Sheets("May").Select
Call PasteifEmpty
Sheets("Stock Check").Select 'selects the original sheet - change the name to fit
Application.ScreenUpdating = True 'Turns on screen updating again
Application.CutCopyMode = False

End Sub
Sub PasteifEmpty()
Dim i As Integer
i = 1  ' i is the row number that changes by 1 for each loop

For i = 2 To Range("Z1").Value   'loops from A2 to the number of days in cell Z1 (You can change that to anywhere)
    If Cells(i, 1).Value = Sheets("Stock Check").Cells(i, 1).Value Then 'checks the A2 values match
    If Cells(i, 3).Value = 0 Then 'checks that C2 is empty (you can replace "" with 0)
    Cells(i, 2).Copy 'Copies B2
    Cells(i, 3).Select 'Selects and pastes values to C2
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End If
    End If
Next i 'Loops through rows 2,3,4,5,...
End Sub

Jamie
 

Attachments

  • Jean.jpg
    Jean.jpg
    74.6 KB · Views: 16
Upvote 0
Jamie McMillan said:
Hello Jean,

If you create a new file (a test file). Put in the tabs Stock Check, Jan, Feb, Mar, April, May. Put some random data in Columns A & B, on each sheet. (You can just select all the sheets and type it in once. Change some data in each month; so it does not match. Then run the Macros below. They work.

Then when it is working, star changing the data in one month to match what is in your spreadsheet. Then you should track down where it is going wrong. :) (I hope)

VBA Code: 
Sub EachMon()
Application.ScreenUpdating = False 'Turns off screen updating - runs faster
Sheets("Jan").Select 'selects each tab in turn and then runs the above code
Call PasteifEmpty
Sheets("Feb").Select
Call PasteifEmpty
Sheets("Mar").Select
Call PasteifEmpty
Sheets("April").Select
Call PasteifEmpty
Sheets("May").Select
Call PasteifEmpty
Sheets("Stock Check").Select 'selects the original sheet - change the name to fit
Application.ScreenUpdating = True 'Turns on screen updating again
Application.CutCopyMode = False

End Sub
Sub PasteifEmpty()
Dim i As Integer
i = 1  ' i is the row number that changes by 1 for each loop

For i = 2 To Range("Z1").Value   'loops from A2 to the number of days in cell Z1 (You can change that to anywhere)
    If Cells(i, 1).Value = Sheets("Stock Check").Cells(i, 1).Value Then 'checks the A2 values match
    If Cells(i, 3).Value = 0 Then 'checks that C2 is empty (you can replace "" with 0)
    Cells(i, 2).Copy 'Copies B2
    Cells(i, 3).Select 'Selects and pastes values to C2
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End If
    End If
Next i 'Loops through rows 2,3,4,5,...
End Sub

Jamie
Click to expand...
So sorry, tried with new Test workbook and still not working. Is it supposed to paste to the relevant month Cell Cx? Gonna give up meethinks and work on the base you have provided when I have some time. Am sure I will have a lightbulb moment at some stage. Thank you so much for trying.
 
Upvote 0
Hello Jean,

The images below show exactly how the "Test File" should look, and where the values are pasted after the macro runs.

Sorry it is late - I posted it in the wrong thread. :)

Jamie
 

Attachments

  • Slide1.JPG
    Slide1.JPG
    161 KB · Views: 25
  • Slide2.JPG
    Slide2.JPG
    159.5 KB · Views: 26
  • Slide3.JPG
    Slide3.JPG
    165.1 KB · Views: 26
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Update formula to check other conditions
Replies
1
Views
78
etaf
etaf
B
  • Question
Copying data from a year tab and pasting into month tabs
Replies
2
Views
164
B-radK
B
Agnarr
  • Question
VBA problem with creating new sheets
Replies
0
Views
333
Agnarr
Agnarr
I
  • Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
187
Insurance Dave
I
J
  • Solved
Sum FTEs based on start/end date
Replies
3
Views
161
etaf
etaf

Forum statistics

Threads
1,214,413
Messages
6,119,374
Members
448,888
Latest member
Arle8907

Share this page

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
Back
Top