Reflecting columns additions from one sheet on another sheet

michaeltsmith93

Board Regular
Joined
Sep 29, 2016
Messages
83
I have two sheets, A and B. Sheet A is a master list of people with data in each person's row corresponding to that person. I've written a userform that allows me to move people's row of data from A to B.

We're still in the preliminary stages of our project, so columns are being added to A sporadically. I want to reflect these changes on B, but I don't want to use Worksheet Change because I don't want to lose Undo, so I'm planning to add the code to the userform button that moves the rows. What's the best way to go about adding the new columns from A to B? My goal is that a person on A can't be moved to B via the button without first reconciling the columns.

I should add that it's very unlikely that columns will be deleted, and if they are, I can do that manually.

I imagine it will be something like:

Code:
Dim SheetA as Worksheet
Dim SheetB as Worksheet
Dim LastColumn As Long

LastColumn = range.find().column
Set SheetA = "A"
Set SheetB = "B"

For i = 1 to LastColumn

If SheetA.Cells(4, i) <> SheetB.Cells(4, i) Then

SheetB.Cells(4, i+1).Insert
SheetB.Cells(4, i+1).Value = SheetA.Cells(4, i).Value

End If

Next
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,436
Office Version
  1. 2013
Platform
  1. Windows
more like this

Code:
Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, fn As Range
Set sh1 = Sheets("A")
Set sh2 = Sheets("B")
    With sh1
        For Each c In .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
            Set fn = sh2.Range("A:A").Find(c.Value, , xlValues)
                If Not fn Is Nothing Then
                    aCol = .Cells(c.Row, Columns.Count).End(xlToLeft).Column
                    bCol = sh2.Cells(fn.Row, Columns.Count).End(xlToLeft).Column
                    If aCol > bCol Then
                        .Range(.Cells(c.Row, bCol + 1), .Cells(c.Row, Columns.Count).End(xlToLeft)).Copy fn.Offset(, bCol)
                    End If
                End If
        Next
    End With
End Sub
 

michaeltsmith93

Board Regular
Joined
Sep 29, 2016
Messages
83
Thanks for this. Could you please explain to me what is being accomplished by Lines 6 and 12? In line 12, what is the second bound of the range getting? The last column on the sheet?
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,436
Office Version
  1. 2013
Platform
  1. Windows
Same code, but annotated with comments to explain what it does.

Code:
Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, fn As Range
Set sh1 = Sheets("A")
Set sh2 = Sheets("B")
    With sh1
        For Each c In .Range("A2", .Cells(Rows.Count, 1).End(xlUp)) 'Initiate loop to step down columm A of sheet 1
            Set fn = sh2.Range("A:A").Find(c.Value, , xlValues) 'Search in sheet 2 for the value of each cell from sheet 1
                If Not fn Is Nothing Then 'Test if value is found
                    aCol = .Cells(c.Row, Columns.Count).End(xlToLeft).Column 'initialize variable for sheet 1 end of row
                    bCol = sh2.Cells(fn.Row, Columns.Count).End(xlToLeft).Column 'same thing for sheet 2
                    If aCol > bCol Then 'If sheet 1 row is longer then copy the extra cells from sheet 1 to sheet 2 for that item
                        .Range(.Cells(c.Row, bCol + 1), .Cells(c.Row, Columns.Count).End(xlToLeft)).Copy fn.Offset(, bCol) 
                    End If
                End If
        Next
    End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,987
Messages
5,526,068
Members
409,685
Latest member
Davetom

This Week's Hot Topics

Top