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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top