editing lines and updating tabs

sbrown64

Board Regular
Joined
Aug 23, 2019
Messages
83
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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

sergioMabres

Well-known Member
Joined
Feb 24, 2013
Messages
946
Maybe if you post your code one can see why a re-run does no extend the correction the the tabs
Cheers
Sergio
 

sbrown64

Board Regular
Joined
Aug 23, 2019
Messages
83
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

Well-known Member
Joined
Feb 24, 2013
Messages
946
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

Board Regular
Joined
Aug 23, 2019
Messages
83
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
 
Last edited:

sbrown64

Board Regular
Joined
Aug 23, 2019
Messages
83
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,519
Messages
5,523,356
Members
409,513
Latest member
TibiBenyi

This Week's Hot Topics

Top