PDA

View Full Version : [SOLVED] VBA code to paste data to a row with today's date in column A in another sheet



juddy
08-28-2017, 06:32 AM
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
08-28-2017, 06:52 AM
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
08-28-2017, 07:36 AM
Ok thanks very much. Let me give this a go.

juddy
08-28-2017, 07:44 AM
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
08-28-2017, 09:19 AM
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
08-28-2017, 09:40 AM
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
08-28-2017, 10:20 AM
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
08-28-2017, 11:18 AM
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
08-28-2017, 11:55 AM
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
08-28-2017, 01:16 PM
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
08-29-2017, 11:50 AM
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
08-29-2017, 12:17 PM
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
08-29-2017, 12:32 PM
Hi Juddy
Please use code tags or the # button when you post code