Clear Ranges On Different Worksheets

Dec 20, 2006

Clicking the button opens the MSgbox then on clicking Yes to run the code it stops on - Range("E3:BU98").Select - saying range selection method failed, and i cant understand why it seems to be such a simple piece of code.

Private Sub CommandButton2_Click()
Dim msg2 As Integer
msg2 = MsgBox("Has a back up copy been saved?" & vbCr & "Are you sure you want to clear all existing products and their results?", vbYesNo, "Delete Products?")
If msg2 = 6 Then
Worksheets("Input Record").Activate
Range("E3:BU98").Select
Selection.ClearContents
Worksheets("Results record").Activate
Range("E3:CA23").Select
Selection.ClearContents
Worksheets("Input Page").Activate
End If
End Sub

View 2 Replies


ADVERTISEMENT

Clear Named Ranges

Jan 5, 2009

There are over 100 named ranges in my excel 2007 workbook. Is there an easy way to clear all of these out?

View 9 Replies View Related

Clear Non-Contiguous Ranges

Feb 28, 2008

I'm getting an error on Range("AB6:AS12,..."). The error is: "1004 Runtime error, Method 'Range' of object '_Global' failed". My code should copy the values from place A to B. Then clear the content of B. Heres my code:

Sub Ny_uke()
' copy cells
Range("B21:K50").Value = Range("AX21:BG50").Value
Range("B54:K83").Value = Range("AX54:BG83").Value
Range("B87:K116").Value = Range("AX87:BG116").Value
Range("B120:K149").Value = Range("AX120:BG149").Value
Range("B153:K182").Value = Range("AX153:BG182").Value
Range("B186:K215").Value = Range("AX186:BG215").Value
Range("B219:K248").Value = Range("AX219:BG248").Value
' clear content of cells
Range("AB6:AS12,B21:K50,B54:K83,B87:K116,B120:K149,B153:K182,B186:K215,B219:K248").Clear
End Sub

When the error occurs I can press 'Debug' and continue the script whiteout changing the code and the code will continue without any errors. If I place the line 'Range("...")' on top of the script no error occurs. I I split the 'Range("...").Clear' into several Range("AB6:AS12").Clear the problem solves, but this produces several unwanted lines of code.

View 7 Replies View Related

Clear Contents Of 2 Ranges On Multiple Sheets

Mar 29, 2012

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?

View 7 Replies View Related

Clear Contents Multiple Non-Continuous Ranges

Nov 16, 2006

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"

View 4 Replies View Related

Clear & Copy Ranges Based On ListBox Choices

Aug 21, 2007

I have a saved file at d: named Report.xls from another file I am copying column and pasting data to this report file, this is done by a macro. While pasting data to the report file, a pop up message is coming up – A file named D:
eport.xls already exists in this location? Do you want to replace it?

In case “yes” is clicked it pastes the data But the problem is that the macro that I have in the report.xls vanishes. The macro I need in the report.xls?

View 6 Replies View Related

Hide Unwanted Columns & Clear Ranges Based On Cell Value

Sep 17, 2008

I have written a VBA procedure which is supposed to hide unneeded columns (based on the a number entered in a certain cell. This is working so far. Since I need to sum only the visible cells in a row i need to clear the contents of a range of cells if they will be hidden. Unfortunately the code i have written runs only to the ClearContents and then starts over from the beginning. If i take out the .clearcontents and put .Select for example instead, the code runs perfectly.
Here is the

Sub worksheet_calculate() 'Hide columns that are not needed

Sheet1. Unprotect

Application. ScreenUpdating = False

Dim i As Integer
Dim r As Integer
Dim s As Integer
Dim rngRange As Range

i = 2

View 8 Replies View Related

Clear Contents In Various Worksheets At Once?

Dec 2, 2011

I have a monthy activity where I would like to clear the area B13:P42 on various worksheets in the same spreadsheet. Is there an easier way to do this rather than just recording a macro?

View 5 Replies View Related

Clear Range From All Worksheets

Jul 19, 2007

I have a macro to clear all contents for a sheet in a workbook. The problem is that every file has over 20 sheets of data. Is there a way I can code a for statement to clear all sheets in this one workbook? Is it possible to make a for statement for multiple workbooks with several sheets?


Range("A11:Z11").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Selection.Interior.ColorIndex = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.ClearComments

View 9 Replies View Related

Clear Data In Multiple Worksheets

Aug 29, 2008

is 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

View 9 Replies View Related

Clear Numeric Input From All Worksheets

May 26, 2006

I'm trying to create code that will clean up a budget template of numeric inputs so it can be reused. This is as close as I can get but the code still isn't right.

[HTML]Sub ClearSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, 21).ClearContents
Next ws

