Forum Discussion
is there a way to make sure my macro doesn't duplicate data
I posted in another blog and haven't received a response so I am not sure that what I am asking can be done. I used code from the following article https://docs.microsoft.com/en-us/office/vba/api/excel.range.copy to partially accomplish my goal but I have a few more questions.
I have a file where I dump data every month and then extrapolate data on other tabs. Within that file, I have a "master" sheet where I do the data dump and I was able to copy the macro from the article above and amend it to send data based on data in a specific column (TERR) to a variety of sheets. What I need to accomplish is this - I would like to continue adding to the same sheets each month but if I run the macro, it will keep adding all of the same data every time (in other words, I don't want it to add the previously added data every time I add new data to the master sheet and then run the macro). Is there a way to add to the macro so that it does not add duplicate rows? I could use a combination of two other columns (Item and Invoice) to look at whether or not the data is duplicate. Also, there are columns of data that have formulas in them and when I paste the data to my other sheets, I would like to paste special values instead of the formula. The goal is that I can then sort and filter the data on the other sheets in a variety of ways and give that information to other people. Thanks for your help!!!
Public Sub CopyRows() Sheets("MORSE Item Sales Analysis").Select FinalRow = Cells(Rows.Count, 1).End(xlUp).Row For x = 2 To FinalRow ThisValue = Cells(x, 29).Value If ThisValue = "FORN" Then Cells(x, 1).Resize(1, 48).Copy Sheets("FORN").Select NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1 Cells(NextRow, 1).Select ActiveSheet.Paste Sheets("MORSE Item Sales Analysis").Select ElseIf ThisValue = "GRLK" Then Cells(x, 1).Resize(1, 48).Copy Sheets("GRLK").Select NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1 Cells(NextRow, 1).Select ActiveSheet.Paste Sheets("MORSE Item Sales Analysis").Select ElseIf ThisValue = "NEST" Then Cells(x, 1).Resize(1, 48).Copy Sheets("NEST").Select NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1 Cells(NextRow, 1).Select ActiveSheet.Paste Sheets("MORSE Item Sales Analysis").Select ElseIf ThisValue = "NWST" Then Cells(x, 1).Resize(1, 48).Copy Sheets("NWST").Select NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1 Cells(NextRow, 1).Select ActiveSheet.Paste Sheets("MORSE Item Sales Analysis").Select ElseIf ThisValue = "PLNS" Then Cells(x, 1).Resize(1, 48).Copy Sheets("PLNS").Select NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1 Cells(NextRow, 1).Select ActiveSheet.Paste Sheets("MORSE Item Sales Analysis").Select ElseIf ThisValue = "NEST" Then Cells(x, 1).Resize(1, 48).Copy Sheets("NEST").Select NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1 Cells(NextRow, 1).Select ActiveSheet.Paste Sheets("MORSE Item Sales Analysis").Select ElseIf ThisValue = "NWST" Then Cells(x, 1).Resize(1, 48).Copy Sheets("NWST").Select NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1 Cells(NextRow, 1).Select ActiveSheet.Paste Sheets("MORSE Item Sales Analysis").Select End If Next x End Sub
9 Replies
Please give this a try and see if this works for you...
Sub CopyRows() Dim wsSource As Worksheet Dim wsDest As Worksheet Dim shArr As Variant Dim sh As Variant Dim FinalRow As Long Dim NextRow As Long Application.ScreenUpdating = False Set wsSource = ThisWorkbook.Worksheets("MORSE Item Sales Analysis") wsSource.AutoFilterMode = False FinalRow = wsSource.Cells(Rows.Count, 1).End(xlUp).Row shArr = Array("FORN", "GRLK", "NEST", "NWST", "PLNS") With wsSource.Range("A1").CurrentRegion For Each sh In shArr .AutoFilter field:=29, Criteria1:=sh If .Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then Set wsDest = ThisWorkbook.Worksheets(sh) wsDest.Range("A1").CurrentRegion.Offset(1).ClearContents wsSource.Range("A2:AB" & FinalRow).SpecialCells(xlCellTypeVisible).Copy wsDest.Range("A2") End If Next sh End With wsSource.AutoFilterMode = False Application.ScreenUpdating = True MsgBox "Task Completed!", vbInformation End SubSubodh_Tiwari_sktneer thank you for your help. Do I insert this after all of the code I showed in my message or do I need to insert this for each section I reference in the code? Once I know what to do with it, I will follow up to let you know how it works.
I appreciate you!
