Combine headers from two sheets onto a third

[JimSnyder]

This is using Excel 2013 and VBA. The goal of the project is to take data from sheet A and find matches on either sheet B or sheet C and build sheet D or sheet E based on matches between A+B or A+C. The use of match and looping are taking care of the data, but I need to combine the headers.

Essentially, I find a match on sheet B from sheet A and build sheet D with the headers of sheet A and the headers of sheet B. I am attempting to use application.union to combine the two ranges of headers onto sheet D. I am getting an error with the union (1003).

Here is the sample code:

    Set wkbk = Workbooks.Open(filePath)
    wkbk.Activate
    With wkbk
        Set sourceSheet = .Sheets(srcSheet)
        Set targSheet1 = .Sheets(trgSheet1)
        Set targSheet2 = .Sheets(trgSheet2)
        
        ' Get size of rows to be copied to set destination range
        Set sourceAllRange = sourceSheet.Range(allBegin & 1, allEnd & 1)
        Set sourceFAR130Range = targSheet1.Range(far130Begin & 1, far130End & 1)
        Set sourceFAR130ORange = targSheet2.Range(far130OBegin & 1, far130OEnd & 1)
        
        ' Get number of columns of each sheet
        colAllCount = sourceAllRange.Columns.Count
        col130Count = sourceFAR130Range.Columns.Count
        col130OCount = sourceFAR130ORange.Columns.Count
        
        ' Add a sheet for matched FAR130 values
        .Worksheets.Add After:=.Worksheets(Worksheets.Count)
        Set tabFAR130 = .Worksheets(Worksheets.Count)
        tabFAR130.Name = moveSheet1
        
        ' Copy All and FAR130 headers to new sheet
        With tabFAR130
            Set dest130Range = .Cells(1, colAllCount + col130Count)
[COLOR=#ff0000]            Set dest130Range = Application.Union(sourceAllRange, sourceFAR130Range)[/COLOR]
        End With

I can add the variable definitions if needed. Note that dest130Range, sourceAllRange, and sourceFAR130Range are all declared as ranges.

 

[JoeMo]

I don't think you can use Application.Union to form a union among ranges on different sheets.

 

[JimSnyder]

Thanks, JoMoe. I finally used brute force:

        ' Copy All and FAR130 headers to new sheet
        With tabFAR130
            sourceSheet.Range(allBegin & 1, allEnd & 1).Copy Destination:=.Range(allBegin & 1, allEnd & 1)
            targSheet1.Range(far130Begin & 1, far130End & 1).Copy Destination:=.Range(pasteBegin & 1)
        End With