VBA reordering columns from different workbook or sheet

0 votes

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
Feb 4, 2023 in Others by Kithuzzz
• 38,010 points
237 views

1 answer to this question.

0 votes

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
answered Feb 4, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

Excel: VLOOKUP from a different Sheet

The worksheet name must be included in ...READ MORE

answered Dec 10, 2022 in Others by narikkadan
• 63,420 points
361 views
0 votes
1 answer

Excel: Group rows and add minimum and maximum from two different columns within the group

You can accomplish your goal with Power ...READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 63,420 points
573 views
0 votes
1 answer

Converting Textboxes Link and/or Formula to Values in a Copied Sheet using Excel VBA

Copy the values using Range and Value2 With ActiveSheet.UsedRange ...READ MORE

answered Jan 31, 2023 in Others by narikkadan
• 63,420 points
311 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
829 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,123 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
418 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
687 views
0 votes
1 answer

Excel: How to merge two columns into one (from different sheets or separated columns)

This equation is completely adjustable. Your two ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,420 points
507 views
0 votes
1 answer

VBA Code to Populate Balance Sheet from Trial Balance Data

It appears that what you need to ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 63,420 points
775 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP