Hi everyone,
so I am trying to extract all the information from the raw data file (available for download into excel format daily, ~10 files per day) and paste each one of the sheets to my master tracker.
I have attached my raw file here, with 2 worksheets in 1 workbook, trying to transfer to a master tracker. In the master tracker you can see how I want them to be shown.
My current coding is just simply taking the exact cells from the raw file, and paste to the master tracker exact cells.
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
Set wsCopy = Workbooks("Raw file.xlsx").Worksheets("Sheet1")
Set wsDest = Workbooks("Master tracker.xlsm").Worksheets("Report")
wsCopy.Range("A9:E9").Copy _
wsDest.Range("B2")
wsCopy.Range("A12").Copy _
wsDest.Range("G2")
wsCopy.Range("A15:E15").Copy _
wsDest.Range("H2")
wsCopy.Range("A18").Copy _
wsDest.Range("L2")
wsCopy.Range("A21:E21").Copy _
wsDest.Range("M2")
wsCopy.Range("A26").Copy _
wsDest.Range("R2")
wsCopy.Range("A29").Copy _
wsDest.Range("S2")
Questions:
- How can I keep adding the data from another new raw file (i.e. Sheet2, and sheet 3 4 5 6 7 in the future..) to the master tracker, without erasing the previous records? (like adding new record below the existing ones)
- My raw file has to be downloaded one by one each time. Is it possible to get the data from each of them as a workbook, rather than combining the different worksheets into one workbook? Which one is easier?
Thanks for reading! You help would be much appreciated!