Reflecting columns additions from one sheet on another sheet

[michaeltsmith93]

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:

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

 

[JLGWhiz]

more like this

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]

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]

Same code, but annotated with comments to explain what it does.

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