0

I need some help with copying data from one excel which is a daily report to a monthly report. Is there a possibility if you insert a date in one cell to recognize the date and paste the data to the needed range for the proper date?

So far, I have made the macro to copy the data for one date (let's say the 1st of a month), and I can't think of a solution that doesn't involve 31 button macros referring to each day of the month. 

Sub Insert_Daily_Report()

Dim OpenBook As Workbook
Dim Report As Workbook
Dim FileToOpen As Variant

Set Report = ThisWorkbook
FileToOpen = Application.GetOpenFilename(Title:="Insert the daily report", FileFilter:="Excel Files 
(*.xlsm*), *xlsm*")

If FileToOpen <> False Then

Set OpenBook = Application.Workbooks.Open(FileToOpen)
OpenBook.Sheets("Daily_Sheet1").Range("E4:AB4").Copy
Set Report = ActiveWorkbook
ThisWorkbook.Worksheets("Monthly_Sheet1").Activate
ActiveSheet.Range("E4").Select
ActiveSheet.PasteSpecial Link:=True

Set OpenBook = ActiveWorkbook
OpenBook.Sheets("Daily_Sheet1").Range("E10:AB10").Copy
Set Report = ActiveWorkbook
ThisWorkbook.Worksheets("Monthly_Sheet1").Activate
ActiveSheet.Range("E39").Select
ActiveSheet.PasteSpecial Link:=True

OpenBook.Close False
End If

End Sub

Also, I think that the marco is not switching between the two workbooks well - the pasted data is not the right data (I have checked the ranges, they are ok)

braX
  • 11,506
  • 5
  • 20
  • 33
luthien93
  • 1
  • 1
    As a rule, if you can avoid using the phrases `Active*` and `Select` in your code that would be a great start. Check out this link for more detail: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Malan Kriel Jun 17 '20 at 06:54
  • @malan-kriel I tried as you suggested and changed it with the following: [Set Range_OpenBook=OpenBook.Worksheets("Daily_Sheet1").Range("E4:AB4") Set Range_Report=Report.WorkSheets("Monthly_Sheet1").Range("E4:AB4") Range_OpenBook.Copy Range_Report] It gets the wrong data again. – luthien93 Jun 17 '20 at 09:25
  • You'll need to provide additional information / minimum working example because we have no idea of the format of any of your data, etc. – Malan Kriel Jun 17 '20 at 11:05
  • @malan-kriel it worked, the error was the missing $ sign on the cells from the daily report. Thank you for the help :) – luthien93 Jun 18 '20 at 06:25

0 Answers0