Copy paste macro based on column headers

[ridradny]

Hi,

I have been trying to work through some VBA code that can solve the following problem:

1. Copy column data from multiple sheets and consolidate into one sheet "DOH_Master";
2. Column locations change month-to-month in the sheets where I need to extract data from, I have setup "DOH_Master" with headers 1-64. I run a separate code that maps the DOH headers and the headers in each sheet, this then assigns each sheet with headers 1-64.
3. The code tries to copy the data from each column, in each sheet, then pastes it in the appropriate location in master sheet.

I have managed to get the below working to some degree, however, it appears to be copying over data and replacing it i.e. basically not matching the header numbers correctly.

Sub CopyPaste()
ExportWS = "DOH_Combine"
Dim ImportWS(1 To 2) As String
    ImportWS(1) = "Public"
    ImportWS(2) = "Private"




Dim TransCol(1 To 64) As String
    TransCol(1) = "38"
    TransCol(2) = "39"
    TransCol(3) = "1"
    TransCol(4) = "2"
    TransCol(5) = "3"
    TransCol(6) = "4"
    TransCol(7) = "5"
    TransCol(8) = "6"
    TransCol(9) = "7"
    TransCol(10) = "8"
    TransCol(11) = "9"
    TransCol(12) = "10"
    TransCol(13) = "11"
    TransCol(14) = "12"
    TransCol(15) = "13"
    TransCol(16) = "14"
    TransCol(17) = "15"
    TransCol(18) = "16"
    TransCol(19) = "17"
    TransCol(20) = "18"
    TransCol(21) = "19"
    TransCol(22) = "20"
    TransCol(23) = "21"
    TransCol(24) = "22"
    TransCol(25) = "23"
    TransCol(26) = "24"
    TransCol(27) = "25"
    TransCol(28) = "26"
    TransCol(29) = "27"
    TransCol(30) = "28"
    TransCol(31) = "29"
    TransCol(32) = "30"
    TransCol(33) = "31"
    TransCol(34) = "32"
    TransCol(35) = "33"
    TransCol(36) = "34"
    TransCol(37) = "35"
    TransCol(38) = "36"
    TransCol(39) = "37"
    TransCol(40) = "40"
    TransCol(41) = "41"
    TransCol(42) = "42"
    TransCol(43) = "43"
    TransCol(44) = "44"
    TransCol(45) = "62"
    TransCol(46) = "63"
    TransCol(47) = "61"
    TransCol(48) = "54"
    TransCol(49) = "51"
    TransCol(50) = "64"
    TransCol(51) = "45"
    TransCol(52) = "46"
    TransCol(53) = "47"
    TransCol(54) = "48"
    TransCol(55) = "49"
    TransCol(56) = "50"
    TransCol(57) = "52"
    TransCol(58) = "53"
    TransCol(59) = "55"
    TransCol(60) = "56"
    TransCol(61) = "57"
    TransCol(62) = "58"
    TransCol(63) = "59"
    TransCol(64) = "60"
    
For i = 1 To 2 'for each import sheet
    FirstImportRow = Worksheets(ImportWS(i)).Cells.Find(TransCol(1), SearchOrder:=xlByRows, SearchDirection:=xlNext).Row + 2
    LastImportRow = Worksheets(ImportWS(i)).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    DiffRows = LastImportRow - FirstImportRow
    FirstExportRow = Worksheets(ExportWS).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    ExportColumn = Worksheets(ExportWS).Cells.Find("Sheet Name", SearchOrder:=xlByRows, SearchDirection:=xlNext).Column 'defines where to insert the sheet name
    Worksheets(ExportWS).Range(Cells(FirstExportRow, ExportColumn), Cells(FirstExportRow + DiffRows, ExportColumn)) = ImportWS(i)
    For j = 1 To 64 'for each column that has to be transported
        ExportColumn = Worksheets(ExportWS).Cells.Find(TransCol(j), SearchOrder:=xlByRows, SearchDirection:=xlNext).Column 'defines where to insert the data
        ImportColumn = Worksheets(ImportWS(i)).Cells.Find(TransCol(j), SearchOrder:=xlByRows, SearchDirection:=xlNext).Column 'defines where to insert the data from
        For k = 0 To DiffRows
            Worksheets(ExportWS).Cells(FirstExportRow + k, ExportColumn) = Worksheets(ImportWS(i)).Cells(FirstImportRow + k, ImportColumn)
        Next
    Next


Next
End Sub

I'm fairly new to VBA so i'm not able to decipher where this is going wrong, any help would be greatly appreciated.

 

[ridradny]

I forgot mention in the OP:

The column headers 1-64 do not appear in all the sheets, i.e. there are sheets that skip headers 55-60 etc etc.
This may be why the data is getting replaced or inserted in incorrect locations.
If the column is not found it just needs to return blank cells but maintain copying the sheet name.