# Reflecting columns additions from one sheet on another sheet

#### michaeltsmith93

##### Board Regular
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``````

### 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
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
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
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``````

Replies
13
Views
162
Replies
13
Views
108
Replies
3
Views
198
Replies
1
Views
49
Replies
3
Views
194