Hi there,
been scratching my head on this one:
Sheet 1 = Data Entry (1 date, several integers)
Sheet 2 = Uses and places the entered data of sheet 1 in the correct cells + some calculations (all same row, different date = different row)
Problem: can’t get Macro button to execute CopyPasteValues on newly appeared data row on sheet 2 (originating from sheet 1)
what I have:
Sheet1 data entry macro code
————————–
Range(“F4”).Select
ActiveCell.Value = InputBox(“Enter Date: MM/DD/YYYY”)
Range(“D9”).Select
ActiveCell.Value = InputBox(“Enter Amount”)
————————–
etc… easy part
Sheet2 cells formula:
>>>>>>>>>>>>
=IF(‘Sheet1′!$F$4=’Sheet2′!F162,’Sheet1’!$D$9,””)
>>>>>>>>>>>>
Macro button code trial:
————————–
Range(“F4”).Select
ActiveCell.Formula = “=VLOOKUP(Sheet1!$F$4,Sheet2!$F$8:$F$5002,6,)”
Sheets(“Sheet2”).Select
ActiveCell.Rows(“1:1”).EntireRow.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
what I’d like the button to do:
-Find the date entered on Sheet1 (F4) within the column of dates (sheet2 range F8-F5002)
-If finding the date : Select the entire row of values corresponding to the date. (Example Sheet2!F162) and CopyPasteSpecial the entire row values for that row only.
-If not finding the date: report error message ‘Date not Found’
-Erase all entered data on Sheet1 so the next date+values can be entered and the process repeated.
Many thanks in advance!
ST
