VBA reordering columns from different workbook or sheet

Question

I'm attempting to replace the incorrectly arranged column headings with the data from another dataset. Therefore, I'm attempting to change the string "a,c,d,b" from "bad" to "good" by adding their headers and data before pasting it on a new page, fixed. The code executes, but no values are printed on the new sheet. The extra headers would be inserted at the end because the data's column lengths vary.

Option Explicit

Sub OrderColumns()
    
    Dim ws As Worksheet, gws As Worksheet, bws As Worksheet, header As String
    Dim gcols As Long, bcols As Long, c As Range, i As Long, fcol As Long

    Set gws = Worksheets("Good Columns")
    Set bws = Worksheets("Bad Columns")
    
     gcols = gws.Range("MD1").End(xlToLeft).Column
     bcols = bws.Range("MD1").End(xlToLeft).Column
    
    With ThisWorkbook
        Set ws = .Sheets.Add(Before:=.Sheets(.Sheets.Count))
        ws.Name = "Fixed"
    End With
    
    fcol = 1
    
    For i = 1 To gcols
        
        header = gws.Cells(1, i)
        
            With bws
            Set c = Range(Cells(1, 1), Cells(1, bcols)).Find(header, LookIn:=xlValues, lookat:=xlWhole)
            End With
            
            If (Not c Is Nothing) Then
                Cells(1, c.Column).EntireColumn.Copy Sheets("Fixed").Cells(1, bcols)
                fcol = fcol + 1
            End If

    Next i
    
End Sub

Where this was written from this code since I wasn't defining variables along with using select statements:

Sub Rearange_Column_Order()

    Sheets("Bad Columns").Select
    i = Sheets("Bad Columns").Index
    
    Sheets.Add
    Sheets(i).Name = "Fixed"
    
    gcols = Sheets("Good Columns").Range("IV1").End(xlToLeft).Column
    bcol = Sheets("Bad Columns").Range("IV1").End(xlToLeft).Column
    
    fcol = 1
    
    For i = 1 To gcols
        header = Sheets("Good Columns").Cells(1, i)
        Sheets("Bad Columns").Select
        Set c = Range(Cells(1, 1), Cells(1, bcol)).Find(header, LookIn:=xlValues, lookat:=xlWhole)
        If (Not (c) Is Nothing) Then
            Cells(1, c.Column).EntireColumn.Copy Sheets("Fixed").Cells(1, fcol)
            fcol = fcol + 1
        End If
    Next i
End Sub

Answer 1

Try this:

Sub OrderColumns()

    Dim ws As Worksheet, gws As Worksheet, bws As Worksheet, header As String
    Dim gcols As Range, bcols As Range, c As Range
    Dim wb As Workbook, f As Range, pasteDest As Range

    Set wb = ThisWorkbook 'use a specific workbook for all sheets...
    Set gws = wb.Worksheets("Good Columns")
    Set bws = wb.Worksheets("Bad Columns")

    Set ws = wb.Sheets.Add(Before:=wb.Sheets(wb.Sheets.Count))
    ws.name = "Fixed"

    Set gcols = gws.Range("A1", gws.Cells(1, Columns.Count).End(xlToLeft))
    Set bcols = bws.Range("A1", bws.Cells(1, Columns.Count).End(xlToLeft))

    Set pasteDest = ws.Range("A1") 'start pasting here
    For Each c In gcols 'loop over "good" headers
        'find in "bad" headers
        Set f = bcols.Find(what:=c.Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not f Is Nothing Then
            'if found, copy whole column
            f.EntireColumn.Copy pasteDest
            Set pasteDest = pasteDest.Offset(0, 1) 'move one column over
        End If
    Next c

End Sub