I have an ms-access button that I use to grab a specific excel file and behind that button is a vba script. I treat the file like an object. This excel file has 9 sheets. Only 2,3,4,5 I am concerned with. I don't want to alter the original 2nd sheet, what I'd like to do is create a copy of the 2nd sheet and place that at the end of the excel sheet (temporarily). This is so I can access that merged sheet and run a set of data validations against it. Once those validations are complete I can remove it, so it's as if the file was never altered.
I want to perform a left inner join or sth like it so that sheet 2 is joined with all columns from 3,4,5, based on the shared join key "Unique Transaction ID", even if there is not a match with the columns they should still be added. And if a match is found with the key, then that rows data is added to the correct line and corresponding column.
The sheets are set up like this:
Sheet 2:
| A | |
|---|---|
| Unique Transaction ID | some_header |
| 321 | d1 |
| 123 | d2 |
| 333 | d3 |
| 231 | d4 |
| 908 | d5 |
| 111 | d6 |
| 367 | d7 |
Sheet 3:
| A | |
|---|---|
| Unique Transaction ID | another_header |
| 333 | q |
| 231 | w |
| 908 | e |
Sheet 4:
| A | |
|---|---|
| Unique Transaction ID | x_header |
| 321 | h |
| 123 | t |
Sheet 5:
| A | |
|---|---|
| Unique Transaction ID | z_header |
| 321 | |
| 123 | |
| 333 | |
| 231 | |
| 908 | |
| 111 | b_2 |
| 367 | a_1 |
The ranges for each sheet is:
Sheet 2: A to AU
Sheet 3: A to I
Sheet 4: A to H
Sheet 5: A to D`
Since I only need the A column "Unique Transaction ID" from Sheet 2, the other ranges would start at "B" I think.
And the merged sheet should therefore be the range: A to BM
When horizontally merged the ranges for the sheets would be:
Sheet 2: A to AU
Sheet 3: AV to BC
Sheet 4: BD to BJ
Sheet 5: BK to BM
The result of the merge should produce a sheet that looks like this:
A
Unique Transaction ID | some_header | another_header | x_header | z_header |
321 | d1 | | h | |
123 | d2 | | t | |
333 | d3 | q | | |
231 | d4 | w | | |
908 | d5 | e | | |
111 | d6 | | | b_2 |
367 | d7 | | | a_1 |
And of course there will be many columns in between those columns.
Is there an error in my reasoning? A more straight-forward approach?
Any help appreicated Thank you
Here is what Ive tried, none seems to fully do the merge correctly. I can get to the point where I have a copy of sheet 2 and all its columns/data but nothing from sheet 3,4,5.
Example:
`Public Sub Init(filePath As Variant)
' Connect to original file
Dim xlApp As Excel.Application, objFile As Excel.Workbook
Set xlApp = CreateObject("Excel.Application")
Set objFile = xlApp.Workbooks.Open(filePath)
' Get the number of sheets
numSheets = objFile.Worksheets.Count
If numSheets = 9 Then
' Define variables for Sheet 2 and the other sheets
Dim sht2 As Worksheet, sht3 As Worksheet, sht4 As Worksheet, sht5 As Worksheet
Dim lastCol As Long, copyRange As Range, pasteRange As Range
' Set variables to the correct worksheets
Set sht2 = objFile.Worksheets("Sheet2")
Set sht3 = objFile.Worksheets("Sheet3")
Set sht4 = objFile.Worksheets("Sheet4")
Set sht5 = objFile.Worksheets("Sheet5")
' Get the last column in Sheet 2
lastCol = sht2.Cells(1, sht2.Columns.Count).End(xlToLeft).Column
' Loop through each column in Sheet 3, 4, and 5 and paste the values into Sheet 2
For i = 1 To lastCol
' Set the copy and paste ranges for each column
Set copyRange = Union(sht3.Cells(1, i), sht4.Cells(1, i), sht5.Cells(1, i)).EntireColumn
Set pasteRange = sht2.Cells(1, sht2.Columns.Count).End(xlToLeft).Offset(0, 1)
' Copy and paste the values
pasteRange.Resize(copyRange.Rows.Count, copyRange.Columns.Count).Value = copyRange.Value
Next i
End If
' Close the original file and clean up
objFile.Close SaveChanges:=False
xlApp.Quit
Set objFile = Nothing
Set xlApp = Nothing
End Sub`
I tried this, but cant use the Union feature in vba.
This one adds everything from sheet 2, but columns/data from sheet 3,4,5 are not there:
' Initialize object and set default values
Public Sub Init(filePath As Variant)
' Connect to original file
Dim xlApp As Excel.Application, objFile As Excel.Workbook
Set xlApp = CreateObject("Excel.Application")
Set objFile = xlApp.Workbooks.Open(filePath)
' Get the number of sheets
numSheets = objFile.Worksheets.Count
If numSheets = 9 Then
' Get references to the relevant sheets
Dim sheet2 As Excel.Worksheet, sheet3 As Excel.Worksheet, sheet4 As Excel.Worksheet, sheet5 As Excel.Worksheet
Set sheet2 = objFile.Worksheets("sheet2")
Set sheet3 = objFile.Worksheets("sheet3")
Set sheet4 = objFile.Worksheets("sheet4")
Set sheet5 = objFile.Worksheets("sheet5")
' Make a copy of sheet 2 and set it as the active sheet
sheet2.Copy After:=objFile.Worksheets(numSheets)
Dim mergedSheet As Excel.Worksheet
Set mergedSheet = objFile.Worksheets(numSheets + 1)
mergedSheet.Activate
' Add a header row to the merged sheet
Dim headerRange As Excel.Range
Set headerRange = Range("A1:AU1")
headerRange.Copy
Range("AV1").PasteSpecial xlPasteValues
Range("BD1").PasteSpecial xlPasteValues
Range("BK1").PasteSpecial xlPasteValues
' Loop through the unique transaction IDs in sheet 2 and find matching rows in sheets 3, 4, and 5
Dim lastRow As Long, i As Long
lastRow = sheet2.Cells(sheet2.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastRow ' Start at row 2 to skip the header row
' Get the unique transaction ID from sheet 2
Dim uniqueID As String
uniqueID = sheet2.Cells(i, "A").Value
' Find matching row in sheet 3 and copy values to merged sheet
Dim matchRange As Excel.Range
Set matchRange = sheet3.Columns("A").Find(uniqueID)
If Not matchRange Is Nothing Then
matchRange.Resize(1, 8).Copy
mergedSheet.Cells(i, "AV").PasteSpecial xlPasteValues
End If
' Find matching row in sheet 4 and copy values to merged sheet
Set matchRange = sheet4.Columns("A").Find(uniqueID)
If Not matchRange Is Nothing Then
matchRange.Resize(1, 7).Copy
mergedSheet.Cells(i, "BD").PasteSpecial xlPasteValues
End If
' Find matching row in sheet 5 and copy values to merged sheet
Set matchRange = sheet5.Columns("A").Find(uniqueID)
If Not matchRange Is Nothing Then
matchRange.Resize(1, 3).Copy
mergedSheet.Cells(i, "BK").PasteSpecial xlPasteValues
End If
Next i
' Auto-fit the columns in the merged sheet and save the file
mergedSheet.Columns.AutoFit
objFile.Save
End If
' Close the file and quit Excel
objFile.Close SaveChanges:=True
xlApp.Quit`
First procedure will not compile. It triggers 'Method or data member not found.' error for copyRange.Value. Second procedure works. Data is copied but column headers are just Sheet 2 repeated. If you want headers from other sheets, then reference each sheet header and copy before each paste. However, select/copy/paste is not necessary. Consider:
' Add a header row to the merged sheet
Range("AV1:BC1").Value = sheet3.Range("A1:I1").Value
Range("BD1:BJ1").Value = sheet4.Range("A1:H1").Value
Range("BK1:BM1").Value = sheet5.Range("A1:D1").Value