code for Duplicating information between excel spreadsheets

Question

I am trying to figure out how to do a macro that will copy data from one sheet titled Master Sheet onto another sheet titled 2015November but only when an 2015November is found in column k on the Master Sheet. If 2015November is found in column K, then I need all data in cells C, H and J (on that row) to be copied onto the Sheet 2015November into corresponding columns A,B, AND C. I have to duplicate this code a few times. so that it corresponds to the month of sales to create a pipeline of estimated sales for the month.

I've been watching Youtube videos and have tried but can't figure it out.

I need it to find the next blank row to insert it into and I need it to not duplicate any data. Any help would be appreciated! I am using Excel 2011

this is code I've been using

Sub copycolumns()
Dim lastrow As Long, erow as long

Lastrow=sheet1.cells(rows.count,1).end(xlUp).Row

for i=4 to lastrow
Sheet1.Cells(i,1).Copy
erow=sheet2.Cells(Rows.Count,1).end(xlUp).Offset(1,0).Row

sheet1.Paste Destination=Worksheets(“Sheet2”).Cells(erow,1)

sheet1.Cells(i,3).Copy
sheet1.Paste Destination=Worksheets(“Sheet2”).Cells(erow,2)

sheet1.Cells(i,8).Copy
sheet1.Paste Destination=Worksheets(“Sheet2”).Cells(erow,3)

sheet1.Cells(i,10).Copy
sheet1.Paste Destination=Worksheets(“Sheet2”).Cells(erow,4)

Next i

application.CutCopyMode = False
sheet2.columns().Autofit
Range(“A1”).Select

youtube.com/watch?v=mtrMG_xk4vE i changed the fields based on my sheet and i received errors — Jennifer Blandino
– Jennifer Blandino, Commented Nov 19, 2015 at 19:26
Can you post any code you have? Also, can "November2015" be in any cell in Column K? Or where "November2015" does appear, use that row? — BruceWayne
– BruceWayne, Commented Nov 19, 2015 at 19:27
so if the sales date changes, in column k i want to reflect in the sheet associated with the month. i have the sheet i can send so you understand better — Jennifer Blandino
– Jennifer Blandino, Commented Nov 19, 2015 at 19:29
but if i change it to 2015December then i want it to move to the sheet titled 2015 December so i could change the information in column k. — Jennifer Blandino
– Jennifer Blandino, Commented Nov 19, 2015 at 19:30

Scott Holtzman · Accepted Answer · 2015-11-20 15:28:18Z

1

How's this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 11 Then
    Dim masterWS As Worksheet, altWS As Worksheet
    Dim copy1$, copy2$, copy3$
    Dim altLastRow&

    Set masterWS = Sheets("Master Sheet")
    On Error GoTo ErrHandler
    Set altWS = Sheets(Target.Value)
    On Error GoTo 0 ' ### EDIT By Scott Holtzman ###

    copy1 = masterWS.Cells(Target.Row, 3).Valu
    copy2 = masterWS.Cells(Target.Row, 8).Value
    copy3 = masterWS.Cells(Target.Row, 10).Value

    altLastRow = altWS.Cells(altWS.Rows.Count, 1).End(xlUp).Row
    If Not IsEmpty(altWS.Cells(1, 1)) Then altLastRow = altLastRow + 1
    altWS.Cells(altLastRow, 1).Value = copy1
    altWS.Cells(altLastRow, 2).Value = copy2
    altWS.Cells(altLastRow, 3).Value = copy3

ErrHandler:
    Dim addSheet$
    If Err.Number = 9 Then
        addSheet = MsgBox("The " & Target.Value & " sheet doesn't exist, create it?", vbYesNo)
        If addSheet = vbYes Then
            Sheets.Add.Name = Target.Value
            Sheets(Target.Value).Move after:=masterWS
            Set altWS = Sheets(Target.Value)
        Else
            Exit Sub
        End If
        Resume Next

    End If
End If
masterWS.Activate
End Sub

Pretty straightforward. I tested it and it worked okay for me:

"Master Sheet":

"2015November" sheet:

Edit: Updated to include an error handler, in case your sheet doesn't exist. (Note: I'm pretty new to Error Handlers, so if someone has a tip/advice, I'd appreciate it!).

Edit2: Updated to be a Worksheet_Change. Place this code in your "Master Sheet" module.

edited Nov 20, 2015 at 15:28

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

answered Nov 19, 2015 at 19:50

BruceWayne

23.5k16 gold badges75 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Jennifer Blandino Over a year ago

I'm going to test it now. but either way thank you so much and i appreciate it so much.. ill update you as soon i try it in 5 minutes

BruceWayne Over a year ago

@JenniferBlandino - It won't run when you make a change, as it is. I can update it though, just clarify: the changes you make will be to column K, and when that change is made, you want the macro to run. Do you want it to run through ALL cells with a value in column K? Or just on the row you changed?

Jennifer Blandino Over a year ago

what do you mean by Run throught ALL cells?

BruceWayne Over a year ago

Great @Scott! Good catch, I edited it to just leave the Sub if they click "No".

Scott Holtzman Over a year ago

Bruce - I edited your code and placed it there with a comment marker. You can re-edit or remove if you want, just thought it was easier that way.

|

Scott Holtzman · Accepted Answer · 2015-11-19 20:13:52Z

0

Place this code in the Master Worksheet Module.

I know you said your sheets are there already, but I added some error testing in case you type the name wrong or don't have the sheet after all. The code assumes the sheet names will be equal to the sales dates in column K.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 11 Then ' column K

    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(Target.Value2)
    On Error GoTo 0 

    If Not ws Is Nothing Then 

         With ws

             Dim lRow As Long
             lRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1

             .Range("A" & lRow).Value = Me.Cells(Target.Row, 3) ' Column C
             .Range("B" & lRow).Value = Me.Cells(Target.Row, 8) ' Column H
             .Range("C" & lRow).Value = Me.Cells(Target.Row, 10) ' Column J

         End With

    Else

         Msgbox "Sheet Does Not Exist! Add sheet and modify cell again!"

    End If

End If

End Sub

edited Nov 19, 2015 at 20:13

answered Nov 19, 2015 at 19:56

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

8 Comments

BruceWayne Over a year ago

We've been thinking quite the same today, I like the error handling. I (coincidentally!) added an error handler as well, but am not positive if I did it correctly/efficiently - do you mind taking a peek?

Jennifer Blandino Over a year ago

i pasted this in the Module and saved. but nothing happened. should i deleted the sheets i created?

Scott Holtzman Over a year ago

it looks pretty good @BruceWayne. I would just move the On Error GoTo ErrHandler statement right before the Set altWS = Sheets(cel.Value) statement since you would not want the code to there anytime before that. Then add On Error GoTo 0 after the Set altWS ... statement so that it will not go there again.... (Actually the ResumeNext may be enough)

Scott Holtzman Over a year ago

@JenniferBlandino. Make a change in column K for the sales date to fire the event - which runs the code. Keep all the sheets! Also replace the REM comment tags with '.

Scott Holtzman Over a year ago

tell me what is happening or not happening? (I can't test fully because I don't have your data, but it works great for me)

|

Collectives™ on Stack Overflow

code for Duplicating information between excel spreadsheets

2 Answers 2

12 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related