Insert additonal rows into multiple files in a folder based on Master Template(Old file)

Hi all,

Basically, I'm looking to standardize the rate cards I have. So the code JBeaucaire supplied helped me out for the task at the time, however, we have now updated the Rate Card and as a result I have been tasked with updating all our current ones (typical). Can anybody help?

The Task

FileA.xlsx is the original file that contains the old "Cover Page" which has now been altered, to include extra rows, which can be seen in FileB.xlsx.

Is there a way to run a macro that will insert these extra rows with titles into all of the FileA.xlsx files (old ones) based on FileB.xlsx format without losing any data?

Yes the additional "new" rows will still need to be populated with the relevant data but that I'm afraid that will need to be done manually (me) as each Rate Card is verified and peer reviewed (them).
FileB.xlsxFileA.xlsx

Now the cheeky bit....

Would it be possible to build into the code the ability for the user (me) to click "run" so that a folder selection box pops up to select the folder rather than me having to cut and paste the folder destination into the code all the time?

The above is a big ask for me but hoping it's not too big for the gurus out there...

Thanking you in advance.

Kind regards,

Mad-dog

Hello Mad-Dog
(I wish I had spent more time thinking of a great user id like yours rather than just taking my normal email address and stripping of the ISP details - just reading it made me smile and started the imagination running off into johnny Depp and the Pirate series)

On to more serious matters.

When you say folder (in the cheeky bit) are you saying sub directory path or are all your files in one directory and you are actually saying "workbooks" and looking for something like the usual excel "file open" dialog to pop up?

The actual code required to do the line inserts is pretty easy and looping through all the workbooks in a directory isn't that hard from what I see in the forum but to be brutally honest it isn't something I have actually done myself (yet).

I will have a play with this today (it will be 40c here today so being inside with the A/C is a good option but I suspect one of the gurus will beat me by a long way

Jmac1947

Hi Jmac1947,

40C is pretty warm....best get some beers in the fridge for later on :-)

Yes my folder is a sub-directory "C:\Users\SAP\Rate Cards\Verified\". Yes the files are all workbooks with each one containing 3 worksheets. The worksheet that needs the rows added to is the "Cover Page" or "Sheet1".

So you are correct in saying that an added bonus would be a usual excel "file open" dialog to pop up as it would simplify the task rather than having to go into the code a manipulate it as the folder may change in the future.

Looking forward to seeing your first attempt.

Mad-dog