VBA code to paste data to a row with today's date in column A in another sheet

[juddy]

Hi,

I am looking for some code to complete the following task -

Sheet 1 has a daily pivot table with figures analysing data. These figures need to be recorded daily to sheet 2. I have recorded a macro to copy the pivot table data but I need to code so that it can be pasted to cells in a row specifically with today's date. Can anyone help?

I have added and example of the workbook I am trying to add the code too. The sheet "CASH" has the pivot table data to be copied and it needs to be pasted to the sheet "CASH FAILS MIS" in the row with today's date. Column A has the date for every day of the year.

Thank

Juddy

[SamT]

Set RowFound = Sheets("CASH FAILS MIS").Cells.Find(CDate(selectedDate)).Row


Range("B" & RowFound).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'Alternately
Cells(RowFouund, "B").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

[juddy]

Ok thanks very much. Let me give this a go.

[juddy]

Hi SamT...

How does your formula look for today's date without me specifying the date? Can I put today() as the selected date criteria?

[juddy]

This is my copy / paste code -

Range("E5:G5").Select
Selection.Copy
Sheets("CASH FAILS MIS").Select
Range("B5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("CASH").Select
Range("E6:G6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CASH FAILS MIS").Select
Range("E5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("CASH").Select
Range("E7:G7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CASH FAILS MIS").Select
Range("H5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B7").Select
Sheets("CASH").Select
Range("C11").Select
Application.CutCopyMode = False

How do I ensure the data is pasted to a row with today's date in Column A ??

[SamT]

How does your formula look for today's date without me specifying the date?

Set RowFound = Sheets("CASH FAILS MIS").Cells.Find(CDate(selectedDate)).Row' This uses the date you selected

Can I put today() as the selected date criteria?

Set RowFound = Sheets("CASH FAILS MIS").Cells.Find(Date).Row

Date is a VBA Function that returns today's date.

Unless you need all the blank rows where no no data was entered on that date, I would just use the first empty Row, and put today's date in column A

With Sheets("CASH FAILS MIS")
RowFound = .Cells.(Rows.Count, "B").End(xlUP).Row+1
.Cells(RowFound, "A")= Date
.Cells(RowFound, "B").PasteSpecial Paste:=xlPasteValues, Transpose:=False
End With

[mdmackillop]

Try to avoid selecting. It's not required and slows things down. Your code in Post #4 can be reduced to

    Set Cash = Sheets("CASH")
    With Sheets("CASH FAILS MIS")
        Cash.Range("E5:G5").Copy
        .Range("B5").PasteSpecial Paste:=xlPasteValues
        Cash.Range("E6:G6").Copy
        .Range("E5").PasteSpecial Paste:=xlPasteValues
        Cash.Range("E7:G7").Copy
        .Range("H5").PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
    End With

[SamT]

I would refactor the entire Routine thusly

Sub SamT_CashMIS()
Dim Col       'Variables not explicitly Typed are of Type Variant
Dim Fut_Clr 'Variants are required for Array operations
Dim STL
Dim NR As Long 'NR stand for Next Row

With Sheets("CASH")
  Col = .Range("E5:G5").Value 'Sets variable to an array of values
  Fut_Clr = .Range("E6:G6").Value
  STL = .Range("E7:G7").Value
End With

With Sheets("CASH FAILS MIS")
  NR = .Cells(Rows.Count, "B").End(xlUp).Row + 1
  .Cells(NR, "A").Value = Date
  .Cells(NR, "B").Resize(, 3) = Col 'Resize(Rows, Columns) makes the Range Rows tall and Columns Wide
  .Cells(NR, "E").Resize(, 3) = Fut_Clr 'Resize is required to Let Range = Values Array
  .Cells(NR, "H").Resize(, 3) = STL
End With
End Sub

[juddy]

Thanks SamT. We are almost there. If you take a look at the attached workbook, The 'CASH FAILS MIS' tab already has every date for the year in column A. The code needs to find today's date, select that row and past in the figures from the pivot table in the 'CASH' sheet. The code you have just added is good but adds a row with today's date to row 4 and then pastes in the data. I actually need to keep the data for every day as we move through the year because there are a set of graphs built from the data. Are you able to adjust the code so that it finds the row with the correct date and then populates the data rather than adding a row. At the end of the year I expect to have a sheet with data for every working day.

Much appreciated.

[SamT]

Read my post #6
Compare FoundRow to NR in my post #8.



There is an inherent conflict in the quotes from your posts below

At the end of the year I expect to have a sheet with data for every working day.

How does your formula look for today's date without me specifying the date?

Set RowFound = Sheets("CASH FAILS MIS").Cells.Find(CDate(selectedDate)).Row' This uses the date you selected

Can I put today() as the selected date criteria?

Set RowFound = Sheets("CASH FAILS MIS").Cells.Find(Date).Row

Date is a VBA Function that returns today's date.

"(CDate(selectedDate))" is not guaranteed to always be the same date as Today's date.

[juddy]

Had a go in line with your last message -

Sub cashMISforum()
'
    Dim Col 'Variables not explicitly Typed are of Type Variant
    Dim Fut_Clr 'Variants are required for Array operations
    Dim STL
         
    With Sheets("CASH")
        Col = .Range("E5:G5").Value 'Sets variable to an array of values
        Fut_Clr = .Range("E6:G6").Value
        STL = .Range("E7:G7").Value
    End With
     
    With Sheets("CASH FAILS MIS")
            Set RowFound = Sheets("CASH FAILS MIS").Cells.Find(CDate(selecteddate)).Row
            selecteddate = Sheets("CASH").Range("J1")
        .Cells(NR, "A").Value = Date
        .Cells(NR, "B").Resize(, 3) = Col 'Resize(Rows, Columns) makes the Range Rows tall and Columns Wide
        .Cells(NR, "E").Resize(, 3) = Fut_Clr 'Resize is required to Let Range = Values Array
        .Cells(NR, "H").Resize(, 3) = STL
    End With
End Sub

but get a run time error '91
object variable or with block variable not set

Apologies if I am new to VBA. I am not familiar with this error.

Thanks

Juddy

[SamT]

That error is probably because, the value of Variable selecteddate is set after It is looked for in the Set RowFound line

Other logical errors:
The Variable NR is not set to any value
The variable RowFound is not used in that Procedure

RowFound was designed to indicate the Row number of the found date (selecteddate)

NR was designed to hold the Row number of the first empty cell in column B.


FYI, please tell us the line of code that causes any errors you get.

[mdmackillop]

Hi Juddy
Please use code tags or the # button when you post code