Thread: Calling Columns into different Worksheets

I'm not sure whether this should be a general question or it should go in the programming section... but here here goes anyways

I need to create a spreadsheet where the contents of Col A & B in Sheet1 are copied into both Col A & B in Sheet2 & Sheet3. Sheet1 Col A&B will be continuously updated (rows will be added) and as these columns are updated, Col A & B in Sheet2 & Sheet3 should be updated automatically and keep the format that is set in Col A & B in Sheet1.

Now, Col C&D in Sheet2 & 3 will be updated by a different user. As the user updates the cells in Col C&D in Sheet2 & 3, Col C&D in Sheet1 should automatically be updated with the data in the cells of Col C&D in Sheet2 (and keep the format) and Col E&F in Sheet1 should automatically be updated with the data in the cells of Col C&D in Sheet3 (and keep the format).

Thanks!

You don't ever resort the data, do you? If not, standard relative references would suffice, wouldn't they?

On Sheet2 cell A2 =Sheet1!A2

Copy over one cell, then down as far as you want.

Do the opposite for C/D and E/F.

I would set my formatting ahead of time myself one time going down as far as I think I need it for this sheet. If it's a repetitive format, or alternating, once you've done a set that looks right, use the Format Painter to paint the formatting settings into the cells below in the same pattern. Quick and easy.

As far as I know, you can't move formats without code, hence:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
    Select Case Sh.Name
    Case "Sheet1"
        If Target.Column < 3 Then
            Sheets(Sh.Name).Columns("A:B").Copy Sheets("Sheet2").Columns("A:B")
            Sheets(Sh.Name).Columns("A:B").Copy Sheets("Sheet3").Columns("A:B")
        End If
    Case "Sheet2"
        If Target.Column = 3 Or Target.Column = 4 Then Sheets(Sh.Name).Columns("C:D").Copy Sheets("Sheet1").Columns("C:D")
    Case "Sheet3"
        If Target.Column = 3 Or Target.Column = 4 Then Sheets(Sh.Name).Columns("C:D").Copy Sheets("Sheet1").Columns("E:F")
    End Select
Application.EnableEvents = True
End Sub

Fairly basic but it does the job.

HTH

Shouldn't that last bit of code copy from Sheet 3 C/D to Sheet 1 E/F?

Case "Sheet3"
        If Target.Column = 3 Or Target.Column = 4 Then Sheets(Sh.Name).Columns("C:D").Copy Sheets("Sheet1").Columns("E:F")
    End Select

Because the three things he needed to happen were:

1. Two columns from Sheet1 copied to 2 & 3 (OK)
2. Two columns from Sheet2 copied to Sheet1 (OK)
3. Two columns from Sheet3 copied to Sheet1 (seems to be missing)

That last line of code in CASE3, both destinations should be Sheet1 and to my mind, the 2nd reads as Sheet2 as the destination.

Am I reading it wrong?

    Case "Sheet2"
        If Target.Column = 3 Or Target.Column = 4 Then Sheets(Sh.Name).Columns("C:D").Copy Sheets("Sheet1").Columns("C:D")
    Case "Sheet3"
        If Target.Column = 3 Or Target.Column = 4 Then Sheets(Sh.Name).Columns("C:D").Copy Sheets("Sheet2").Columns("E:F")

Thank God! I don't program at the level you guys do, so I am desperately trying to teach myself/understand the advanced stuff you guys post...so I'm glad I'm able to decipher some of it properly. Hehe.