I am working on a workbook where I have a huge list of hotels for a specific city; 'Hotels List'. I have a seperate workbook where I have a Black List of hotels; 'Black List'.
I would like a macro which checks the Black List and deletes the row of all of the hotels in 'Black List' from 'Hotels List' so that I am just left with good hotels and none of the hotels in my black list appear in my hotel list.
Is this possible?
Private Sub UserForm_Initialize()
With AddDelete
ListBox1.RowSource = "Phone"
End With
End Sub
(Userform is called AddDelete)
I am trying to remove an item from the list box, by way of 'delete' button (command button) and cannot seem to remove from the list box and the spreadsheet
The list is spread over two columns and I can't seem to find a way of doing it, surely there is something simple I'm missing here..?
How is it possible to delete a workbook from a userform in an open workbook?
View 6 Replies View RelatedI am using a userform to pull some data from a form that many different workers fill out. I have a button on the userform that automatically saves certain cell values in the form to a different workbook when they hit this button (they hit it every time after they are done filling it out)....The problem is that if they need to go back and make changes to the form they originally created, the workbook that has all the data will now have two entries for the same form number (when they hit the button it also saves the workbook as NCR####) but I only want the most recent one..
So what I need to make it so that if two numbers in column C (C2 and on) of workbook "NCR_Data.xls" match that this macro deletes the one furthest from the top (my code places newest entries at the top, in row 2).
I have a large keyword phrase list.
In a sheet called "AllKWs".
What I would like to be able to do is find all Country and town names in that list and delete the names only in the phrase, not the whole row.
Several points though.
Can the macro (As I will assign a text button to it).
Only find and delete these words if they are either at the start or end of a phrase?
4 example phrases; ....
I am a tax auditor, and I am working with excel and I have two lists. One is a complete list of items, both taxable and non-taxable, the other is a list of just non-taxable. I highlighted the non-taxable sales and copied and pasted them under the main list, and I want to delete all the non-taxable items. Basically, the only duplicates in my list are non-taxable, and I need to delete ALL of them.
View 9 Replies View RelatedThe following code for a custom menu is used in a workbook which has two sheets with
embedded charts, two chart sheets and several sheets for calculations
and information
In This Workbook
Private Sub Workbook_Activate() 'Changed Activate to Open
Run "AddMenus"
End Sub
and
Private Sub Workbook_Deactivate() 'Changed Deactivate to Close
Run "DeleteMenu"
End Sub
and
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Run "DeleteMenu"
End Sub
I want to create a macro that will find a text string called "Season Average" in the entire workbook and delete a range of cells above it 4 cells longs starting right above the text and then going left.
Example is if "Season Average" is in cell D11, than I want the cells D10, C10, B10 & A10 Deleted and the cells to shift up
This happens throughout the entire workbook and multiple times on each work sheet in different spots not consistently same cells.
I am trying to delete a workbook, yes the whole workbook, if cell A1 is blank.
View 2 Replies View RelatedI'm new to this forum and to VBA
First-off, I'm using Excel 2003 SP3.
The setup: A software application I regularly use produces csv data files (in this case approx 300 files). These I have converted to xls format using a separate macro. The files are in one folder and named as follows eg
G1 18800.xls
G1 18802.xls
G2 18975.xls
G3 19881.xls
G3 19990.xls
G5 19990.xls
etc
The files contain the following data:
DATE TIME VALUE STEPS EXCL ACCEL
01-Oct-03 08:00 0 0 FALSE 0
01-Oct-03 08:01 0 0 FALSE 0
01-Oct-03 08:02 0 0 FALSE 0
01-Oct-03 08:03 0 0 FALSE 0
01-Oct-03 08:04 757 20 FALSE 0.18
01-Oct-03 08:05 1714 32 FALSE 0.44
01-Oct-03 08:06 1524 32 FALSE 0.39
01-Oct-03 08:07 1665 45 FALSE 0.47
01-Oct-03 08:08 1644 42 FALSE 0.46
01-Oct-03 08:09 263 8 FALSE 0.06
etc
I then created a macro using code I sourced from the internet, and included some addtional commands (filename, copy/paste). See below. This macro opens the all the xls data files in the folder and copies the relevant data to an analysis workbook [Analysis sheet, Results sheet, Master sheet] ie the data is copied from the data file and pasted into the Analysis worksheet. Then the results are copied from the Results sheet to the Master sheet. So far so everything works.
My problem is this: I'm stumped at how to delete rows from the xls data files before running the analysis workbook and macro. In other words, after converting the csv files to xls format I need to clean the xls data files.
I have a separate xls file with criteria data in two columns:
FileName Date
G1 18800 06-Oct-03
G1 18801 02-Oct-03
G1 18801 03-Oct-03
G1 18801 05-Oct-03
G2 18795 14-Oct-03
G2 18795 15-Oct-03
G2 18795 16-Oct-03
G2 18795 17-Oct-03
etc
The rows in a particular data file, with dates that are not present in the Criteria workbook must be deleted. This to be done for every data file in the folder. Also, I need to exclude rows for specific time periods eg 12H00AM - 04H00AM from all the files irrespective of date.
the code needed [should a separate macro be run or can code be placed within the present macro?]. I've tried looking for something similar on the internet, but my requirements seem too customized to be able to adapt the code that I found. And of course this is waaaay above my present skill level!
Option Explicit
Sub CopyPaste()
'This code opens up data files (xls) in a specified folder and copies data A1:G17281 to an Analysis workbook (Analysis sheet).
'The data from the Results sheet is then copied to the Master sheet.
Dim wkbDest As Workbook
Dim wksDest As Worksheet
Dim wkbSource As Workbook
Dim wksSource As Worksheet
Dim MyPath As String
Dim MyFile As String
[Code]...
if you can remove the word total from each cell without deleting the numbers after you copy from a subtotal list?
View 9 Replies View RelatedI have an excel work book with 6 tabs. I would like to have Excel move an entire row from one tab to another tab (removing the row and inserting it in the other tab). I.e. Example I have a tab with items that are marked as "Open Actions" so if I were to change the drop down to close. Excel would move that entire row of actions to the tab with the "closed actions" and insert into the next available row. Now if someone were to come back at a later date say no it should be reopened than I would change the drop down to open and excel would move that row back to the open actions tab into next available row. I tried a PIVOT table and no good I played with few macro and not.
View 5 Replies View RelatedI wanted to write a function that can search for the whole Workbook and give me the list of sheets that has the requirement/ name specified the first sheet. I have many sheets to search for and i cannot slist down each and every sheet using a Vlookup or countif function.
View 6 Replies View RelatedI have around 40 to 50 worksheets in a workbook. Is there a quick way of listing the names of all the worksheets in a single worksheet instead of typing the sheet names one by one?
View 2 Replies View RelatedI have a list of names in column A with monthly budget figs next to them in columns B,C,D etc. At the bottom of the list there is a total spend summary on line 7. This spreadsheet is then pasted with links to a separate worksheet (see example)
If an additional name is added to the list in Row 7 in the original list, the total is now in row 8 - how do I ensure that the linked sheetl picks up this addition?
(I have a project where I am drawing data from numerous worksheets. I have paste linked them as tabs to a summary worksheet so that funtions such as SUMIF can update when the file is closed or open)
I have two workbooks.
Workbook1 contains validated list which is defined by a Range name and also its data is pulled from workbook2. I am unable to change the value of the validated list from drop down when i close workbook2, infact the validation drop down list do not drop at all so that can select values from it. But if i open the workbook2 i am able to change the values of the validation list.
I want the dropdown list to be active when i close the second workbook.
I have this code that works in Word but when I try it in Excel I get a "runtime error 438/object does not support this property or method" on this line: "For Each ish In Sheets("SDF & ER").InlineShapes"
Code:
Sub LoadPEGC()
Dim ish As InlineShape
Dim cbo As MSForms.ComboBox
Dim xlapp As Object
Dim xlbk As Object
xlsfile = "C:fileserverusersTPCentral_TooPE_GC_list.xls"
[Code] ......
Is it possible to have a list from a different workbook as the source list for a validation dropdown on a cell? If so, what is the syntax.
View 2 Replies View RelatedIt can be done using named ranges. Name the range of the source list and set the validation to List =name. Sheet location does not matter.
I can get it to work using Indirect - but this puts a limitation on the process that the source must be open. Mike, perhaps you could explain more fully how you are achieving this?
I know it's possible to list option reference in another worksheet in relation to data validation, but is it also possible to reference to a complete different workbook. If so, what's the procedure? I have many workbooks that reference the same list options.
View 7 Replies View RelatedI am trying to implement this formula in my file: = SUMPRODUCT(SUMIF(INDIRECT("'"&$H$1:$H$20&"'!A1:A100"),"criteria1",INDIRECT("'"&$H$1:$H$20&"'!B1:B100"))). I have over 35 sheets in this file with different names. Is there a way to export all the sheet names in list format so that I don't have to go back and forth in the file and type the names?
View 2 Replies View RelatedI have been working on creating a dependent drop down list and have been able to create lists (Insert > Name > Define) and it has worked for all the lists except 2. It will not allow me to create a list titled C or R. I get a pop up stating "that is not a valid name". Why is that?
I have a list for each letter of the alphabet except the letters C & R. No matter what I seem to do I just keep getting the pop up window stating that it is not a valid name. I also tried closing the workbook and restarting the pc and then trying but the same thing keeps happening.Thanks!
I have two coworkers who input a list of times spent throughout the week into a basic 5x5 grid (daysof the week x 5 employees) into their own seperate workbooks. I then take those workbooks and copy and paste them on to my own master work book. Is there any way to get their data to auto populate on to my spreadsheet?
View 3 Replies View RelatedI have a database which shows members details with a colour system for varying levels of payment. I want to copy the membership number title and name from this d/base to another worksheet in the same w/book so I can print it in a4 size and select the page breaks. I think this is achieved by some thing called "concactia"??
View 1 Replies View RelatedI have two workbooks. One is called the Extraction Sample, which is attached, and the second is the Sorted Results workbook.
The Extraction Sample workbook contains a master sheet with data in a chart, which gets updated periodically. The Sorted Results workbook contains a list of specific extracted results from the Extraction Sample workbook.
The formula I am trying to create for the Sorted Results workbook needs to do two things:
1. Extract only the rows from the master sheet in the Extraction Sample workbook which contain the word 'Thing' in column D.
2. List the extracted rows on the Sorted Results workbook in numerical order using the data from Column B.
Is there a way to auto name sheets within a workbook by creating a list. or by a different way.
Each list will have a person name.
Is it possible to have a workbook (1) with a list on it and another workbook (2) which has a dropdown list on it that shows the list on the 1st workbook. But when a new entry is made on workbook (2) it is added to the list on workbook(1)
View 8 Replies View RelatedHow do you automatically list all the worksheets within a workbook? I did some searching and decided to use the following code:
Sub ListWorksheets()
'Lists all the sheet names in the workbook onto the _
first sheet (starting at cell a4)
Dim i As Integer
For i = 1 To Worksheets.Count
Worksheets(1).Cells(i, 1) = Worksheets(i).Name
Next i
End Sub
It would be very useful if I could control the row in which the data is returned. "starting at cell [U]a4[U] seems to be that part of the command, but it still returns in the 1st row.
i just want to generate a list validation where source resides in another workbook?
i have used named range concept ?but not working can anyone help..
Need a way to generate a list (i.e. array) of tabs available in the current spreadsheet?
View 3 Replies View Related