Macro for Copying Cels from All Workbooks to a Single Master Workbook

**JohnnyJ2013** · 05-22-2013, 09:09 PM

Hi!
I’m new to the forum and I’m trying to get a crash course to VBA. The simple version of my issue is I have a folder on my C drive “C/TEMP/” in which I want to copyl the data from any and all closed xlsx workbooks placed within that folder from four different cells: C1, F1, F34, and G34 from sheet1, and place in a Master.xlsm sheet2 in cells in separate columns.

What I am doing is trying to take the data from these four cells as time sheets and create one master payroll sheet to turn in twice a month. The columns would correspond to each of the four cells.

I’ve plowed through a good many posts everywhere and my problem is I simply don’t have enough experience with the coding language to determine where things are going wrong although I’ve come close (I think) to what I’m needing to do. A lot of what I’ve come across is oh so close, but not quite there if you know what I mean. Not for lack of trying first at least.

Any help would be greatly appreciated.

Thanks!

Johnny

**AlvaroSiza** · 05-22-2013, 10:34 PM

Unfortunately your data is non-contiguous, which makes it a good bit more difficult. Nonetheless, I wanted to be sure you had reviewed this - specifically the section on 'Workbooks'.

**JohnnyJ2013** · 05-22-2013, 10:46 PM

Good info for sure, I'll read through it and pick up what i can. Hopefully it will help. Funny how it seems simple on the outset.

Thank you

**AB33** · 05-23-2013, 04:13 AM

You wanted to copy C1, F1, F34, and G34 from sheet1 in to where? Where are the copied cells pasted in to? It would have been easier if you could attach a sample book? To attach a sample, go to advance then attachment.

**JohnnyJ2013** · 05-23-2013, 06:06 PM

I am needing to copy cells F1, C1, F34 and F35 from the "Time Sheet" tab on all the workbooks in the C:\Temp folder and pasted into Sheet1 on the Mater.xlsm as a list of content from those cells. I've attached the files as you suggested and have added a few highlights so perhaps a better explnation.

Thank you very much for your help.

Master.xlsm Test1.xlsx Test2.xlsx

**AB33** · 05-24-2013, 06:31 AM

Johnny,
Merge cells and VBA are not good friends. You have merged cells in time sheets. VBA will not copy merged cells, so I have to unmerge them.
I have tested it, as you can see, I used my own path: FolderPath = "C:\Marcotest"
You need to change this path to your folder ,i.e. C drive “C/TEMP/”. I do not want to put this in to the code as there may be typo, or space, so I left it for you to fill it in.

**JohnnyJ2013** · 05-24-2013, 11:06 PM

AB thanks a bunch, works perfect. Just what I was looking for and a great start to build upwards from. Did you code that from scratch or are you able to use the recorder? I gave the recorder a try but there was still too much code missing (and far too much excess) for all the details to work together.

Thanks so much!

Johnny

**AB33** · 05-25-2013, 05:51 AM

Johnny,
I am not sure if it is possible to code it using recorded macro. Even if it is possible, it will take lots of pages in coding and may take a while to run it. Recoded macros are great for learning purposes, but not efficient.
The code is not that complicated as it appears to be. It is a normal merging data code with opening folders added a bit complication. Most codes you find on -line search on merging from folders are very similar.