editing lines and updating tabs

[sbrown64]

Hi

I have a spreadsheet running vba which separates data into the relevant tabs. my problem is after running the macro, if i need to correct a line in the master sheet it doesnt update or create a new tab.

So for instance, tab 2 has 2 lines of info, i then go to the master sheet and edit 1 line changing the company name, I re-run the macro, however tab 2 still shows both entries and a new tab hasnt been created.

If I enter a new line of info with a new company name and run, it works ok.

I hope you can help.

Regards

Stephen

 

[sergioMabres]

Maybe if you post your code one can see why a re-run does no extend the correction the the tabs
Cheers
Sergio

 

[sbrown64]

hi see the code below

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub sbrown64()
Dim Cl As Range
Dim Ky As Variant
Dim Ws As Worksheet
Dim UsdRws As Long

On Error GoTo Xit
Application.ScreenUpdating = False
Set Ws = Sheets(1)
If Ws.AutoFilterMode Then Ws.AutoFilterMode = False
UsdRws = Ws.Range("D" & Rows.Count).End(xlUp).Row
With CreateObject("Scripting.dictionary")
.CompareMode = vbTextCompare
For Each Cl In Ws.Range("D5:D" & UsdRws)
If Cl.Value <> "" Then .Item(Trim(Cl.Value)) = Empty
Next Cl
For Each Ky In .Keys
Ws.Range("A4:M" & UsdRws).AutoFilter 4, Ky
Ws.Range("A4:M" & UsdRws).AutoFilter 13, ""
If Not Evaluate("isref('" & Left(Ky, 30) & "'!A1)") Then
Sheets.Add(, Sheets(1)).Name = Left(Ky, 30)
Ws.Range("A1:A" & UsdRws).SpecialCells(xlVisible).EntireRow.Copy Sheets(Left(Ky, 30)).Range("A1")
Ws.Range("M5:M" & UsdRws).SpecialCells(xlVisible).Value = Date
Else
On Error Resume Next
Ws.Range("A5:A" & UsdRws).SpecialCells(xlVisible).EntireRow.Copy Sheets(Left(Ky, 30)).Range("D" & Rows.Count).End(xlUp).Offset(1, -3)
Ws.Range("M5:M" & UsdRws).SpecialCells(xlVisible).Value = Date
On Error GoTo Xit
End If
Next Ky
End With
Ws.AutoFilterMode = False
Exit Sub
Xit:
Ws.AutoFilterMode = False
MsgBox "The macro encountered an error" & vbLf & "Error " & Err.Number & " " & Err.Description
End Sub


Regards

Stephen
[/FONT]

 

[sergioMabres]

Best way to do what you need, it is to delete the tab and run the macro, it will recreate the tab with all the updated information
Cheers
Sergio

 

[sbrown64]

Hi

I tried what you said and when i ran the macro the following error appeared.
The macro encountered an error.
error 1004 no cells were found.

The edited line did not appear on the existing tab and the deleted tab re-appeared, and if i delete both tabs and re-run the macro the existing tab for the now edited list reappers and the other tab which has quite a few entries didnt get created?

update
If I delete the entire line and then enter new info it will go to the relevant tab, however if you want to update a line to change any info, it does not update the tab

 

[sbrown64]

Hi

I tried what you said and when i ran the macro the following error appeared.
The macro encountered an error.
error 1004 no cells were found.

The edited line did not appear on the existing tab and the deleted tab re-appeared, and if i delete both tabs and re-run the macro the existing tab for the now edited list reappers and the other tab which has quite a few entries didnt get created?

update
If I delete the entire line and then enter new info it will go to the relevant tab, however if you want to update a line to change any info, it does not update the tab​