Merging workbooks

[m14020256]

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!


Test excel.zip

[Kenneth Hobs]

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]

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]

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]

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.

Test excel.zip

[Kenneth Hobs]

Sorry, I normally test my solutions.

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

[m14020256]

This works - thank you for your help, Kenneth! Really appreciate it.

[m14020256]

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]

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.