End Sub[/HTML]

View 3 Replies View Related

Using Ranges From Two Worksheets

Oct 18, 2008

I would like to perform a calculation with each cell in a range from 2 worksheets and place the result in a range on a third worksheet.

I thought I could use the For Each Cell in Range construct but I don't see how to reference the two ranges in a nested pair of For loops. I am sure there must be an simple/elegant way to do this.

View 7 Replies View Related

Sum Same Ranges Of Multiple Worksheets IF...

Aug 27, 2009

I am trying to use an ActiveX CommandButton on Sheet40 to do the following:

Sheet40 (E31:AN39) = sum of (E31:AN39) for sheets 6-15 PROVIDED that cell D3= "y" in those sheets

So the steps are:
(1) Among sheets 6-15 select those in which cell D3 = "y"
(2) Set the range of (E31:AN39) in sheet40 to the sum of the same range in the selected sheets

ie cell E31 = sum of cells E31 in selected sheets... cell E32 same... cell AN39 same

View 6 Replies View Related

Name Ranges For Duplicate Worksheets

Feb 16, 2007

I have a worksheet in a workbook that is for 1 of 8 sub-projects. I have all the macros running perfectly and everything looks fine. Now, I have to duplicate that sheet 7 more times and create a Summary sheet for the entire workbook. My question is this:

How can I make it easy to set up named ranges in the new sheets so I don't have to pick each new range in each sheet and define names individually?

Example of a small macro...
Sub ActCurrJTDtoActPrevJTD()
Application.Goto Reference:="AActHrsCurrPeriod"
Selection.Copy
Application.Goto Reference:="AActHrsPrevPeriod"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1:V1").Select
Application.CutCopyMode = False
End Sub

I need to duplicate AActHrsCurrPeriod into BActHrsCurrPeriod and CActHrsCurrPeriod, up to an H version.

View 9 Replies View Related

Link To Ranges On Worksheets

Apr 7, 2008

I would like to link a sheet to another sheet in the same workbook. The link would indicate what week of information is needed. I would like the source (weekly information )page to go to the top of the page with the requested data. If I use a hyperlink it directs to the source page but this data could be several rows down. How do I create a link and have the source page provide the data at the top of the page?

View 6 Replies View Related

Sum Different Ranges Across Sheets/Worksheets

May 26, 2008

I have three sheets: On sheet 1, I have a list of products ( Down ) with Bought and sold info by time period ( Across ). On sheet 2, I have the same list of products from sheet 1 ( May not be in the same order ) with " Attributes " associated to them ( e.g. male, female etc ). On Sheet 3: I would like to add the Bought and sold info by time period on sheet 1 by attribute as per sheet 2

View 2 Replies View Related

Copying Date Ranges To Different Worksheets

May 31, 2009

I have a file with four worksheets. The first is the master sheet. The other three sheets we can call "SheetB" "SheetC" and "SheetD." The actual names are quite long. Column T of the master sheet (except for the header row) contains dates formatted as text in YYYYMM format. I need a macro that will copy the rows to the other three sheets based on these criteria:

SheetB - dates April 2010 or before
SheetC - dates May 2010 to April 2011
SheetD - dates May 2011 and beyond

