Daily macro to copy and paste based on column header

Question

I'm very new to writing Macros in Excel, and have done a bit of looking around to try and solve my problem, but I haven't found a solution yet that works.

I'm trying to write a Macro to do the following:

Copy data from Source A (sheet Daily_Inventory(m3), workbook Daily_Inventory_YYYYMMDD) based on column headings (so for example, I want to copy all the data under the column name "All activities").The number of rows of data in this row may increase/decrease.

And then I want to paste this data into Destination B (sheet Daily_Inventory(m3), workbook Daily_Inventory_Master) under the corresponding column name "MMDDYYYY".

The workbook Daily_Inventory_YYYYMMDD will be stored in my specify folder everyday in 365 days, then I have to copy information from it to another workbook with every single related column header daily. If column header is not exist, add new value (same datetime as source file) then paste.

Please help me in this case, I'd be extremely grateful for any help. Many thanks!!!

If the code goes into a module in workbook Daily_Inventory_Master.xlsm, then you have to decide how or why will the code run for exactly Daily_Inventory_YYYYMMDD.xlsm. On the other hand, if the code goes into a module in Daily_Inventory_YYYYMMDD.xlsm, then it is straightforward and you could use Daily_Inventory_Master.xlsx for the results (Note .xlsx). Think about these and do clarify. — VBasic2008
Thank you for advice, I had clarify then did it at another file. — denova

Amiga500 Amiga500 · Accepted Answer · 2021-03-19T10:12:34

This is all done in my head and not tested, but it shouldn't be far away:

bookName1 = "Daily_Inventory_YYYYMMDD.xlsx"
sheetName1 = "Daily_Inventory(m3)"
headerRow1 = 8

bookName2 = "Daily_Inventory_Master.xlsm"  'note, xlsm as the macro would need to reside somewhere, better in the book you are using every day
sheetName2 = "Daily_Inventory(m3)"
headerRow2 = 12

DestHeader = mid(bookName1,20,2) & mid(bookName1,22,2) & mid(bookName1,16,4) 
'Parsing the input date, play around with the numbers or use len(bookName1)-X, Y Z to work backwards if the first part of the name changes

'Oops, forgot to open the workbooks
Workbooks.Open "C:\folderName1\" & bookName1
Workbooks.Open "C:\folderName2\" & bookName2


'1. Find the source column
iCol = 1
DoUntil Workbooks(bookName1).Sheets(sheetName1).cells(headerRow1, iCol) = "All activities")
    iCol = iCol + 1
Loop

'2. Find the last row for data within that column 
lastRow = Workbooks(bookName1).Sheets(sheetName1).cells(1048576,iCol).End(xlUp).Row

'3. Copy the data   
Range(Workbooks(bookName1).Sheets(sheetName1).cells(headerRow1+2,iCol), _
    Sheets(Workbooks(bookName1).sheetName1).cells(lastRow,iCol)).Copy

'4. Use variation of step 1 to find the destination column
iiCol = 1
DoUntil Workbooks(bookName2).Sheets(sheetname2).cells(headerRow2,iiCol) = DestHeader
    iiCol = iiCol + 1
Loop

'5. Paste data in 
Workbooks(bookName2).Sheets(sheetname2).cells(headerRow2+2,iiCol)._
    PasteSpecial Paste:=xlPasteValues

'Close the books
Application.DisplayAlerts = False  'Disable the popups asking for confirm for saving
Workbooks(bookName1).close saveChanges:=False
Workbooks(bookName2).close saveChanges:=True
Application.DisplayAlerts = True

Further to the above, if this was going to run daily, you could automatically get todays date and use it using

currDay = Format(Date, "dd")
currMonth = Format(Date, "mm")
currYear = Format(Date, "yyyy")

so bookName1 & DestHeader would be:

bookName1 = "Daily_Inventory_" & currYear & currMonth & currDay & ".xlsx"

DestHeader = currMonth&currDay&CurrYear

Daily macro to copy and paste based on column header

1 Answers