Deleting Workbook Based On Criteria In Active Worksheet?
Aug 16, 2012I am trying to delete a workbook, yes the whole workbook, if cell A1 is blank.
View 2 Replies
ADVERTISEMENT
Deleting Multiple Worksheets And Saving Active Worksheet
Aug 3, 2007I am trying to find a easy way to delete multiple worksheets in a workbook and then saving the workbook based on the tab name of the worksheet.
View 9 Replies View RelatedTransfer Value From Active Worksheet In One Workbook To Another Workbook?
May 9, 2014I am trying to transfer a value from the active worksheet in one workbook to another workbook. I keep getting a subscript out of range error on the line that uses activeworksheet command when I run my code.
View 3 Replies View RelatedDeleting Rows Based On Two Criteria?
Jan 30, 2013I 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)
View 5 Replies View RelatedDeleting Rows Based On Criteria
May 29, 2012I have this excel file where I every day have over 10.000 rows. I have 2 sheets, one called "Data" and once called "Include list".
The Data sheet contains a list of all of our customers and their customer IDs. The Include list sheet should contain the Customer ids which I want to keep in the Data sheet.
So what I have done is to loop through the Data sheet. If you are in the Include list sheet you should not be deleted fromt he Data sheet. If you are not then the row should be deleted.
I have actually done this and it works but the problem is it take a lot of time to run. I tested in earlier today and I had to break it after 20 minutes which is way too long for our users to wait.
This is the code I wrote:
Sub Include()
Dim FindString As String
Dim Rng As Range
Dim RowNr As String
Dim Lookup_x As String
Application.ScreenUpdating = False
[Code] ........
So my question is, is there any other way to speed this process up? The ScreenUpdating part I have tried but it didnt really speed it up as much.
Deleting Rows Based On Two Criteria?
Apr 20, 2013I have a very large spreadsheet (>10,000 rows) of data. I did Conditional Formatting based on duplicate values for the serial numbers column (B) and then sorted to "Put Selected Cell Color on top". Next I did a sort by the Last Scan Date column M (Oldest to New). The date/time format appears as follows
I used the following macro to delete rows with duplicate serial numbers but retain the row that has the newest time stamp. When I run my macro it's doing the opposite where it deletes rows with the newest time stamp and retains the oldest time stamp.
Code:
Sub Test()
'for Macro to Delete Duplicate Rows and Retain Unique Value
Dim LR As Long
[Code].....
Deleting Columns Based On Certain Criteria
Jul 6, 2014I am having trouble coming up with an algorithm for deleting columns, based on a certain set of criteria. Heres the issue, all the columns have a "1" the top of column . If that column has a cell number that's greater than or equal to 0.90 or less than or equal to -0.90 then that column gets deleted, if it just has a "1" then the row doesn't get deleted. Very complicated set of criteria.
View 6 Replies View RelatedDeleting And Adding Rows Based On Date Criteria
May 21, 2006ColumnA in my data base contains dates. How can I delete the entire row if the date is before today's date and add a new date at the end of the range to replace the deleted row.
Make Array Reference Cells On Another Worksheet Of Active Workbook
Feb 27, 2014My company has complicated time sheets because we have several tasks that are billed differently to different people. Once a month we have to sit down and compile everything from several forms and so forth. I have created a worksheet that pulls all the numbers together so that they can simply be copied and pasted into our reports. The hope was to simply copy this worksheet into a time sheet workbook and it will pull out all of the correct numbers. Although all the time sheet workbooks are set up the same way, whenever I copy the file into another time sheet workbook the program keeps its references from the workbook it was in. Make sense? is there a formula that I can insert into an array to tell it to pull the information from the worksheet with the same name, but in the active workbook?
here is one of the equations I am working with:
{=TRANSPOSE('1st week'!A10:L48)}
so it would look something like this maybe
{=TRANSPOSE('[active workbook]1st week'!A10:L48)} but this doesn't work of course.
Insert A Value Into The Active Cell In This Workbook Based On The Value Of A Checkbox In The Other Spreadsheet
Jul 25, 2007I have 2 workbooks open, one called "Temp" and the other called "CRM 2"
My active workbook is "Temp". I am trying to insert a value into the active cell in this workbook, based upon the value of a checkbox in the other spreadsheet. Here's my
If Windows("CRM 2.xls").SelectedSheets.CheckBox6.Value = True Then Selection.Value = "By Phone"
I've tried variations on this such as:
If Windows("CRM 2.xls").ActiveSheet.CheckBox6.Value = True Then Selection.Value = "By Phone"
Macro: Create Directory Based On Active Workbook And Save To It
Sep 8, 2006Read “My Documents” Path And Use Result
Problem:
Note:
Typical user OS will be Windows XP Pro / Win 2K
Excel version : 97 / 2002 / 2003
1. Corporate network security settings will only allow directory/subdirectory creation in the “My Documents” section of customers individual computers.
2. Per customer request, VBA application needs to save extracted files for future use.
3. I can specify an initial “My Documents” subdirectory be made and the VBA application file be loaded/copied into that location – i.e. – “My DocumentsCat”.
4. When VBA application is opened from that specified directory, (first time), the application needs to make an additional subdirectory tree to save future files. I can read the opened from location via VBA with the following:
Dim filepath As String
filepath = ThisWorkbook.Path
As an example – this code would produce a string definition of “filepath” – such as the following:............................
Auto Copy Part Of Row In A Worksheet To Another Worksheet Based On Criteria
Dec 15, 2009I'm a novice Excel 2007 user and appreciate all the help I can get. I have a workbook with monthly worksheets in it. When a certain data Type is selected from a drop down menu in that monthly worksheet than I would like to have it automatically enter specific data (Name, Date, Eval, Type) copied to another worksheet (CC) in the same workbook. I have been manually entering the data so far. Another thing, some of the data will be entered into the Monthly worksheets and some will only be manually entered into the CC worksheet so it would need to accomodate both methods of data entry. Please let me know if I need to clarify. I have attached the workbood, too.
View 11 Replies View RelatedUpdating Contents Of One Worksheet Based On Criteria In Another Worksheet (2)
Mar 10, 2009OK, two files attached in the zipfile, pricelist-half.xls and pricelist-full.xls
The background is that i exported a file from our warehouse system so we could update prices and re-import it. That is the pricelist-half.xls
After our staff had spent a few days working on it, it came to light that (as the name suggests!) its only about half the products that should have been exported that are on the list.
Hence the second file, pricelist-full.xls which as its name suggests is the full
product list.
What I need to happen, to make this as painless as possible, is somehow for the items/rows that are on the full list, but not on the half list to be inserted to the half list but also have a yellow background for their rows so they stand out clearly.
Populating One Worksheet With Data From Other Worksheet Based On Criteria?
May 22, 2013I have two sheets in one workbook; one has a list of names in column A (136 of them) and another is a master list with all the names of people who work for the company (over 2000). Sheet 1 and Sheet 2 respectively. I need to populate columns B-E in Sheet 1 with the data in columns B-E in Sheet 2, but only for the names that match in column A. I've tried applying filters but can't get to the data I need for some reason. The names are formatted the same way in both sheets (Last, First). In Sheet 2 the names are links but in Sheet 1 they aren't; not sure if that's important.
View 7 Replies View RelatedDeleting Row At Active Cell
Nov 8, 2013Creating macros in excel. I am looking for a way to delete a row at the active cell. So far this code does the job for me:
Code:
Sub slet()
ActiveSheet.Unprotect
ActiveCell.EntireRow.Delete
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True
End Sub
But I want to make it impossible to use this code on the first 4 rows.
Deleting An Active Control Along With The Cell
Jul 10, 2009I have created a spreadsheet that uses checkboxes. When deleting a particular row that is no longer needed, it leaves the check boxes. Is there a way to link them to the cell (which contain formulas) so when the row is deleted, I don't have to delete each individual check box.
View 9 Replies View RelatedVBA To Open Workbook And Sum Based On Criteria?
Mar 4, 2014I have a lot of data that I want to put into another workbook so as to free up space and make the workbook efficient, however, I do not know how to open a workbook in VBA.
Once this workbook is open, I then want to sum the data based on set criteria.
Autofilter Based On Criteria From Other Workbook
May 24, 2008I am looking for a way to search through several workbooks for a row containing the user's inputted data. I was thinking I could Autofilter each workbook but having trouble getting the code to reference a cell value in another workbook.
Sub Autofilter()
Windows("file.xls").Activate
With "Sheet1"
.AutoFilterMode = True
.Range("A1:EV1").Autofilter Field:=1, Criteria1:"Workbooks("reference.xls"). Sheets(1).Cells(A2)"
End With
End Sub
I would like to have file.xls Autofiltered by the value in cell A2 which is in workbook reference.xls. Am I going about this the wrong way? Auto Merged Post Until 24 Hrs Passes;Here is the code repaired so it doesn't through the syntax error:
Sub Autofilter()
Windows("file.xls").Activate
With "Sheet1"
.AutoFilterMode = True
.Range("A1:EV1").Autofilter Field:=1, Criteria1:=Workbooks("reference.xls").Worksheets("Sheet1").Range(A2).Value
End With
End Sub
But it still isn't working. Something is causing trouble at the With statement...
Transpose From One Worksheet To Another Based On Several Criteria
Feb 10, 2013I have several worksheets in one workbook entitled, "A", "B", "C", "D", and "E". In A1 of each worksheet is the title of the respective worksheet. In A3 is the word "Day", while in rows A4:A34 are the numbers 1 through 31 (corresponding to days of the month), in the date format "1/1/2012", etc. In row 3, columns B:F contain dates of the first day of a respective, consecutive month and year spanning ther period 1/1/2012 thhrough 3/31/2013. For example in B3, the date 1/1/2012 appears, while in C3, 2/1/2012 appears, etc. all the way to P3 the date 3/1/2013 apppears. In the "body" of the table (B4:F34) is daily data corresponding to the respective day, month and year.
What I would like to do is "transpose" this data to a "Master" worksheet set up as follows: in row B1:F1 would contain the names of the individual worksheets (A, B, C, D, and E). Range A2:A457 would contain daily dates beginning with 1/1/2012 and ending with 3/31/2013. In the "body" of this worksheet, i. e., B2:F457 would be the daily data corresponding to the respective day, month, year and worksheet.
Populate Worksheet From Another Based On Criteria
Dec 18, 2006I have a master list of people. (call it master) I have 1 other worksheet that should contain everyone aged 15 and up (call it must progress). One of the columns on the master show's their ages. I was hoping excel would automatically populate the sheet based on the age from the master list. So, for example. On the Master list, Joe Smith is 17. I would like Each cell in the Joe Smith Row to automatically copy itself to the must progress worksheet because his age is over 15. The formula would be based off the age on the master.
View 2 Replies View RelatedCopy Rows From One Workbook To Another Based On Criteria
Aug 4, 2009I have been struggling to setup these two workbooks for a bit now, and I can't for the life of me figure out a formula to do what I need to do. Essentially, I have one workbook that contains a list of purchase records for my company, sortable by Date, Vendor, Price, etc. and one workbook that has a sheet for every vendor. What I need is a formula that will search column B for a vendor, Allied Waste for example, and transfer all the information within the rows for every instance that vendor is found to the new workbook.
This is basically just a way where I can input information once in one workbook, where the sheets are divided by month, and the info will automatically transfer to another workbook, where the sheets are divided by vendor.
Hide Workbook Tabs Based On Certain Criteria
Jun 10, 2014I have tabs within my workbook that are pulling from other tabs in the same workbook. There are 30-40 tabs, and only 10-15 are used at a time. Is there a way to hide tabs that are not being used? If I do a calculation in A1 and it's greater than 0, I'd like the tab that is pulling from A1 to be visible. If A1 is 0, then I'd like to hide it.
View 14 Replies View RelatedLookup Data In Another Workbook Based On Criteria
Sep 8, 2006I have a work book with several work sheets, what I'm trying to do or find a a method of coping data from one sheet to another provided the data in any given cell matches a specific criteria. Example below:
Work sheet 1 is the primary sheet in the work book on this sheet I have a cell with a text value (Bob). Ok on the next work sheet 2 I need to search for a cell that contains (Bob). When or if it is found I need information from other cells in that row (where Bob was found) copied to work sheet 1 in a specified row and colum. If it is not found a negative value can be placed in the specified cells.
Update One Workbook From Another Based On Unique Criteria
Mar 11, 2008I have two workbooks. I use excel 2003
I need to update the master workbook with data from the invoices workbook using the following criteria.
In the master workbook column C has unique job numbers.
In the invoice workbook column F has unique job numbers.
Both worksheets are the first worksheet in both workbooks.
If the job number in the invoice workbook is already in the master workbook do nothing.
If the job number is in the master workbook but not the invoice workbook then delete the entire row from the master workbook.
If the job number is not in the master workbook add it to the master workbook.
To complicate matters the master workbook is shared. It is my understanding that shared workbooks can't contain the macros needed ? It would be preferable that the master sheet had them but if not ...
Copy Data To New Workbook Based On Criteria
Apr 4, 2008I have a workbook that is used to track job estimates.
What I would like to have is a macro that would search through column N on the original file and if any of the cells have "No RFE" in them - generate a new workbook with only this list that would include any data from columns C-U copied from the original file.
I've been seaching the forum, but so far have had little luck finding something that suits my needs.
Cut Data Based Upon Criteria And Move It To New Worksheet
Jan 10, 2012Error in the code, where I'm trying to copy the data (based on criteria) from one worksheet to another, then delete the data from the first worksheet. In the code (I have complied from this message board) I get an 1004 error "Application-defined or Object-defined error".
Code:
Option Explicit
Sub CopyALColKYes()
Dim NR As Long, c As Range, firstaddress As String
Application.ScreenUpdating = False
NR = Sheets("Completed-Expired").Cells(Rows.Count, 13).End(xlUp).Row + 1
[Code] .........
Copy Row Data To Another Worksheet Based On Criteria
Sep 28, 2006Data exists in a workbook's sheet name "0293" in columns A, B, C, & D. If a row's value in column B exceeds zero, the entire row & formatting needs to be added to the bottom of data in a sheet called "Comprehensive" of the same workbook and put 0293 in column E of the same row. What would be the simplest VBA code to copy sheet 0293's data & add it to the "Comprehensive" sheet with the sheet name in column E of the same row?
View 2 Replies View RelatedLink Row Of Data From One Worksheet To Another Based On Column Criteria
Oct 11, 2012I have excel sheet with 4 worksheets tabs(Master, Won, Lost, No Bid). All data entered on Master. I want each row to automatically link/appear on 2nd, 3rd or 4th worksheet tab based on info in column Q (Result) which would be either 'Pending' - in which case it would stay on Master, 'Won' - would stay and copy to Won worksheet, 'Lost' - would stay andcopy to Lost worksheet, 'No Bid' - would stay and copy to No Bid worksheet. All rows would always show up on Master worksheet.
View 1 Replies View RelatedMoving Rows To Anoter Worksheet Based On Certain Criteria..
Feb 11, 2009I have an excel workbook with 5 sheets, the first called 'data drop sheet' & the others called 'Louise', 'Thor', 'Peter' & 'Steve'. In the data drop sheet, the first row is header data and underneath (A2 onwards) are rows of data that I have cut & pasted in. The first column (A) of this data will be the person's name, e.g. Louise Wilson.
I want to be able to move the rows for each person to their respective sheets, starting from row 2 as the first is a header. So, for example, if 'data drop sheet' cell A2 says Steve Brady, I want it to copy the whole line to sheet 'Steve' row 2 etc.
VBA Code To Copy Rows To A New Worksheet Based On Criteria
Dec 17, 2008I need a macro that will search data from a range of cells in one column for multiple criterias and them copy the entire rows to a new worksheet.
Example I have a list of group names
Network
Telcom
Help Desk
BA
Network
I only want to choose all Network and Telcom rows copy to another worksheet.