View 9 Replies View Related

Copy Multiple Ranges To Other Worksheets

Aug 27, 2006

I have this

Sub transpose_UPCID()
Application.CutCopyMode = False
Range("A7:B7").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("BAUCS").Range("C11").PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = True
End Sub

This macro copies a range and paste it in another worksheet. I dont now how to make this macro to copy another range at the same time and copy it to row C37.

How can I make both things at the same time? The code I am showing here copies and paste product codes. I need to select and copy the production (which is in column F) for each product code and paste it starting in C37.

View 3 Replies View Related

Copy And Paste Ranges Between Worksheets

Dec 18, 2006

I'm trying to copy range from one worksheet to another. I'm guessing there must be something wrong with my syntax here:

For Count = 2 To 50
If Sheets("sheet1").Range("H" & (Count)) < 0.1 Then
Sheets("sheet3").Range("A" & (next_place), "K" & (next_place)) = Sheets("sheet1").Range("A" & (Count), "K" & (Count))
next_place = next_place + 1
End If
Next Count

View 4 Replies View Related

Consolidate Ranges From Multiple Worksheets

Jan 4, 2008

What I am attempting to do is take data from two columns on multiple sheets within the same workbook file and consolidate them using a macro. (End result would be a sum of the numbers associated with the adjacent name calculated using all the sheets data--as some names would appear on other sheets as well).

The first column is a list of names and the second is a list of numbers. I have titled the columns the same on each sheet and placed them in the same location. However, the number of rows of data each sheet contains will vary per sheet. (For example: one sheet might have 10 rows of names while the next might have 15 or so). Also, each time this file is to be used, there might be a differing number of sheets, as users add or remove a sheet.

Is it possible to create a macro that can scan the all the varying sheets's data and output a consolidation?

I have been able to create both a pivot table and use the consolidation feature by selecting the date ranges manually, but I am lost on how to automate this for other users that have very little excel knowledge.

I've uploaded an example file in case my explanation isn't clear.

View 7 Replies View Related

Coordinate Specific Ranges Across Worksheets

Jan 24, 2008

I have a spreadsheet of several sheets, but 7 of them (Red, Orange, Yellow, Green, Blue, Purple, Black) are the guts of the file and where I make adjustments. The layout of these sheets is the same, there are several cells of data on each sheet that coordinate with data in the same cell on other sheets. The problem is the sheets are so big, when I change sheets I have to go find the data that was in the same cell (or close) to where I just was on a previous sheet. So for example if on “Red” I am in cell AX253 and I go to sheet “Yellow” I would like it if AX253 would be selected when I get there. And if I select a different cell like D56 on Yellow and pick Green then I want D56 to be automatically selected on Green.

In addition, I plan to use a check box on an eighth sheet called “White” to turn the feature on and off. I have never written code for a check box before.

Is this to terribly difficult to do? I did some searching and saw some code that was similar to this but could not get it to work.

View 3 Replies View Related

Consolidate Ranges From Several Worksheets Into A Summary Worksheet

Sep 23, 2006

I need the correct syntax to consolidate ranges from several worksheets into a summary worksheet - all in the same workbook. I am building the Array on the fly, and I keep getting errors. (Subscript out of range being the most recent).

The code now is opening only one workbook so I can keep it simple. It iterates through the sheets collection, and builds the array.

Private Sub cbConsolidateToRollups_Click()
Dim MyArray() As Variant
Dim Source As Variant
Dim SheetNames As Variant
Dim SingleQuote, DoubleQuote
SingleQuote = Chr(39)
DoubleQuote = Chr(34)
'set the directory to Rollups
ToPath = Application.DefaultFilePath & "Cost Tracking" & "Rollups"
ChDir ToPath ....................

View 9 Replies View Related

Consolidate Ranges From Several Worksheets Into A Master Sheet

Jan 29, 2008

I am trying to consolidate a specific range on 8 similar worksheets into a master list through the use of a macro, and the workbook has a total of 26 worksheets. Details:

