I have two data sheets within the same excel file: Sheet1 as "Data" with 7 columns:
The second sheet is "Main" with 5 columns:
The same column to match the two files is "name". I want to have a VBA code that matches the name on both sheet and copy data from proc1 - Proc4 from sheet "Main" to sheet "data" by matching the column names on both sheets.
I searched stack overflow for similar question and here is the code that I found (modified it slightly):
Sub CopyData()
Dim shtImport As Worksheet
Dim shtMain As Worksheet
Set shtImport = ThisWorkbook.Sheets("Data")
Set shtMain = ThisWorkbook.Sheets("Main")
Dim CopyColumn As Long
Dim CopyRow As Long
Dim LastColumn As Long
'- for each column in row 1 of import sheet
For CopyColumn = 1 To shtImport.Cells(1, shtImport.Columns.Count).End(xlToRight).Column
'- check what the last column is with data in column
LastRowOfColumn = shtImport.Cells(shtImport.Columns.Count, CopyColumn).End(xlToRight).Column
'if last column was larger than one then we will loop through rows and copy
If LastColumn > 1 Then
For CopyRow = 1 To LastColumn
'- note we are copying to the corresponding cell address, this can be modified.
shtMain.Cells(CopyRow, CopyColumn).value = shtImport.Cells(CopyRow, CopyColumn).value
Next CopyRow
End If
Next CopyColumn
End Sub
This is not working the way I want it to work. Can somebody please help me with this problem. Thanks a lot!