editing lines and updating tabs

sbrown64

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

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Maybe if you post your code one can see why a re-run does no extend the correction the the tabs
Cheers
Sergio
 
Upvote 0
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]
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top