(Solved) – Excel VBA editing – identifying columns for merge

  • by

I inherited code to merge a single worksheet from multiple Excel workbooks into one sheet in a “master” sheet in a separate workbook. There was a blank column in the individual worksheets that is replicated in the master. I would like to update the merge so that this blank column now populates. In the individual worksheets I have updated the column with the data required (A vlookup formula that pulls data from other worksheets in the same workbook). and thats well and fine. But in the merged workbook its not populating the data. So into the macro I went to problem solve….

I don’t know if its too much Xmas cheer, but when I look through this code it looks like the whole workbook is merging, so this column (H) that has been there the whole time should be populating. But its not. so I must be reading the code wrong. Another thing too that makes me think I’m reading the code wrong…
– in the individual workbooks, the worksheet referenced, each cell is formulated to pull from other worksheets in the same workbook. But in the master worksheet that is merging all the workbooks, there are columns that now just have the data values from the merge, and there are columns that remain formulaic and are referencing within the master workbook, not the external workbooks. My mind goes to the macro being written to just pull the first set of columns.

So what am I missing- where is it referencing which columns to pull so I can add column H?

As additional info: Column 1 is being skipped. Columns B-G(2-7) are showing up as just data (no formulas) in the master worksheet and are merging fine. Column H(8) is the column that was blank, that I’ve now populated and want merged. Columns I-P are individual formulas in the master roll up, referencing content in the master worksheet, not the individual workbooks.

Here’s the code for the merge:

 Dim Sheet1 As Worksheet *repeated for each worksheet needed in workbooks being merged*

Dim rn As Range

Dim rowCounter As Long
Dim mergedRowCount As Long

mergedRowCount = 4

Set mergedSheet = Sheets("Data")

Set objExcel = GetObject(, "Excel.Application")

    If objExcel Is Nothing Then
        Set objExcel = CreateObject("Excel.Application")
    End If

    ' ***** CHANGE THIS and Repeat for as many wr*******'
    Set wkbk1 = objExcel.Workbooks.Open(Filename:="https://sharepoint.ehealthsask.ca/sites/SHA_HR/HRSLT/Shared Documents/APP_Workplans/WorkPlan_EDSS_Trent.xlsx?d=w66b6d330fc3b43d3bd32ecde39760940")
    Set Sheet1 = wkbk1.Sheets("Data")

    **This section below is replicated for each workbook as well**

    Set rn = Sheet1.UsedRange

    rowCounter = rn.Rows.Count
    For a_counter = 4 To rowCounter
    ' not copying the first column.
    If (Sheet1.Cells(a_counter, 3).Value <> "") Then
            mergedSheet.Cells(mergedRowCount, 2).Value = Sheet1.Cells(a_counter, 2).Value
            mergedSheet.Cells(mergedRowCount, 3).Value = Sheet1.Cells(a_counter, 3).Value
            mergedSheet.Cells(mergedRowCount, 4).Value = Sheet1.Cells(a_counter, 4).Value
            mergedSheet.Cells(mergedRowCount, 5).Value = Sheet1.Cells(a_counter, 5).Value
            mergedSheet.Cells(mergedRowCount, 6).Value = Sheet1.Cells(a_counter, 6).Value
            mergedSheet.Cells(mergedRowCount, 7).Value = Sheet1.Cells(a_counter, 7).Value
            mergedSheet.Cells(mergedRowCount, 8).Value = Sheet1.Cells(a_counter, 8).Value
            mergedRowCount = mergedRowCount   1
    End If
    Next a_counter

' close all the workbooks you opened
wkbk1.Close *Repeated for each workbook*

Leave a Reply

Your email address will not be published. Required fields are marked *