i would like to know whether a worksheet exists or not... say, the worksheet name is sheet1, i need to check whether the worksheet exists... the point is, i need to delete the worksheet if it exists and execute a code... if the worksheet doesn't exist also, i need to execute the code... so im getting a little confused with this... i need something like this...
if sheet1_exists then delete the sheet end if my_code.......
I got a pivot table that when selected will give me a value (actually its an ID number) I want in H3. I then got a list of values outside of the pivot table in column J. If H3 matched any of the values in column J, I need a “Warning”, if not “OK”. I have been using the formula =IF(H3=J3,"OK","WARNING") to match selected cells (H3 with J3) but cant get it to work to look down the column list. The OK or Warning is in cell F34 out of arms way!
I have a huge database consisting of zipped file names which i created by importing the directory listing into excel. The zipped files contain two files each an mp3 and a correspondent cdg file (karaoke files).
I continue to add new disks (as I acquire them) to my collection. the trouble is that new disks have some songs already in my collection and I do not want to waste space in hard drive with duplicates. The existing data is arranged in a worksheet by columns as follows:
Discnumber | track number| artist | song name | path | complete file name
what I would like to do is to check the ** listing after import it to excel in the same format (i can do this part ) and then check it against the 10,000 plus entries that i have if the artist-song name combination already exists in the data. If so return the row in a separate worksheet or pivot table so that i can check it. this way I would only save the songs I do not have in my hard drive thus avoiding duplicates and saving precious space.
I have an array that opens a workbook containing close to 100 worksheets, and copies specific sheet names into there own individual workbooks. The problem I have now, is that I assume the worksheet exists in the workbook, but often times it does not Is there a way to add some sort of "catch" that will 1st verify the worksheet exists instead of my code crashing?
I am writing a macro for a my team. I will distribute the .bas file then have them run it. I don't know what their individual sheets in their workbook are called so I need to ask them. Grab various columns from that sheet, check if they have a sheet called data import, then put in the columns in order in data import.
Code: Private Sub checkForSheet() Dim sh On Error Resume Next sh = Worksheets("Data Import").Name If Not Err.Number = 0 Then Sheets.Add.Name = "Data Import" On Error GoTo 0 End Sub
to check if Data import is made
Code: Sub prepareData() Dim SCMsheet As String, TSE As String Dim DISh As Worksheets SCMsheet = InputBox("Enter your SCM Sheet name in entirety.") If SCMsheet vbNullString Then
But it doesnt stop scmsheet is not in the workbook, and the values arent put in.
I have written a routine that automatically saves the file to a specified folder as the name of a cell from one of the worksheets. I am trying to use the following code to see if that filename already exists and stop the routine if it does. If I manually enter an existing filename the routine works well, however I cannot get it to recognise the automatically created filename, here is my .....
I am writing a macro which first needs to look and see if a certain worksheet exists. If it doesn't then I want it to create it but if it does I first want it to delete the sheet then create a blank one with the same name. At trhe moment I have this but it is scrappy and doesn't work very well:
On Error Goto AddSheet If Sheets("MONEYSHEET").Activate Then Goto Data
I am looking to see if a directory exists, and if it does, to save a file in it, otherwise create it, then save the file. My IsFileThere function works fine on files, but does not detect directories. The len(Dir(Filename)) statement always returns a 0, with the Dir statement always simply populating with "". It works fine on filenames - so can anyone tell me why it does not pick up directories? Shouldn't the Dir function on a directory name return "."? I have excerpted some of my code to include here: Global Const StoredJobsSubdirectory = "Jobs"
Dim AppPath As String: AppPath = ActiveWorkbook.Path 'other code in here - activeworkbook changed, hence previous line If IsFileThere(AppPath & "" & StoredJobsSubdirectory) Then ActiveWorkbook.SaveAs FileName:=AppPath & "" & StoredJobsSubdirectory & _ "" & NameString & ".xls" Else MkDir AppPath & "" & StoredJobsSubdirectory ActiveWorkbook.SaveAs FileName:=AppPath & "" & StoredJobsSubdirectory & _ "" & NameString & ".xls" End If............................
I know this is my third thread, but I have made sure that I have trawled through other threads before posting. I have created some VBA to collect data from multiple workbooks and paste them into one workbook, Basically the copy and paste script runs according to how many tabs are in the summary workbook because each tab ( sheet) is essentially a condensed version of a workbook. Unfortunately some workbooks I am getting the info from don't have the right data that the copy and paste program is looking for or sometimes the document doesn't exist, is there anyway of telling my VBA to pass this tab if the document doesn't exist instead of throwing up a VBA debug error?
I'm looking to use the value from a series of dropdowns (made via data validation lists).
Cell AM5 is a dropdown of named ranges made form a seperate sheet Cell AO5 is a dropdown that uses '=INDIRECT(AM5) to lookup the values in the named range.
I need cell AO5's value to be used to filter rows in the current worksheet. The current problem is when i use the first dropdown in AM5 it still displays the last value, untill i use the dropdown to select a new one. This value typically will not be found and i do not want my code to execute in these cases.
I'm creating a macro to select, modify dimensions and place shapes on a excel sheet. (I'm talking about pictures insered and stocked in a specific sheet of my workbook) When the users insert a new picture he has to set a name for each of them. A combobox contain the choices, when an item is selected, the macro identify, size and place the corresponding picture.
But I have a bug if a shape doesn't exist when I try to select it :
Sheets("fiche de controle").Select 'select the sheet with the pictures ActiveSheet.Shapes(Item_old).Select 'select the shapes "Item_Old" Item_Old is a variable corresponding to the picture name.
If the user made a typo, I have a bug. Someone knows how to test if the shapes exist to display a meesage if not ? Or somethig to avoid this kind of bug ?
I'm trying to check if a named range "ActiveCells" exists before deleting it in VBA but can't seem to get the syntax right, I have tried: If Range("Activecells") Is Nothing Then Resume Next Else: ActiveWorkbook.Names("ActiveCells").Delete
If ActiveWorkbook.Names("Activecells") Is Nothing Then Resume Next Else: ActiveWorkbook.Names("ActiveCells").Delete
Can anyone point me in the right direction? I have tried searching but I can only find threads about checking for named objects or about using the toolbars to add/delete named ranges.
I am trying to write some code that will copy the worksheets from one workbook (wkbSource) to another (wkbTarget), but I need it to maker sure the worksheets being copied from wkbSource don't already exist in wkbTarget. If they do exist, it just skips and moves to the next worksheet. Here is the code I have already, I thought that by adding the On Error Resume Next to the code it would just skip it, but for some reason it is still copying the first duplicate workbook, then it skips.
Code: Dim wkbSource As Workbook Dim wkbTarget As Workbook Dim WorkbookName As String WorkbookName = ThisWorkbook.Name