I'm pretty new to coding from scratch in VB, and I've got some code that should loop, but it doesn't. The idea eventually will be to download a spreadsheet, make a new sheet for every item in a range, and then filter for each of those items and put it in the right sheet. I have to code to create the sheets (lightly modified from something I got here)
Sub CreateSheets()
'Written by Barrie Davidson
For Each c In Sheets("FilterList").Range("b2:b74")
Sheets.Add
ActiveSheet.Name = Right(c.Value, 30)
Next c
End Sub
That's working well, so I've tried to modify it so that it will also delete sheets based on the same range. I've gotten it to delete the first item in the range, but then it stops.
For Each c In Sheets("FilterList").Range("b2:b74")
Application.DisplayAlerts = False
Worksheets(c.Value).Delete
ActiveSheet.Name = Right(c.Value, 30)
Next c
End Sub
By the way, I don't know what the ActiveSheet.Name = Right(c.Value, 30) line does, but I put it in since it was in the code I was modifying.
I have a macro which creates and names worksheets. I am making a button which also deletes the latest of these created worksheets, but doesn't delete other sheets. I am getting the error: Run-time error '424': Object Required. Here is my code for deleting the sheet:
[Code] .....
MSCount stores the highest "MS#" sheet.
The first line of the IF statement is where the error is.
I am attempting to make a range of cells' data be deleted if a cell outside of that range that had a name in it is no longer there. So if I have a list of names in cells A1:A6 and a range of data in cells D10:E20 that corresponds to a name in cell A2. If the name in A2 is deleted I would want the data in D10:E20 to be deleted.
I've got a spreadsheet with a 'data' sheet and I want to add and name additional sheets based on the contents of a range in the 'data' sheet - it could be 1 extra sheet, it could be 100, the range is open ended.
I found this very nice code on this site that changes the names of my sheets based on the first sheet, works great. What I want though is to have the sheets be unhidden when their name changes from the generic name "sheet1" to whatever else we call it. Can this code be manipulated to do that? I want the sheets whose names do not change to stay hidden, the others to unhide.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lCount As Long
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next If Not Intersect(Target, Range("A2:A11")) Is Nothing Then For lCount = 2 To 11 If Sheets(lCount).Name <> Me.Name Then Sheets(lCount).Name = Me.Cells(lCount, "A") End If Next lCount End If
i have a procedure that imports data & splits it by staff name, creating a separate sheet for each person
i wanted to ensure if the code was run twice it wouldn't throw up errors trying to name sheets with names already used, so used the following code to delete all sheets apart from the 2 sheets containing my base data (sheet names for these are IDs and base data)
Sub ClearUpSheets()
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In Worksheets If ws.Name <> "control sheet" Or ws.Name <> "IDs" Then ws.Delete Next ws
Application.DisplayAlerts = True End Sub
unfortunately, apart from a chart sheet (which i actually want deleted) this code deletes all sheets including the 2 i don't want deleted
I have a workbook wherein I have 7 sheets.Lets say they are called Tom, Peter, John, Sia, Mia, Tia and "Home Page". I have 2 buttons for Report 1 and 2 to which I want to assign the macros.I also have a table wherein I have defined which sheets I want to show. First Column of table has sheet names from A2:A6(Home Page,Tom, Peter, John, Sia, Mia, Tia). Column 2 has report 1 sheets - Home Page, Tom, John, Mia and Column 3 has report 2 sheet names- Home Page, Peter, Sia, Tia
What I want to do is, if I click on "Report 1" button, I only want to show sheets whose names are there in cells under report 1 so for report 1 it will be Home Page, Tom, John, Mia. For Report 2, it will be Home Page, Peter, Sia, Tia. Since I have many reports I want this to be one macro. Stepwise, here is what I want
1. Click on button for Report, macro should check which report I am referring to and select the range on basis of that. Report 1 = column B, if Report 2, Range is column C. 2. Basis the range I want sheets to show or hide.
I have a need to add or delete rows from a number of known sheet names using a table of variables on another sheet that tell me the start row of the sheet I need to go to and the number of rows I need to either add (ie copy rows and paste / insert these) or delete (delete rows).
There are multiple blocks of data I mey need to amend on each sheet and the values in my table of variables will change on each iteration (ie if I delete rows from the first block on a sheet, the start row for the 2nd block I need to amend will need to be updated in the table of variables before I can edit the 2nd block on that sheet).
I have been able to get the process to work for a single instance (ie one sheet and amendments to the first block of that sheet) but I can't figure out how to create the loop to elegantly move to the next set of variables and repeat the process for the 2nd, 3rd, 4th block etc on the first sheet and then move to the 2nd sheet to repeat the process etc.
Public Sub EditCurrentBlock() Dim rowcount As Integer Dim startrow As Integer Dim endrow As Integer Dim rowcountBal As Integer 'Dim selSheet As Worksheet (tried to use this to nominate the sheet variable but ' had problems so scrapped it)
'Reconfigure the GP Revenue block.................
I have a File with many sheets (40 - 60) and I'm trying to eliminate the sheets I don't want. The names of the sheets are all random, but in common they have a three letter code for a country.
The following is the code that I have -
Sub Deletesheets() Dim x As Integer Application.ScreenUpdating = 0 On Error Resume Next For x = 1 To Sheets.Count Sheets(x).Select
I'm hoping to save any sheet that has either CAN or USA the name and delete the rest.
Besides being inefficient, my code errors out because I've got my IF's and ELSE in the wrong place, but I can finagle it to work.
I’m would like to delete some sheets in my workbook using a macro. The problem is that I won’t know in advance the name of these sheets.
I was thinking using a code like the following one to delete the sheets but the problem is that Excel expect a sheet name at this part of the code since it is expecting the sheet name.
Sub Delete_Active_Diagram() Application.DisplayAlerts = False Dim i As Integer Dim strSheetName As String Dim blnFound As Boolean
strSheetName = "Average Sales Per Week" & "" & Sheets(i + 1).Range("C3") i = Sheets.Count
For j = 1 To i If Sheets(j).Name = strSheetName Then
blnFound = True Exit For End If Next If blnFound = False Then Exit Sub If blnFound = True Then strSheetName.Delete End Sub
Is there a way to cope this problem, or should i think about another way to do it. Like selecting each sheet I would like to delete and then delete the selected sheets.
By the way. All the sheets I try to delete a Charts Sheets. Maybe there is a way to delete all the charts in my workbook.
I have two sheets. One of them has a long list of numbers and the other one has a shorter list, all of which are also on the first sheet. How can I automatically delete the second sheets numbers from the first sheet?
Is it possible for a macro to delete any sheet that within a workbook that does not match a specific list of sheets. For example a workbook will always have Sheet1, Sheet2, Sheet3, Sheet4... but it can have many other sheets added to it. The names of the sheets can be completely different however the sheets that I want to keep will always have the same name.
Issue with array that's bolded The way it is set up is to rename the 3rd sheet to MyFileName and rename the last sheet to MyFileNameTwo. The file names will remain constant. They will always be the 3rd and last sheets, but the number in between will vary. Is there anyway to select the 3rd sheet through the last sheet to delete these? When I use the array it wants sheet names but those are based on multiple variables in other workbooks.
I have to load the data in Sheet 3 but before I do that I have to make sure that any organisation name that already exist in either Sheet 1 or Sheet 2 should be removed from Sheet 3.
Sheet 1 has 226 Organisation Names Sheet 2 has 62 Organisation Names Sheet 3 has 664 Organisation Names
I do not know how to write a Macro, nor d o I know how to write code. Can someone help with a formular or code? or is there another simple way to do it?
I've just set up a workbook full of pivot tables linked to an access database. The book will be used by several individuals interested in both the statistics presented in the tables and in the details underlying them. My concern is the accumulation of the extraneous sheets generated by drilling down to details. Is there any code I could use that would delete these sheets as soon as the user leaves them?
I've got a workbook that I'm currently designing and I've just realised that I need to delete a few sheets, paste in some new template sheets and rename them. The problem is, I've already completed my summary sheets and I don't want to have to redo the values. Essentially, I just want to (temporarily) turn off auto-updating of formulas when cells / sheets are changed/moved/deleted.
For practical purposes: I need to delete the sheet named "Jan", paste in a new sheet "MonthTemp", rename "MonthTemp" to "Jan" and have all my formulas not updated (e.g. still referencing "Jan" instead of "#REF!")
whenever you want to delete a sheet, excel would prompt you to ask you if you are sure you want to delete the sheet. im making a vba so that the sheets will delete upon workbook close, but i dont want to be prompted everytime to be sure to delete it when running the vba macro. Also, i would like to avoid being asked to save any changes to my workbook
Say i have a range A1:B3 and i define it as "Fruit", but then later decide i want to call it "food", or even just revert back to cell numbers. How would one go about doing this?
I'm trying to remove gif, jpg, and xls objects from a specific range and I can't seem to figure out how to do it. I can remove all objects from the sheet or only the gif and jpg files from a range. Here is the code I've been playing with:
Sub Clear_Sigs() Dim Sh As Shape With Worksheets("Sheet1") For Each Sh In .Shapes If Not Application.Intersect(Sh.TopLeftCell, .Range("A26:E32")) Is Nothing Then If Sh.Type = msoPicture Then Sh.Delete End If Next Sh End With End Sub
I know this code says to delete only pictures and I've tried tweeking it to do all objects but it doesn't work for just the range I want. Any help is appreciated.
I need to have my macro select a certain range of cells with a specific product code and delete them. What would be the code for it (product code 107). Here is what i have so far:
I have a column with dates (dd.mm.yy) and I have a column with names. Moreover, several dates and names are repeated. What is needed, is to delete all the rows in which the difference between dates is smaller than 1825 days (5years) for the same name. (Namely, if I have three rows 01.01.1996 - "A"; 01.01.2002 - "A" ; 01.01.2005 - "A" I want all the rows with "A" to be deleted)
I run a daily report that contains material usage and I copy that into a workbook designed for Data Analysis. I only keep a years worth of usage in the workbook so I need part of my macro to look at todays date and delete anything that is older than a year.
