PDA

View Full Version : [SOLVED] Merging workbooks



m14020256
11-22-2016, 08:39 AM
I've been struggling with this for quite a while and would really appreciate if someone could help:



Context: I have a merger that consolidates into one sheet all files in the "sheets to merge" folder
Problem: Instead of printing the name of the source workbook in column A, I would like to copy the "region" specified in each source workbook


The files are attached. Any guidance would be appreciated!


17665

Kenneth Hobs
11-22-2016, 01:33 PM
I don't know what Region means. I am guessing that you want all below the title row?

From the Immediate Window in the VBE, you can see what currentregion and usedrange return from your new file with just the header row in D4:F4.

?worksheets(1).Range("D5").currentregion.address
$D$4:$F$5
?worksheets(1).usedrange.address
$C$4:$F$4

Of course we can get a better picture with an example file or two with sample obfuscated data.

m14020256
11-22-2016, 01:43 PM
Thanks for the reply, Kenneth.

For each worksheet, the region is specified in cell C1. What I am looking to do on the merged sheet is to replace the filenames in column A with the region. For example, after you click merge, cells A5:A8 should say "East", which is the region in worksheet 1.

Kenneth Hobs
11-22-2016, 02:21 PM
I see, I missed that folder in the zip file. I should be something like:



'Copy the file name in column A.
With sourceRange
'BaseWks.Cells(rnum, "A"). _
Resize(.Rows.Count).Value = MyFiles(FNum)
BaseWks.Cells(rnum, "A"). _
Resize(.Rows.Count).Value = BaseWks.Range("C1").Value

m14020256
11-22-2016, 10:42 PM
Thanks for the reply, Kenneth. I tried using the code you provided but it just yields blanks instead. As a beginner at macros, I have no idea why that is - could you take a look? Thanks in advance!

Attached is the latest files for your reference.

17671

Kenneth Hobs
11-23-2016, 06:03 AM
Sorry, I normally test my solutions.

BaseWks.Cells(rnum, "A").Resize(.Rows.Count).Value = _
mybook.Worksheets(1).Range("C1").Value

m14020256
11-27-2016, 04:24 AM
This works - thank you for your help, Kenneth! Really appreciate it.

m14020256
11-28-2016, 07:30 AM
One more question, Kenneth, if you could lend your brain:

Let's say that I have consolidated files A, B and C by clicking merge. Now, I would like consolidate File D below the data that has already been copied from files A, B and C. Is there a way to do that?

Essentially, the question boils down to whether there is a way to merge the files not to a specified row number but for the macro to look for the non-blank row that does not already contain consolidated data.

Kenneth Hobs
11-28-2016, 07:52 AM
Most like to mandate that column A always have a value. If that is the case then:

'....
' Add a new workbook with one sheet.
Set BaseWks = ActiveWorkbook.ActiveSheet
'rnum = 5
With BaseWks
rnum = .Cells(Rows.Count, "A").End(xlUp).Offset(1).Row
If rnum < 5 Then rnum = 5
End With
'...
Otherwise, a find routine is needed to find the next "blank/empty" row.