none
Copy some columns of worksheet to other?

    Question

  • Hi,

    I have excel data with multiple worksheets, in this I have to add new entries manually to all the worksheets everytime we have some new information. Is it possible to automatically add/delete cell entries on all the worksheet when I do changes to Master worksheet. I have to copy the data along with colors.

    Thank you,

    -Hareesh.


    Best Regards, -Hareesh. If you think my post is the answer to your question, please mark it as answer so future visitors can easily find it.
    Wednesday, January 25, 2012 11:28 AM

Answers

  • Copy the code below, right-click the sheet tab of your Master sheet, select "View Code" and paste the code into the window that appears.

    When you enter data into the master, format the four columns the way you want. Then enter the values into columns A, b, and C. When you enter the fourth value into column D, the code will:

    Copy the four columns to the bottom on the column on all other sheets

    Copy the formula from column E to the new row, if it isn't already there.

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim shtSheet As Worksheet
        If Target.Column <> 4 Then Exit Sub
        If Target.Cells.Count <> 1 Then Exit Sub
        If Target.Offset(0, 1).HasFormula Then Exit Sub
        If Application.CountA(Cells(Target.Row, 1).Resize(1, 3)) <> 3 Then Exit Sub
        Application.EnableEvents = False
        Cells(Target.Row, 1).Resize(1, 4).Copy
        For Each shtSheet In Worksheets
            If shtSheet.Name <> Target.Parent.Name Then
                shtSheet.Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial (xlPasteAll)
            End If
        Next shtSheet
        For Each shtSheet In Worksheets
            With shtSheet.Cells(Rows.Count, 5).End(xlUp)
                If .Cells(2, -1).Value <> "" Then
                    .Copy .Cells(2)
                End If
                End With
        Next shtSheet

        Application.EnableEvents = True
    End Sub

     

     


    HTH, Bernie
    Friday, January 27, 2012 2:15 PM

All replies

  • You can do it, using macros, based on whatever criteria you can determine from where you enter the data.

    But it is a better idea to only use one sheet, and either filter the data or use pivot tables to show the information that you want.

    It is rarely necessary to enter data into two different places, if you design your database correctly.


    HTH, Bernie
    Wednesday, January 25, 2012 4:02 PM
  • Thank you, attached is an example (jpg) sheet I want to have in all sheets, Here columns A B C D remain same for all worksheets, but Rating data changes. So I want to have A B C D colums copied automatically to all the worksheets. Apart from this as and when I have new servers I add it in master sheet and they should be copied to other sheets automatically along with colors I used in master sheet. Could you please get some macros or pivot tables for this example?

     

    Than you,

    -Hareesh.


    Best Regards, -Hareesh. If you think my post is the answer to your question, please mark it as answer so future visitors can easily find it.
    Friday, January 27, 2012 3:54 AM
  • Copy the code below, right-click the sheet tab of your Master sheet, select "View Code" and paste the code into the window that appears.

    When you enter data into the master, format the four columns the way you want. Then enter the values into columns A, b, and C. When you enter the fourth value into column D, the code will:

    Copy the four columns to the bottom on the column on all other sheets

    Copy the formula from column E to the new row, if it isn't already there.

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim shtSheet As Worksheet
        If Target.Column <> 4 Then Exit Sub
        If Target.Cells.Count <> 1 Then Exit Sub
        If Target.Offset(0, 1).HasFormula Then Exit Sub
        If Application.CountA(Cells(Target.Row, 1).Resize(1, 3)) <> 3 Then Exit Sub
        Application.EnableEvents = False
        Cells(Target.Row, 1).Resize(1, 4).Copy
        For Each shtSheet In Worksheets
            If shtSheet.Name <> Target.Parent.Name Then
                shtSheet.Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial (xlPasteAll)
            End If
        Next shtSheet
        For Each shtSheet In Worksheets
            With shtSheet.Cells(Rows.Count, 5).End(xlUp)
                If .Cells(2, -1).Value <> "" Then
                    .Copy .Cells(2)
                End If
                End With
        Next shtSheet

        Application.EnableEvents = True
    End Sub

     

     


    HTH, Bernie
    Friday, January 27, 2012 2:15 PM