Conditionally Deleting Lines On Multiple Worksheets
Jun 17, 2014
I need to delete line if 2 conditions are meet.
If col A is blank and col D or E is blank then I want the line deleted for lines 1no more than 100 The option of D or E has me stymied I end up with it not working right. This is what I had tried
Dim lastrow As Long, c As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For c = 1 To lastrow
If Range("a" & c).Value " " and Range("d" & c). Value " " or Range("e" & c)Then ActiveSheet.HPageBreaks.delete
Next c
But doesn't work
I also need macro to run multiple worksheets which varies anywhere from 12 sheets to 50 sheets.
I have workbook that expands or shrinks in number of worksheets each time and I need to gather information from each worksheet to compile a "total" spreadsheet. The location of the cells to be copied in each worksheet vary however it is always in the same column. Managed to find a macro that will collect the information if it is located in the same cell, across all worksheets but did not manage to find something that will conditionaly copy.
Need code, that will search based on text that will be found in the worksheets and then copy the values from the column next to it (same row) to the "total" worksheet. If any of the text that is to be searched in the worksheet does not exist, then it should leave the cell "blank" in the "total". I have attached a workbook of with the example of the worksheets that I have and the result that I want to have at the end, in the "total" worksheet.
I am searching for a formula or simple macro to select text data from column named "SM NAME" to corresponding worksheet. I need this to populate as many worksheets as there are SM Names. ( there are usually 10 SMs)
For Example in the Master Data there are Several SM names listed. I need to extract the "ID" and "Agent" columns in the Master and populate into the workshhet with the Approriate name tab. The Master list changes regularly.
My spreadsheet involved plenty of importing of data for Analysis. However the spreadsheet get cluttered very often and i have manually delete the data worksheets.
I was wondering whether is there a way of deleting all the worksheets that contains data(I.E someway of protecting my proccessing and display sheets from being deleted, about 100 of them) or isit possible to delete spreadsheet in a loop with the last char being a number as all my data sheets are named xxx1 ,xyz2 etc and none of my other sheets end with a number
I have another report which needs to be sorted using a macro. However, before I can sort the data, I need to delete rows at the bottom of each report that have "0" values in column A and B. Again, each report I run is of different length so I need to write a code that will take that into consideration. Here is an example of what I need to do:
Sub second_step() ' ' second_step Macro '
' Range("C1").Select Range(Selection, Selection.End(xlDown)).Select Rows("7876:7876").Select Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents Range("A1").Select End Sub
Now, the thing is, not every report will start having "0" values in row 7876. How do I write a code to reflect this?
I have a question for you that can be much helpful for me. I prepared a macro which is collecting many datas from different pages and entering them on a sheet. Some lines of this sheet are with data and some are empty. I would like to write a code, which will delete the lines in which there is no data. But I would like the code to do this between line 5 and line 225.
I have what on the outset seemed a fairly basic task. In VBA I wanted to conditionally hide worksheets based on a value in my main worksheet.
I have 1 "main interface" worksheet with quite a few "result" worksheets as part of my workbook. Instead of scrolling left and right in the tabs menu everytime I wanted to veiw a particular worksheet I wanted to rather hide all the worksheets except the "main interface" and the relevant "result worksheet", which excel would identify based on a value in my main worksheet.
I wrote the following VBA code to do this but for some reason I cannot get it to work correctly. Perhaps I have made a simple logic error? I am fairly new to VBA. Any advice? ......
With a macro i want to delete some lines in sheet1. In sheet2 however i have formulas that point on sheet1 .. like this
=sheet1!A1 =sheet1!A2 =sheet1!A3
Now when i delete line 2, the result would look like this =sheet1!A1 #NV! =sheet1!A2
Is there a way to make these formulas pointing at the same cells after line two is deleted? And i do not mean a work around, with different formulas. I just want the Formulas in sheet2 to remain unchanged, regardless of what happens in sheet1...
I am currently working on a spreadsheet that records data from 11 different locations, with each location having it's own page within the workbook.
All of the pages are identical in terms of how the data is stored, the only difference is literally the name of each page (i.e. the location).
What I would like is to have a master page that then pulls through the relevant data, dependent on which location I choose from a drop down list on this page. As each individual page is identical in terms of lay-out, one formula will work for each page, with the only obvious exception is the page reference embedded in the formula. I know how to do a standard lookup for various figures, but I can't figure out a formula that would work with 11 different options... Is there any way to accomplish this?
I would like to reduce the size of a macro, but not managed to have success.
My goal is to just have the cells of these worksheets (in the code below), selected
Code: Sub SumazeCode() Sheets("PEDIDO 2013").Rows("42:67").EntireRow.Select Sheets("Print_Cliente").Rows("32:45").EntireRow.Select Sheets("Print_Producao").Rows("30:43").EntireRow.Select Sheets("Print_Orcamento").Rows("32:45").EntireRow.Select Sheets("PEDIDO 2013").Rows("C43:D43").EntireRow.Select End Sub
I am trying to write a macro that will look through all the worksheets in my workbook and delete any that are called "Data2", "Data3",... "Data#"
So basically I want to find any sheet that has Data and a number after it (with the exception of "Data1") and delete it. Does anyone know of any way to do this?
Would anyone have a way to delete worksheets not between worksheets called Beg and End? I am actually looking for a way to delete all worksheets to the left of Beg (including Beg) and all worksheets to the right of End (including End).
What I am attempting to do is have my spreadsheet automatically detect and highlight personnel working seven days in a row. There are 63 total columns, the first one being for the person's name. The next 31 columns are a full month's dates in which their day shifts are recorded, while the final 31 are the full month's dates for night shifts worked. Days worked are listed as a "1" in the cell beneath the respective shift's date, while days off go down as "3". Currently I am using conditional formatting with the following formula, which highlights cells in red:
I created the conditional formatting formula to apply to the row for the first person listed, and then copied and pasted the formatting only for the rest of the personnel rows. The issue I'm having is that while it will highlight the seventh work days, it will often highlights all cells prior to the grouping which has seven straight days of work. It also tends to highlight single or multiple cells in red which do not fit the pattern, and whose rows may not even have seven straight days of work.
I am trying to write a macro that takes the value in cell B2 from a workbook called ""numbers" in a worksheet called "summary," and creates a new worksheet in a workbook called "filestransfer" that is named the same thing as the value in cell B2 from "numbers." That part is done.
I tried to write a macro for deleting some columns automatically in my workbook
- it will be applied to all worksheets, except two worksheets called "samsung" and "toshiba" - columns will be deleted from V to AA and AU to AZ in the related worksheets
Please check my attached file and my macro inside it.
I'm on a system using Windows XP with several user accounts. It's possible to share a document by putting it in a directory called, unsurprisingly, "Shared files' or somesuch. Anyway, I wish to put one of my Excel spreadsheets into this directory so that other users can see it. The workbook itself has about 10 worksheets and I want other people to be able to edit only one of them. So I used the "Protect Worksheet" option within Excel to protect those worksheets I don't want edited. However, I notice that it's still possible to delete a worksheet even though its protected. Is there a way to prevent from deleting worksheets?
In my userform I have a list of check box's that can be selected. Currently if more than one is selected, they will appear in the spread sheet in the same line one after another with a space between them. How do I make it so they either appear with a comma appearing after each, so the next value appears on the next line below or most perferably the cell turning to a drop down list with the values?
Current code
VB: If CheckBox1.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox1.Caption If CheckBox2.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox2.Caption If CheckBox3.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox3.Caption If CheckBox4.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox4.Caption If CheckBox5.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox5.Caption If CheckBox6.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox6.Caption
Spreadsheet current cell appearence if all 6 are selected ' Chinnook EH101 Lynx Puma Sea King Fixed Wing'
Required appearence: Chinnook EH101 Lynx Puma Sea King Fixed Wing
I have a column of cells with Customer info and address in each row, that I need to move into multiple columns. ie Cust name, acct #,address, city,state, zip the problem is ALL of the info for each customer is in ONE cell.To be clear, cell B1 has complete name address etc for one customer, B2 has the info for another, B3... on and on. To complicate matters as in the example, there is not a pattern in the lines. the first example shows the info beginning on the 2nd line of the cell, but the 2nd example shows info in the first line. I have tried the text to column fixed width. Didn't work because names and addresses are of varying lengths. Then I tried delimited, but there isn't a common separation between different data. I am using excel 2007. FYI I did go through many many threads and found some that were close to my problem but not close enough to work.
" JOES TOWING SERVICE [0123451] 123 STREET
CALEDONIA FL 12345 " "BUBBA / SHOP ACCURATE AUTO SUPPLY INC [0234567] 21234 HYDRAULIC DR
I have a challenge where in have a list of about 4000 rows and each cell have mutiple lines. ( i know there is a thread about this but that did not address my issue).
So my data is as follows - (I have attached teh sample file)
"Pari bom - GP and CFO 02547895452 pari@gmail.com
papa bom - GP and CFO 23365789541 papa@gmail.com
mama bom - CFO 2587412589 pari@gmail.com
nana bom - CFO 2587412589 nana@gmail.com
masi bom - CFO 2587412589 masi@gmail.com
"
I want to be in a position to seperte each line in exclusive rows and then seperate teh name, number and the email ... the later eing easier but cant get my head around seperating the cells.
share a macro to copy cells from range C20:C300 and paste them in C2 on multiple lines. If possible two lines gap between each cell's value when they are pasted in C2.
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
