1

My problem is that I need items within a workbook in excel 2007 to combine into a single sheet, but then the duplicates need to be removed. I've gotten most of this to work with the code below, I've even got it set up to do it so many second, minutes, hours after the doc is opened.

There are two remaning problems though:

  • When I share this document, allow others to edit at the same time, it breaks and will no longer remove the duplicates. Not to mention it posts the headers as many times as there are sheets.
  • I don't know how to keep this thing from going crazy, when I'm compiling the code it does this thing where it will run about correctly for the first couple of cycles and then it starts repeating rapidly.

I'm no coding expert, and I apologize for the noob card, but I don't know what to do. 

        Private Sub Workbook_Open

    Application.OnTime Now + TimeValue("00:00:10), "ThisWorkbook.Combine" 'this ten second loop is an example'

    End Sub
Sub Combine
Applicatio.DisplayAlerts = False
Dim J as Integer
One Error Resume Next
Sheets(1).Select
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1:F1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offeset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(x1Up)(2)

End Sub

Next

Sub RemoverDuper

Dim icol As Long

With Sheets("Combined")
icol = Application.Match("Number"),.Rows(1),0)
With .Cells(1, 1).CurrentRegion
.RemoveDuplicates Columns:=icol, Header:=x1No
Application.DisplayAlerts = True

Sheets(1).Select
Sheets(1).Name = "Combined"

End With
End With
Next
End Sub

PS if there is a syntax error or anything like that please point it out to me and know I had to retype it all from memory so if there is an error I would love to know.

braX
  • 11,506
  • 5
  • 20
  • 33
Jett
  • 11
  • 1
  • removed chatter, formatted question. – Luuklag May 25 '18 at 08:29
  • 1
    While writing code NEVER put in `On Error resume Next` this way you will skip over any error that occurs making debugging impossible! – Luuklag May 25 '18 at 08:30
  • Your code does not compile, it is full of typos. Please make sure the code you actually use is **exactly** the code you show here. • This statement `On Error Resume Next` should be removed, otherwise you cannot see any error messages as @Luuklag pointed out and therefore you cannot debug a code with that line. • Read and apply [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) at first this will fix many issues. – Pᴇʜ May 25 '18 at 09:27

0 Answers0