Insert Missing Column in Worksheets and Paste Data onto new Excell

  • I am trying to collate specific sheets with "Report" in their names across different excel files and paste them into a new excel document, with each new sheet's data pasted below the last used row of the collated sheet. I also need to check for any missing headers in the different excel files and add a header at the appropriate location if its missing. I have combined vba codes I have found online to first copy all the "Report" sheets of files in the directory to the consolidated excel file and then search for missing headers before copying the data to paste in Sheet1. However, I have some issues Firstly the missing columns are not added when I run the code. Next, I'm not too sure on how to write the via to do the copying and pasting in the first sheet's last used row as it is not working.


  • I can’t really post the workbooks due to sensitive information on them. Basically what I have is a directory with many different workbooks.

    1) I need to extract information on from workbooks with the word “Report” in them

    2) Of these “Report” workbooks, I only need information from certain sheets with titles from an array of names ( ie sheet names with “‘New”, “Continue” etc in them)

    3) I need to check the columns of these sheets and add in any missing columns

    4) What I’m doing currently is to copy these sheets onto the back of my compilation workbook

    5) Lastly, I need to toggle through each sheet and copy the data there and paste them onto the last used row of the first sheet of the compilation workbook