I am using this code to clear 25 sheets in a workbook.
Sheets("Dadadooo Lasino").Select
Range("A2:I500").Select
Selection.ClearContents
This code works fine, but was wondering if a faster way to do this existed.
How do I selectively rename sheets by sheet code name?
Instead of sheet1.name = "New Name" I need sheet1 to be a variable of a specific sheet.
I have the following code for about 20 sheets
Sheets("Multi CT 43").Select
Cells.ClearContents
Range("A1").Select
Sheets("Multi CT 61").Select
Cells.ClearContents
Range("A1").Select
Sheets("Multi CT 80").Select
Cells.ClearContents
Range("A1").Select
i'm sure there is a more simple way of doing this but I can't find a way that works.
I need to clear the the text in the same cell ranges on multiple worksheets. on a regular basis.
F7:K13
Q7:Q13
Is there a simple way to do this?
I am trying to clear check boxes and list boxes on multiple worksheets within a workbook. Below is what I have,but doesn't work. Note: the "bottom " code does work to clear selected cells of their data. I tried to create a macro for the list boxes selecting "----" which I have as the last entry in the vlookup table but was totally unsuccessful at.
Sub cleardata()
Dim Obj As OLEObject
Dim ws As Worksheet
[Code]....
I would like a macro to clear data on all sheets, except the last 3 sheets from row 4 onwards.
View 9 Replies View Relatedclear, copy and paste on different sheets
I recorded the following macro :
In a workbook with about 400 sheets I have this code, to clear any light blue cells for a button:
View 5 Replies View RelatedIm looking for a Workbook_Open event that will "clear all" on every sheet.
View 3 Replies View RelatedLooking to code a loop to go to sheets whose names begin with "day" and a number and clear a specific range. How would i code this without affecting the other sheets in the book?
View 3 Replies View RelatedI have attached a test workbook excel 2010 (ignore ref# errors, I've cut the workbook down for uploading purposes) What I would like to do is have a 'Button' on my 'information Sheet' which when clicked would clear certain cells. I have searched the forum but can't find a solution, everyone seems to want to delete rows or columns but I just want to clear certain cells. The workbook will have 11 sheets each named 'caravan 1' through to 'caravan 11' The uploaded test workbook only only has 3 sheets.
On 'caravan 1' (which is slightly different to the other 10) I want to clear the content of cells
B4 & B5
C4, C22 & C41
D4 & D5
E4,E5, E22,E23,E41 &E 42
On all other 'Caravan sheets' I want to clear the contents of cells
D4 & D5
E4, E5, E22, E23, E41 & E42
It would be icing on the cake if it could give a warning such as " are you sure you want to clear these cells" but that isn't really necessary. The worksheets will be password protected, but the cells mentioned above will not be. If it proves too difficult to clear all the cells on all the sheets with one click, then perhaps a simpler solution might be to have a button on each sheet instead
I have a Worksheet_Change method in my sheet module which has suddenly begun working selectively. I have several target ranges in my worksheet that I need to watch for changes. One range of cells is all pull-down menus (a list of electrical loads through excel's validation), another two cells are looking at the name and location of an electrical panel.
When the user selects an electrical load, the worksheet_change method updates a legend of definitions. When the user enters a new name or location, the method updates similar fields elsewhere on the same sheet.
The problem is, excel has stopped executing worksheet_change when a load is chosen or location entered. It only executes when the aforementioned load and location are deleted. However, the name field triggers worksheet_change just fine!
I have attempted to put breakpoints on the worksheet_change method; Excel does not even execute the method as described above. I've tried breaking on the toggling of Application.EnableEvents (which occurs early on in the execution of worksheet_change). I currently have a global watch on Application.EnableEvents and I'm quite sure it's TRUE before I attempt to trigger a worksheet_change event.
My next step is to create a brand new workbook and copy my spreadsheet and code into it. I'll post the results of that experiment.
my values within column A are separated by a dash. I'd like column B to continuously copy column A with the exception of the characters after the dash. example:
____A_____________B
12345-456_______12345
22456-333_______22456
1553665-45______15553665
How can I format cell B to constantly reproduce this result?
I'm trying to programatically remove select controls from a worksheet (I want to keep combobox and buttons, but remove all else). I execute the following code, but keep getting a run time error "The index into the specified collection is out of bounds" about half way through the deletes.
Dim i As Integer
Dim counter As Integer
i = ActiveSheet.Shapes.Count
For counter = 1 To i
If ActiveSheet.Shapes(counter). Name = "ComboBox1" Then Goto SkipDelete
If ActiveSheet.Shapes(counter).Name = "CommandButton1" Then Goto SkipDelete
If ActiveSheet.Shapes(counter).Name = "CommandButton2" Then Goto SkipDelete
If ActiveSheet.Shapes(counter).Name = "CommandButton3" Then Goto SkipDelete
ActiveSheet.Shapes(counter).Delete
SkipDelete:
Next counter
I have a chart with multiple series which I am hoping to show/hide based upon a particular value attached to each series. I was therefore wondering if it was possible to extend the Series object class so that I could have something along the lines of a "Series.specialvalue" property which I could assign and then check to decide whether to show or hide the series?
View 1 Replies View Related=IF(G88="PT",VLOOKUP(B88,Vlookup1!A:R,5),IF(G88="OT",VLOOKUP(B88,Vlookup1!A:R,6),IF(G88="ST",VLOOKUP (B88,Vlookup1!A:R,7),FALSE)))
Two tabs in an excel spreadsheet. One with this formula and another one named Vlookup1 with the info (three columns indicating diagnosis codes for PT (Physical Therapy), OT (Occupational Therapy) and ST (Speech Therapy). Out of approximately 350 rows, this formula works perfectly fine with all but three customers’ names. It seems to be only with the last three customers –sorted alphabetically– and can’t figure out how to solve it. Have reduced the amount of rows in the info tab / have changed the format of all cells for these 3 customers / have retyped all information under another row and inserted above all the conflict rows but it always brings “0” as the cell value – not even False….
I've got a master excel sheet with about 6000 rows of unique variables. In separate source files, I have many rows of these variables with information next to each. Each file has a random number of these variables, some a couple of hundred, some a thousand or two.
What I'm looking to do is to have the master sheet with all 6000 rows and have all of the information next to each variable, with new columns for each new bit of information.
I could sit there for hours copying and pasting each new bit of information to the relevant variable in the master sheet, but I'm convinced there's a quicker way. Is there, for example, a way to filter the master sheet based on the variables contained in one of the source files and therefore (once a-z sorted) copy all the information from a source file and simply paste it into the master file? Then once the filter is lifted the same can be done for the next file (there's only about 20 source files so that's manageable).
Is it possible to selectively format text in a textbox on a userform ie. to make some of the text bold, other parts underlined and other parts italic etc. The text which appears in my text box is entered automatically by a piece of code which extracts the contents of certain cells and arranges them in the text box as needed. I would now like to be able to display the text box with the text formatted so that its not just all in plain text.
View 4 Replies View RelatedI'm trying to copy cells from a column in one workbook to cells in a column in another workbook based on what's in a header column.
Such as
Original workbook
Mat Qty
A 1
C 3
D 2
E 4
G 5
Total 15
Target workbook becomes
Mat Qty -> Mat Qty
A -> A 1
B -> B
C -> C 3
D -> D 2
E -> E 4
F -> F
G -> G 5
My code so far (which might not be worth reading) is:
Code:
Sub CopyColumnToWorkbook()
Dim sourceColumn As Range, targetColumn As Range, sourceColumnValue As Range, targetColumnValue As Range
Dim mySourceCell As Range, myTargetCell As Range
[code].....
I'm getting a runtime error ('1004'. Application-defined or object-defined error) on the Copy statement within the If sttement.
I am attempting to modify VBA code from [URL] .....
The section of the code I want to modify is below dealing with importing selective text. The original function returned a "False" if the line contained a keyword specified in the Array function. I have reworked it so that it shows a "True" if the line contains a keyword specified in the Array function.
My only problem is that it only does it when the keyword appears in the beginning of the line, as it uses the "Left" operator in looking at line. How do I configure the function so that it returns a "True" if the keywords appears anywhere in the line?
i have attached a template spreadesheet that 20 people uses everyday. The template works fine but what i would like to do is write a code to clear all the content that manually entered beforeclose. For some reason code is not working properly because when i wrote code to clear the contents the buttons don't work . I also attached the spreadsheet.
View 4 Replies View Relatedis there a way to clear data in all worksheets according to the ranges specified in the code below, I have around 34 worksheets in which i need to clear data and i dont want to write the below code 34 times.
Sub Clear()
With Sheets("Rec")
.Range("A7:C7").Clearcontents
.Range("E7:O7").Clearcontents
End With
End Sub
I have a sheet with 550 checkboxes (form toolbar). I want to be able to reset the boxes to unchecked with a button. I found an example here but I seem to need the label (Ex: "Check Box 1")
Sub UnCheckit()
ActiveSheet.Shapes("Check Box 1").ControlFormat.Value = False
End Sub
It doesn't make sense I would need to leave the label showing on the sheet, but the label seems to be the identifer for each checkbox, not the named cell. So I am a little confused how to alter the code.
So when it works it looks like:
[] Check Box 1 Click this box to indicate something
While I wanted it to look like:
[] Click this box to indicate something
(I guess control checkboxes was the way to go but we are here now.)
I am currently trying to write a macro that will search a column for different strings. These strings are inside the cell (as in it is not the only value in the cell) and then copy the entire cell over to another column, then delete the two cells to the right of the originally found cell.
Heres an example of what I want to:
There are four columns, the first has a name with both the first and last (amanda white)the second just the first name (amanda), the third just the last name(white), and the forth an organization name. if the first column contains an organzation name, it has to clear out the first name and last name columns, and copy the name column to the organization column.
NAME | FIRST | LAST| ORG|
amanda white amanda white
mike jones mike Jones
pizza hut pizza hut
I have a list of organization keywords to search the first column (e.g. enterprise, variety, management, pizza). I want it to recongnise the "pizza", copy that entire cell over to the organization column and delete the first name and last name for that row.I've been trying to modify a code like this but i can't seem to make anything work.
VB:
FindWhat = "pizza"
For Each Cell In Range("B2", Range("B" & Rows.Count).End(xlUp)) [code]....
what is described in the post clear "contents of rows based on cell contents in column" clear contents of rows based on cell contents in column
What is different is that for rows that I wish to leave untouched, I have in column A the text "Active". If that text is not present (""), then I need to clear contents for only certain cells in each row because others have formulas. For example, if A32 does not say "Active" I want to clear contents of cells D:32, G:32, etc while leaving the others in row 32 in tact. I need this to loop through about 400 rows. If it would make the macro easier, I can have the data in column "A" say "Inactive"/"Active" instead of blank/"Active"
In my code I am searching a spreadsheet for certain states and deleting rows that have states I want to exclude. Is there a way to do this with a list of states, instead of having to make many for loops?
For i = lastrow To 2 Step -1
Cells(i, 4).Select
If Cells(i, 4).Value = "PA" Then
Rows(i).Select
Selection.ClearContents
End If
On Error Resume Next
Next
For i = lastrow To 2 Step -1
Cells(i, 4).Select
If Cells(i, 4).Value = "TX" Then
Rows(i).Select
Selection.ClearContents
End If
On Error Resume Next
Next
I have some problems in VBA Excel List Box which I want to rectify. See an attachment " listbox. zip" file
I craated a Userform and Seven List boxes in it. the following problems I am facing.
1) When I change List Box 1 other List Boxes do not show with related data.
2) List Box 1 and List Box 2 show their header others List boxes not. Why?
3) I have defined Names of all ranges. How Can I use it in VBA Coding?
I have a workbook that updates from external source and creates sheets depending on a cell range.
I have put tab 1 and tab 0 on either end of where the new sheets will be inputted, will never know how many sheets
What i need to happen is if someone fills in "complete" in A7 in my "summary" sheet then the values in row 6 in all the other sheets get hardcoded. This needs to happen from A7 down to A26, so A8 = complete then copy row 7 etc
This is what i have so far
I get compile error here ........Sheets(ArrSh(1)).Activate
Also need it to work for all the other rows.
Sub hardcode()
'
'Sheets("Summary"). Select
If Range("a7") = "complete" Then
'
Sheets(Array("1", "0")).Select
Sheets(ArrSh(1)).Activate
[Code] ......
I have been running a simulation for about 18 hours now and just received:
Run-time error '1004':
Method 'Add' of object ' Sheets' failed
I have been creating new sheets, importing data, pulling some values from the data then deleting the respective sheet. I am using:
ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count)
The sheet is actually being added to the workbook, seemingly before the error. I resume the code, and a new sheet is placed in the workbook and it errors again. The Debugger stops and highlights on the code above.The sheet count number was 10895 at the error, just as an indicator of how many times the simulation has performed successfully. I am hoping this is something I can fix without having to start over...
I have a work sheet named "Main_List"...In column D starting with "D2" I would like to list worksheets that I would like to have printed via VBA.
The workbook has several hundred worksheets and I would like to list in column D only worksheets that I would like to print with VBA code.