Look up todays date and special copy paste value for multiple sheets

[SebBev2024]

Hi,

I have a work book that I use to predict my stock levels given a average. I have one sheet in the work book for each product. The name of each sheet is the product name. There are 40 sheets (40 products)

On each sheet there is a table. The first two columns (A and B) are "Date" , "Daily count". With the actual date values and count values starting on A2 and B2 respectively.

I am using a formula to look up todays date in column A, If it is today, the count for the day is displayed in the cell next to it in column B, if not the predicted stock level is displayed.

The problem I am having is everyday I have to manually go to each sheet and copy the cell and paste as value to retain the cells value on that day. This is time consuming because of the number of sheets in the work book. I was wondering if there would be a way to automate this process? I have included an image of a sample sheet.

If you need any additional information, let me know. First time posting, hope I've don an alright job.

Also if you do have a solution you can provide, also please give a short explanation it or point me to a resource to learn from.

 

[OaklandJim]

I'd be willing to try to assist. Unfortunately despite your good attempt to characterize the need; what you are asking for is not totally clear.

As far I can tell, in each product worksheet there are a bunch of dates (in column (A) each with a corresponding daily count (in column B). The daily count may be an actual value or a predicted value. If the date, say in cell A4 as an example, is the current date (i.e., "today") then a lookup formula (in B4) pulls in the actual count for that date. But if the date in A4 is not the current date the predicted count is pulled into the cell (B4).

So for ALL dates that are not the current date the count values are always predicted counts? Or is the count for "past dates" always an actual value?

Where are the actual and predicted counts located?

Maybe try again to characterize the "problem." Based on what you wrote (everyday I have to manually go to each sheet and copy the cell and paste as value to retain the cells value on that day) I really cannot tell how to help to address the issue. What cell is copied? From where? To where? How does doing that copying and pasting address the problem (need to retain the cells value on that day). Retain the value of which cells?

I could be way off but here is my guess about what is needed. A formula in B4 (using the example) has three "conditions." 1. date has passed and the lookup pulls in the actual count for that date. 2. If the date is the current date, the lookup pulls in the actual count. (If I'm correct, processing for dates past and the current date are the same?) 3. For future dates the formula looks up the predicted count.

Is there a chance that you could post a sample workbook with say two or three products and the data source (for actual or predicted counts)? Use Dropbox, 1Drive or? That makes helping a lot easier!

Jim

 

[mumps]

If I understood correctly, place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Close the window to return to your sheet. Save the workbook as a macro-enabled file and then close it. Each day when you open the file, the cells in column B that have today's date in column A, will be changed to the values.

Private Sub Workbook_Open()
    With Sheets("Sheet1")
        .Range("A1", .Range("B" & .Rows.Count).End(xlUp)).AutoFilter Field:=1, Criteria1:=1, Operator:=11, Criteria2:=0, SubField:=0
        .Range("B2", .Range("B" & .Rows.Count).End(xlUp)).SpecialCells(xlVisible).Value = Range("B2", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlVisible).Value
        .Range("A1").AutoFilter
    End With
End Sub

 

[SebBev2024]

I'd be willing to try to assist. Unfortunately despite your good attempt to characterize the need; what you are asking for is not totally clear.

As far I can tell, in each product worksheet there are a bunch of dates (in column (A) each with a corresponding daily count (in column B). The daily count may be an actual value or a predicted value. If the date, say in cell A4 as an example, is the current date (i.e., "today") then a lookup formula (in B4) pulls in the actual count for that date. But if the date in A4 is not the current date the predicted count is pulled into the cell (B4).

So for ALL dates that are not the current date the count values are always predicted counts? Or is the count for "past dates" always an actual value?

Where are the actual and predicted counts located?

Maybe try again to characterize the "problem." Based on what you wrote (everyday I have to manually go to each sheet and copy the cell and paste as value to retain the cells value on that day) I really cannot tell how to help to address the issue. What cell is copied? From where? To where? How does doing that copying and pasting address the problem (need to retain the cells value on that day). Retain the value of which cells?

I could be way off but here is my guess about what is needed. A formula in B4 (using the example) has three "conditions." 1. date has passed and the lookup pulls in the actual count for that date. 2. If the date is the current date, the lookup pulls in the actual count. (If I'm correct, processing for dates past and the current date are the same?) 3. For future dates the formula looks up the predicted count.

Is there a chance that you could post a sample workbook with say two or three products and the data source (for actual or predicted counts)? Use Dropbox, 1Drive or? That makes helping a lot easier!

Jim

Hi Jim,

Sorry if my explanation was not specific enough. There was too much unnecessary information looking back at it.

Essentially, I needed a macro that would start at a specific worksheet and look for todays date in column A. Once it found the cell with todays date, it would copy the cell in the adjacent cell in column B (Which is a formula) and then Special paste the result of the formula in that same cell in column B. Then move on to the next worksheet.

1) Start at a specific sheet
2) Loop through each cell in column A till the last row with data
3) Check if the cell's value matches today's date
4) Copy the value from column B of the matching date cell
5) Paste the value back to the same cell to remove any formulas
6) Exit the loop once this was done and move on to the next worksheet and repeat the process.

I ended up using the following AI generated code after many iterations:

Sub CopyAndPasteSpecialValue()
    Dim ws As Worksheet
    Dim currentDate As Date
    Dim cell As Range
    Dim startSheet As Worksheet
    Dim nextSheet As Worksheet
    
    ' Set the current date to today's date
    currentDate = Date
    
    ' Set the start sheet to "SheetName"
    Set startSheet = ThisWorkbook.Sheets("SheetName")
    
    ' Loop through each worksheet starting from the specified sheet "SheetName"
    For Each ws In ThisWorkbook.Sheets
        ' Check if the worksheet is "SheetName" or the ones after it
        If ws.Index >= startSheet.Index Then
            ' Loop through each cell in column A till the last row with data
            For Each cell In ws.Range("A1:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
                ' Check if the cell's value matches today's date
                If cell.Value = currentDate Then
                    ' Copy the value from column B of the matching date cell
                    cell.Offset(, 1).Copy
                    ' Paste the value back to the same cell to remove any formulas
                    cell.Offset(, 1).PasteSpecial Paste:=xlPasteValues
                    ' Exit the loop after the first match
                    Exit For
                End If
            Next cell
            ' Clear the clipboard
            Application.CutCopyMode = False
        End If
    Next ws
End Sub

I don't know if this is the most efficient way to go about it but it seems to be working.

 

[OaklandJim]

That seems like pretty good code. One small possible modification would be to avoid copy and paste using this line of code

Cell.Offset(,1).Value = Cell.Offset(,1).Value

That said, you should just leave well enough alone.