Forum Discussion

Suzanne25811's avatar
Suzanne25811
Copper Contributor
May 13, 2022
Solved

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/e...
excel
Macros and VBA
  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    May 16, 2022

    Suzanne25811 

     

    Just replace your existing code with the proposed code and see if that works for you.

Suzanne25811's avatar
Suzanne25811
Copper Contributor
May 16, 2022

Subodh_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! 

Subodh_Tiwari_sktneer's avatar
Subodh_Tiwari_sktneer
Silver Contributor
May 16, 2022

Suzanne25811 

 

Just replace your existing code with the proposed code and see if that works for you.

  • Suzanne25811's avatar
    Suzanne25811
    Copper Contributor
    May 16, 2022
    It worked in my test file!!! Thank you SO much!!!
    • Subodh_Tiwari_sktneer's avatar
      Subodh_Tiwari_sktneer
      Silver Contributor
      May 17, 2022
      You're welcome! Glad it worked as desired.
      • Suzanne25811's avatar
        Suzanne25811
        Copper Contributor
        May 17, 2022

        Subodh_Tiwari_sktneer 

        Good morning!

        Yesterday, I added a couple more territories.  Adding the territories worked, I ran the macro and it added the info to the appropriate tabs.   Then I noticed that I was missing columns at the end - when I originally started this there were 29 columns and we have added more so the sheet is now 45 columns wide.  

         

        This morning I updated the macro to reflect 45 columns.  I tried re-running the macro but it did not add the "missing" info.  I deleted the information off the tabs, saved the file, and re-ran the macro again but nothing at all transferred to my other sheets FORN, GRLK, NEST, etc.  Can you help?

         

        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", "SEST", "SWST", "WCAN", "ECAN")

        With wsSource.Range("A1").CurrentRegion
        For Each sh In shArr
        .AutoFilter field:=46, 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:AS" & FinalRow).SpecialCells(xlCellTypeVisible).Copy wsDest.Range("A2")
        End If
        Next sh
        End With
        wsSource.AutoFilterMode = False
        Application.ScreenUpdating = True
        MsgBox "Task Completed!", vbInformation
        End Sub

         

Resources