Thread: how to copy data from one worksheet to another by using macro

Hi!
Please help create a macro that would update my data on a daily basis in sheet 2. If there’s an input data in “qty.” in sheet1, it should be copied accordingly in sheet2 whereas the date must be updated also.

If there's anything more i should do please let know. thank you...

Please refer to the worksheet attached.

regards,
arem28daily record.xlsx

Would Sheet1 be cleared each day to put in the new values?

Use this code -

Option Explicit
Dim lcol As Long
Dim lrow As Long

Sub update_daily()

With Worksheets("Sheet2")
    lcol = .Range("XFD2").End(xlToLeft).Column
    .Cells(2, lcol + 1).Value = Date
    lrow = .Range("A" & Rows.Count).End(xlUp).Row
    .Range(.Cells(3, lcol + 1), .Cells(lrow, lcol + 1)).FormulaR1C1 = "=VLOOKUP(RC1,Sheet1!C1:C3,3,0)"
    .Range(.Cells(3, lcol + 1), .Cells(lrow, lcol + 1)).Copy
    .Cells(3, lcol + 1).PasteSpecial , Paste:=xlValues
End With

With Worksheets("Sheet1")
    lrow = .Range("C" & Rows.Count).End(xlUp).Row
    .Range("C3:C" & lrow).ClearContents
End With

End Sub

I dont think vlookup will have a problem while working in the same workbook. Why do you say its not working?

Oh ok....got you this time .

Well, i have been using vlookup a lot and have used it within the same workbook. I will provide you an alternate solution using 2 workbooks as you suggested and see if it works perfectly as you need it.

I just realised one more aspect regarding your test file above (while i was creating an alternate code). I found that product 1 to product 10 in sheet 1 were written with one extra space at the end (some of them), while product 1 to product 10 in sheet 2 didnt have the space. For e.g.Product 2 on sheet1 doesnt have the space at the end and sheet2 has it. So this will result in the vlookup not working. You need to remove these spaces.

You can use this revised code -

Option Explicit

Dim lcol As Long
Dim lrow As Long

Sub update_daily()

With Worksheets("Sheet2")
    lcol = .Range("XFD2").End(xlToLeft).Column
    .Cells(2, lcol + 1).Value = Date
    lrow = .Range("A" & Rows.Count).End(xlUp).Row
    .Range(.Cells(3, lcol + 1), .Cells(lrow, lcol + 1)).FormulaR1C1 = "=VLOOKUP(RC1,'[daily record_2.xlsx]Sheet1'!C1:C3,3,0)"
    .Range(.Cells(3, lcol + 1), .Cells(lrow, lcol + 1)).Copy
    .Cells(3, lcol + 1).PasteSpecial , Paste:=xlValues
End With

With Workbooks("daily record_2.xlsx").Worksheets("Sheet1")
    lrow = .Range("C" & Rows.Count).End(xlUp).Row
    .Range("C3:C" & lrow).ClearContents
End With

End Sub