The worksheets to be consolidated are named: AR, CM, JR, Trader1, Trader2, Trader3, Trader4, and Trader5. The range to be consolidated are: A6:F26, of each of the above worksheets. Currently, A6:D21 are linked through an array from the worksheet "Index". This is because there are often changes to the names listed under "Trader/Backup Trader", or there may be additions/deletions of a row.

Right now, I have set up a sheet called "Traders List", using arrays to link the data on the 8 different sheets. The only problem is, if there were a change in the "Index" worksheet, I would have to manually drag out each array, for these 8 different worksheets in the "Traders List" sheet. Is there a way a macro can be written such that it is automatically linked to each range (A6:F26) on those 8 worksheets? Is it possible to only show rows that are non-zero?

View 2 Replies View Related

Sort Data Ranges Across Multiple Worksheets

May 2, 2008

I have been asked to create an attendance worksheet where employee names and data are entered on a "main" worksheet and hours are entered on monthly worksheets. The names on the monthly worksheets are referenced from the main worksheet. Therefore, if I add a name and do a sort, the names on all pages will move, but the data will not. I imagine I will need an ID column to help sort. How do I make a macro to do the sort?

View 2 Replies View Related

Macro To Summarize Data From Multiple Worksheets With Different Ranges?

Jun 25, 2014

I have a requirement where I need to summarize multiple work sheets. And each work sheet as different range. Column names are same in each sheet but number of rows in each are different. Like consider there are 3 sheets with employee details. Each sheet has Employee Name, Employee Number, Employee Location. But in first sheet as 10 employees and second sheet has 20 employees and third as 25. So the requirement is I need to summarize all employees.

View 1 Replies View Related

Hide Certain Ranges In Different Worksheets According To The Assigned Cell Value In The First Worksheet

Jan 22, 2010

I have got a macro (from Mr Excel's MVP) that hide certain cells according to assigned cell value
What I need is:
to hide certain ranges in different worksheets according to the assigned cell value in the first worksheet

if sheets1.cell A1= "hi" then
range (" goooo") in sheet 1.hide
range ("deeeee")in sheet 2 .hide
range ("faaaaa") in sheet 3.hide
the macro I had is :
====================================
====================================
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Target.Value = "hi" Then
Worksheets("Sheet1").Rows(2).Hidden = True
Else
Worksheets("Sheet1").Rows(2).Hidden = False
End If
End If
End Sub

View 9 Replies View Related

Excel 2010 :: Create Macro Button To Clear Multiple Cells On Multiple Worksheets?

Jul 2, 2014

I 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

View 8 Replies View Related

Clear Macro NOT Clear Formula?

Aug 15, 2014

I'm having a problem with a macro clearing a formula in a cell. I have the same type of cell that doesn't have the problem but I can't find the difference between the 2 cells or difference in vb that's making it happen. I have to intentionally cause this to happen but don't see why it's happening. Do I need to attach workbook and describe what's happening? I have been copying and pasting from different sources as well as paying to have it created/started but it was expensive(for me) and I make nothing off of it, just use it at work. I am not proficient in Excel or vb but I'm desperately trying to learn as I go so as not to fork out a few hundred dollars again.

here's atleast the vb for the macros:

VB:
Sub clearclientinfo() '
'
'
'
Sheet5.Select

[Code].....

View 9 Replies View Related

Move Ranges To Corresponding Ranges Based On Column Having Data

Oct 25, 2007

I need help creating a macro that will search through my excel spreadsheet and for every instance where column A isn't empty it should cut a range of columns from that row and paste them in a different range of columns in the row before it. It should then delete the row that it cut the columns from and keep searching until it has done this for the whole worksheet. I can modify which range of columns are needed, but it has been so long since I've worked with excel macros that I haven't been able to do it.

View 5 Replies View Related

Static Ranges And Dynamic Ranges

Feb 26, 2010

definitions for static ranges and dynamic ranges?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved