Extracting data from one sheet to another on a daily basis

[AndyGav86]

I'm looking to try and extract data from one sheet to another, but the first sheet is used daily so the data is overwritten every day. I need it to go into another sheet for each day of the month.

So I need the day total of LAG, ETD & SCANNER to be put into the corresponding table on another sheet for each day if that makes sense?

Many Thanks in advance

 

[MCLIFTO8]

So the format of both sheets remains the same but the data contained changes?
Can i take it that every day you click a button and the macro would pull data from cells D31, D35 and D40?
And then places that data on the row corresponding with the day of the month?

What happens when it's no longer the month you started on? Do you wipe the second sheet manually, or copy the sheet to a different name and start again?

 

[AndyGav86]

So the format of both sheets remains the same but the data contained changes?
Can i take it that every day you click a button and the macro would pull data from cells D31, D35 and D40?
And then places that data on the row corresponding with the day of the month?

What happens when it's no longer the month you started on? Do you wipe the second sheet manually, or copy the sheet to a different name and start again?

yes, so format is the same I believe, and yes, a button with a macro would be perfect but i have realised a new problem i actually need the percentage of the day for each one so it would be what percentage is D31 from B29 & D35 from B29 & D40 from B29.
Yes, then it would go into the corresponding day of the month in the other sheet
so, at the end of the month the worksheet is saved then I would start a new worksheet for the following month

I hope that makes sense I'm really new at this, so ask me more if you need more info

And thank you for replying to this

 

[MCLIFTO8]

i actually need the percentage of the day for each one so it would be what percentage is
D31 from B29 &
D35 from B29 &
D40 from B29.

at the end of the month the worksheet is saved then I would start a new worksheet for the following month

So daily you click a button.
With the 'Lag's - ETD's - Scanner' sheet row X:
Column B = D31/B29
Column C = D35/B29
Column D = D40/B29

Can i take it this destination sheet has the cells formatted as percentages?

Macro
Alt+F11 to open VBA developer.
Insert>Module
Paste in the following.

Sub collect_lag_etd_scanner_ratios()

Dim strCopySheetName As String, strPasteSheetName As String
Dim lngTodaysRow As Long
Dim tempCalc As Variant

strCopySheetName = "Sheet1"
strPasteSheetName = "Sheet2"

lngTodaysRow = Day(Date) + 11   'the row your date list starts at -1.

    With ThisWorkbook
        Debug.Print lngTodaysRow
        
        tempCalc = Sheets(strCopySheetName).Range("D31").Value2 / Sheets(strCopySheetName).Range("B29").Value2
        Sheets(strPasteSheetName).Range("B" & lngTodaysRow).Value2 = tempCalc
    
        tempCalc = Sheets(strCopySheetName).Range("D35").Value2 / Sheets(strCopySheetName).Range("B29").Value2
        Sheets(strPasteSheetName).Range("C" & lngTodaysRow).Value2 = tempCalc
    
        tempCalc = Sheets(strCopySheetName).Range("D40").Value2 / Sheets(strCopySheetName).Range("B29").Value2
        Sheets(strPasteSheetName).Range("D" & lngTodaysRow).Value2 = tempCalc
    
    End With

End Sub

Create your shape and assign macro
Insert>Shape
paste your shape
left click to give it a title
right click > assign macro
select the macro we made.
left click the shape again to run the macro.

 

[AndyGav86]

So daily you click a button.
With the 'Lag's - ETD's - Scanner' sheet row X:
Column B = D31/B29
Column C = D35/B29
Column D = D40/B29

Can i take it this destination sheet has the cells formatted as percentages?

Macro
Alt+F11 to open VBA developer.
Insert>Module
Paste in the following.

Sub collect_lag_etd_scanner_ratios()

Dim strCopySheetName As String, strPasteSheetName As String
Dim lngTodaysRow As Long
Dim tempCalc As Variant

strCopySheetName = "Sheet1"
strPasteSheetName = "Sheet2"

lngTodaysRow = Day(Date) + 11   'the row your date list starts at -1.

    With ThisWorkbook
        Debug.Print lngTodaysRow
       
        tempCalc = Sheets(strCopySheetName).Range("D31").Value2 / Sheets(strCopySheetName).Range("B29").Value2
        Sheets(strPasteSheetName).Range("B" & lngTodaysRow).Value2 = tempCalc
   
        tempCalc = Sheets(strCopySheetName).Range("D35").Value2 / Sheets(strCopySheetName).Range("B29").Value2
        Sheets(strPasteSheetName).Range("C" & lngTodaysRow).Value2 = tempCalc
   
        tempCalc = Sheets(strCopySheetName).Range("D40").Value2 / Sheets(strCopySheetName).Range("B29").Value2
        Sheets(strPasteSheetName).Range("D" & lngTodaysRow).Value2 = tempCalc
   
    End With

End Sub

Create your shape and assign macro
Insert>Shape
paste your shape
left click to give it a title
right click > assign macro
select the macro we made.
left click the shape again to run the macro.

So, I didn't understand a couple of things,
Yes, they are formatted to percentage
The Daily button would be on the sheet named "SDR" and the destination sheet is named "KPI Hidden"
I didn't know what you meant by 'the row your date list starts at -1.
as you can see by the screen shot i think it worked but seemed to go into the day of the 14th?
I'm not sure what I'm doing wrong

 

[MCLIFTO8]

I didn't know what you meant by 'the row your date list starts at -1.

The 'Date' function returns something like 'dd-mm-yyyy' and 'Day()' extracts the day of the month from that.
So Day(Date) should return 28 as today is the 28th (In Australia, at least).
But your list of dates start at row 12.....
So to get your correct row we need to add 11 + 28 to get your correct row.
Which is "'the row your date list starts at -1."

as you can see by the screen shot i think it worked but seemed to go into the day of the 14th?
I'm not sure what I'm doing wrong

Yeah neither do I. Let's troubleshoot it.

Add these two lines of code under 'lngTodaysRow = Day(Date) + 11'

Debug.Print "Today's date is " & Date
Debug.Print "Today's row is " & lngTodaysRow

If your immediate window is not showing in the editor, open the editor up and press CTRL+G.
You should see this after running the code once (F5):

Let us know what your immediate window said.

 

[AndyGav86]

I think you've done it !!!!

I'll continue to try it every day till the end of the month then again on the first and let you know.

Thank you so much for your help this has taking me a long time to sort out and I can't thank you enough

 

[MCLIFTO8]

I think you've done it !!!!

I'll continue to try it every day till the end of the month then again on the first and let you know.

Thank you so much for your help this has taking me a long time to sort out and I can't thank you enough

Great!
For the people reading this thread coming from a search engine, would you mind marking the post as a solution so it pops up at the top of the thread?