Delete sheets if cell A2 is empty in VLoc Array sheets is blank
Hello Experts,
Whenever I run this code, around 40 sheets are created from one source sheet. The sheets with data depends on column 19 values. If there are 10 unique values in the column then 30 of the created sheets have no data and unwanted. The unique values can be 5 -25 in different scenarios.
My question is how can I avoid creating the sheets which are not in the unique values or create all the sheets as per the code and then delete all the sheets where the cell A2 is empty. Please note that only the unique sheets in the array should be deleted as some of the other sheets have values starting from cell A5 or A6 as the case may be.
Thank you in advance.
Re: Delete sheets if cell A2 is empty in VLoc Array sheets is blank
May be:
PHP Code:
Option Explicit
Sub B2B_ExtractStateWise()
Application.StatusBar = "Extracting from B2B Data..." ' Display Status Bar to Update user to what is happening
Dim lr As Long, i As Long, rng, code, wsname As String
Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary") ' create a dictionary, to store unique code
lr = Cells(Rows.Count, 19).End(xlUp).Row ' last used row of column S
rng = Range("S2:S" & lr).Value ' read column S into array
For i = 1 To UBound(rng)
If Not dic.exists(rng(i, 1)) Then dic.Add rng(i, 1), "" ' read unique code into dictionary
Next
Application.ScreenUpdating = False '
For Each code In dic.keys ' loop thru each unique code
wsname = "B2B" & code
If Evaluate("=ISREF(" & wsname & "!A1)") Then Sheets(wsname).Delete 'delete old sheet, if exists
Application.CopyObjectsWithCells = False ' to avoid copy objects
Sheets("B2B").Copy after:=Sheets("B2B") ' Create a duplicate sheet of 'B2B' after sheet 'B2B'
ActiveSheet.Name = wsname 'name the new sheet
Application.CopyObjectsWithCells = True ' Remove the setting to not copy buttons when copying sheet
With Cells(1).CurrentRegion
.AutoFilter 19, "<>" & code
.Offset(1).SpecialCells(12).EntireRow.Delete
.AutoFilter
End With
Next
Application.StatusBar = False
Application.ScreenUpdating = True ' Turn ScreenUpdating off
End Sub
Re: Delete sheets if cell A2 is empty in VLoc Array sheets is blank
Bbebo. You read my mind. I was just imagining it to do the way you wrote this line but due to lack of knowledge I was not able to do so. Will test the code and revert back.
ng = Range("S2:S" & lr).Value ' read column S into array
Re: Delete sheets if cell A2 is empty in VLoc Array sheets is blank
The code is running fine the first time. But I run it for the second time it is displaying a confirmation message whether to delete the sheet or not. I can avoid by inserting Display alert and on error resume and so on. When I ran the code in the original data it is displaying an error as the sheet is not mentioned which sheet to select column S from. As I have assigned the code to a button it will not work properly. If I run the code from the B2B sheet then only it will work. If I insert the sheet name then hopefully it may work without any hindrance.
Re: Delete sheets if cell A2 is empty in VLoc Array sheets is blank
Here is the workbook in which the code is giving correct result without any error if I run the code from the VBA window. But when assigned to a button it is showing error.
Again, when I replay the code from the VBA window the second time, it is not deleting the sheets created by the code but facing an error again.
Re: Delete sheets if cell A2 is empty in VLoc Array sheets is blank
bebo when you are online the next time please see this. I tried editing the old code also with your variable but still did not work. Please help me to get the results from the button.
I am facing 2 issues with these codes. When I run the code from the modules individually, I am getting the correct expected result. But when I assign the module to a button it is displaying an error. The second issue is when I run the code the second time, as per the code, the code has to delete the previous created sheets and replace them with new sheets and that too is not working properly.
Re: Delete sheets if cell A2 is empty in VLoc Array sheets is blank
For good enough readers only (only the bad will have an issue !) …
According to your post #3 attachment an easy fast Excel basics VBA demonstration as a beginner starter
to paste only to the Sheet1 (B2B) worksheet class module :
PHP Code:
Sub Demo1()
Dim V, R&
Application.ScreenUpdating = False
[AFC1] = [S1]
With [A1].CurrentRegion
.AdvancedFilter 2, , [AFC1], True
With [AFC1].CurrentRegion: .Sort .Cells(1), 1, Header:=1: V = .Value: End With
For R = 2 To UBound(V)
If Evaluate("ISREF('" & V(R, 1) & "'!A1)") Then
Sheets(V(R, 1)).UsedRange.Clear
Else
Sheets.Add(, Sheets(Sheets.Count)).Name = V(R, 1)
.Rows(1).Copy
ActiveSheet.[A1].PasteSpecial 8
End If
[AFC2] = V(R, 1)
.AdvancedFilter 2, [AFC1:AFC2], Sheets(V(R, 1)).[A1]
Next
End With
[AFC1].CurrentRegion.Clear
Application.ScreenUpdating = True
End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Re: Delete sheets if cell A2 is empty in VLoc Array sheets is blank
Thanks Marc. But you have not understood my issue. To make it brief, I have 4 sheets like B2B to extract data and with multiple codes. Once I get a perfect code, I will create 3 more similar codes with B2C, HSN, E_Invoices sheets to get the extract of around 160 sheets depending on the values in column S, with different names. )B2B KA, B2C KA, HSN JA, e_INVOICES KA, etc.,). Your code produces only the state code without B2B and hence when I add 3 more modules all the sheets will be over written. Secondly, your code will work only if the cursor is in the B2B sheet whereas I will be assigning the module to a button. It will not work from the button as the Button is in the steps sheet.
The sheet I shared in post #11 is giving me the expected result when I press the button. The only problem is that when I want to re run the same code, it has to delete the created sheets first and then create new ones in its place. That is what is not working.
Re: Delete sheets if cell A2 is empty in VLoc Array sheets is blank
As I well understood what you explained in your initial post !
So as a starter you have to fit my demonstration for what you misexplained / forgot as guessing can't be coding …
Re: Delete sheets if cell A2 is empty in VLoc Array sheets is blank
Originally Posted by RAJESH SHAH
Secondly, your code will work only if the cursor is in the B2B sheet whereas I will be assigning the module to a button. It will not work from the button as the Button is in the steps sheet
Wrong as it well works on my side whatever the active sheet
so that just means you are a bad reader 'cause again you did not follow the plain text direction …
Re: Delete sheets if cell A2 is empty in VLoc Array sheets is blank
If you can correct this one code to delete the created data then my query will be solved. Right now, this code is not deleting the created sheets before creating the new sheets.
Re: Delete sheets if cell A2 is empty in VLoc Array sheets is blank
Like I demonstrated my efficient VBA procedure does very not need to delete any existing result worksheet just clearing it before …
So according to your code check at least what contains the array variable versus what is necessary.
And like demonstrated such array seems useless …
And on a smart designed workbook there is a third way without needing such array.
Again, next time you create a thread you must crystal clear explain your need in order there is nothin' to guess
and accordingly attach at least a well representative workbook with obviously the expected result,
so very not such the poor attachments you have made in your different posts !
Like in a restaurant you ordered a Coca and a burger but in fact you wanted a beer and a fish …
Last edited by Marc L; 03-31-2023 at 12:02 PM.
Reason: typo ...
Re: Delete sheets if cell A2 is empty in VLoc Array sheets is blank
Looks like my query is not understood. Forget all the above. I will ask in very simple terms.
I have say 120 sheets in the workbook. Some workbook have data below the headings in row 1 and some have only headings in the sheet with no data. I want to delete all the sheets in the workbook which has no data.
Something like If ws in workbook below cell A2 (A2:A10) is blank then delete sheet.
If anyone can understand this, please share the line of code which will do the above and I can add that at the end of the code. Hoepfully it will solve my problem.
Thanks
Re: Delete sheets if cell A2 is empty in VLoc Array sheets is blank
Marc.Thanks. Without assigning it to a button, With this code I am able to delete only one sheet at a time. It works only when the particular sheet is open. Is it possible for the code to cycle through each sheet and delete the sheets with the same criteria and that too from a button.
Re: Delete sheets if cell A2 is empty in VLoc Array sheets is blank
Originally Posted by RAJESH SHAH
bebo when you are online the next time please see this. I tried editing the old code also with your variable but still did not work. Please help me to get the results from the button.
I am facing 2 issues with these codes. When I run the code from the modules individually, I am getting the correct expected result. But when I assign the module to a button it is displaying an error. The second issue is when I run the code the second time, as per the code, the code has to delete the previous created sheets and replace them with new sheets and that too is not working properly.
Sorry for late reply. I just came back from weekend.
1- Why not work:
Because in your code, all range does not use "Sheets" then it is treated as activesheet.
For ex:
You assign this code to a button in sheet "Steps" then all the range without sheets mention are belong to sheet "Steps"
PHP Code:
lr = Cells(Rows.Count, 19).End(xlUp).Row
vLocArray = Range("S2:S" & lr).Value
equals
PHP Code:
lr = sheets("Steps").Cells(Rows.Count, 19).End(xlUp).Row
vLocArray = sheets("Steps").Range("S2:S" & lr).Value
If you want it refer to other sheet, B2B:
PHP Code:
lr = sheets("B2B").Cells(Rows.Count, 19).End(xlUp).Row
vLocArray = sheets("B2B").Range("S2:S" & lr).Value
2- In my code, I was using dictionary to get unique items from column S, since they are unique, no need to use "On error..."
Your code:
PHP Code:
For Each vLoc In vLocArray
...
Sheets("B2B").Copy After:=Sheets("B2B")
ActiveSheet.Name = "B2B " & vLoc
because vLock are repeated in vLocArray, it took more time to repeat code.
Re: Delete sheets if cell A2 is empty in VLoc Array sheets is blank
I really appreciate your effort and time to help me to solve my issue. Thanks bebo. I had to reconstruct the whole code with a separate module to delete all the created sheets and it is working as expected. You were right about adding Sheets ("Steps") in the defined variable. Thanks man.
LinkBack URL
About LinkBacks
Register To Reply
Re: Delete sheets if cell A2 is empty in VLoc Array sheets is blank
Re: Delete sheets if cell A2 is empty in VLoc Array sheets is blank
Originally Posted by RAJESH SHAH
Bookmarks