View Full Version : Duplicating macros across worksheets
Hoosier03
08-31-2011, 01:25 PM
I am using Excel 2007. I ran and recorded a simple marco that deletes data out of 10 sections in one workheet. I would like to have a macro that deletes the six other worksheets as well. How can I set this up to where I don't have to run it 6 times? Also, I cannot find the code in the VBE. Where I can find this?
Thanks,
Rodney
Hoosier03
08-31-2011, 04:52 PM
Here is the code
Sub DataWipe()
'
' DataWipe Macro
'
'
Range("CA5:DZ17").Select
Selection.ClearContents
ActiveWindow.ScrollColumn = 105
ActiveWindow.ScrollColumn = 104
ActiveWindow.ScrollColumn = 103
ActiveWindow.ScrollColumn = 101
ActiveWindow.ScrollColumn = 99
ActiveWindow.ScrollColumn = 97
ActiveWindow.ScrollColumn = 94
ActiveWindow.ScrollColumn = 93
ActiveWindow.ScrollColumn = 92
ActiveWindow.ScrollColumn = 91
ActiveWindow.ScrollColumn = 90
ActiveWindow.ScrollColumn = 88
ActiveWindow.ScrollColumn = 87
ActiveWindow.ScrollColumn = 86
ActiveWindow.ScrollColumn = 85
ActiveWindow.ScrollColumn = 84
ActiveWindow.ScrollColumn = 81
ActiveWindow.ScrollColumn = 80
ActiveWindow.ScrollColumn = 79
ActiveWindow.ScrollColumn = 77
ActiveWindow.ScrollColumn = 75
ActiveWindow.ScrollColumn = 73
ActiveWindow.ScrollColumn = 72
ActiveWindow.ScrollColumn = 71
ActiveWindow.ScrollColumn = 70
ActiveWindow.ScrollColumn = 68
ActiveWindow.ScrollColumn = 67
ActiveWindow.ScrollColumn = 66
ActiveWindow.ScrollColumn = 65
ActiveWindow.ScrollColumn = 64
ActiveWindow.ScrollColumn = 62
ActiveWindow.ScrollColumn = 61
ActiveWindow.ScrollColumn = 62
ActiveWindow.ScrollColumn = 64
ActiveWindow.ScrollColumn = 65
Range("CA21:DZ33").Select
Selection.ClearContents
ActiveWindow.ScrollColumn = 123
ActiveWindow.ScrollColumn = 121
ActiveWindow.ScrollColumn = 120
ActiveWindow.ScrollColumn = 118
ActiveWindow.ScrollColumn = 117
ActiveWindow.ScrollColumn = 114
ActiveWindow.ScrollColumn = 113
ActiveWindow.ScrollColumn = 111
ActiveWindow.ScrollColumn = 110
ActiveWindow.ScrollColumn = 108
ActiveWindow.ScrollColumn = 107
ActiveWindow.ScrollColumn = 106
ActiveWindow.ScrollColumn = 105
ActiveWindow.ScrollColumn = 104
ActiveWindow.ScrollColumn = 103
ActiveWindow.ScrollColumn = 101
ActiveWindow.ScrollColumn = 100
ActiveWindow.ScrollColumn = 99
ActiveWindow.ScrollColumn = 98
ActiveWindow.ScrollColumn = 97
ActiveWindow.ScrollColumn = 95
ActiveWindow.ScrollColumn = 94
ActiveWindow.ScrollColumn = 93
ActiveWindow.ScrollColumn = 92
ActiveWindow.ScrollColumn = 91
ActiveWindow.ScrollColumn = 90
ActiveWindow.ScrollColumn = 88
ActiveWindow.ScrollColumn = 87
ActiveWindow.ScrollColumn = 86
ActiveWindow.ScrollColumn = 85
ActiveWindow.ScrollColumn = 84
ActiveWindow.ScrollColumn = 82
ActiveWindow.ScrollColumn = 80
ActiveWindow.ScrollColumn = 79
ActiveWindow.ScrollColumn = 78
ActiveWindow.ScrollColumn = 77
ActiveWindow.ScrollColumn = 75
ActiveWindow.ScrollColumn = 74
ActiveWindow.ScrollColumn = 73
ActiveWindow.ScrollColumn = 72
ActiveWindow.ScrollColumn = 71
ActiveWindow.ScrollColumn = 70
Range("CA37:DZ49").Select
Selection.ClearContents
ActiveWindow.ScrollColumn = 121
ActiveWindow.ScrollColumn = 120
ActiveWindow.ScrollColumn = 119
ActiveWindow.ScrollColumn = 116
ActiveWindow.ScrollColumn = 112
ActiveWindow.ScrollColumn = 111
ActiveWindow.ScrollColumn = 110
ActiveWindow.ScrollColumn = 107
ActiveWindow.ScrollColumn = 106
ActiveWindow.ScrollColumn = 105
ActiveWindow.ScrollColumn = 103
ActiveWindow.ScrollColumn = 101
ActiveWindow.ScrollColumn = 100
ActiveWindow.ScrollColumn = 99
ActiveWindow.ScrollColumn = 98
ActiveWindow.ScrollColumn = 97
ActiveWindow.ScrollColumn = 95
ActiveWindow.ScrollColumn = 94
ActiveWindow.ScrollColumn = 93
ActiveWindow.ScrollColumn = 92
ActiveWindow.ScrollColumn = 91
ActiveWindow.ScrollColumn = 90
ActiveWindow.ScrollColumn = 88
ActiveWindow.ScrollColumn = 87
ActiveWindow.ScrollColumn = 86
ActiveWindow.ScrollColumn = 85
ActiveWindow.ScrollColumn = 84
ActiveWindow.ScrollColumn = 82
ActiveWindow.ScrollColumn = 81
ActiveWindow.ScrollColumn = 80
ActiveWindow.ScrollColumn = 79
ActiveWindow.ScrollColumn = 78
ActiveWindow.ScrollColumn = 77
ActiveWindow.ScrollColumn = 75
ActiveWindow.ScrollColumn = 74
ActiveWindow.ScrollColumn = 73
ActiveWindow.ScrollColumn = 72
ActiveWindow.ScrollColumn = 71
ActiveWindow.ScrollColumn = 70
ActiveWindow.ScrollColumn = 68
ActiveWindow.ScrollColumn = 67
Range("CA53:DZ65").Select
Selection.ClearContents
ActiveWindow.ScrollColumn = 117
ActiveWindow.ScrollColumn = 112
ActiveWindow.ScrollColumn = 110
ActiveWindow.ScrollColumn = 106
ActiveWindow.ScrollColumn = 105
ActiveWindow.ScrollColumn = 104
ActiveWindow.ScrollColumn = 103
ActiveWindow.ScrollColumn = 101
ActiveWindow.ScrollColumn = 100
ActiveWindow.ScrollColumn = 99
ActiveWindow.ScrollColumn = 98
ActiveWindow.ScrollColumn = 97
ActiveWindow.ScrollColumn = 95
ActiveWindow.ScrollColumn = 94
ActiveWindow.ScrollColumn = 93
ActiveWindow.ScrollColumn = 92
ActiveWindow.ScrollColumn = 91
ActiveWindow.ScrollColumn = 90
ActiveWindow.ScrollColumn = 88
ActiveWindow.ScrollColumn = 87
ActiveWindow.ScrollColumn = 86
ActiveWindow.ScrollColumn = 85
ActiveWindow.ScrollColumn = 84
ActiveWindow.ScrollColumn = 82
ActiveWindow.ScrollColumn = 81
ActiveWindow.ScrollColumn = 80
ActiveWindow.ScrollColumn = 79
ActiveWindow.ScrollColumn = 78
ActiveWindow.ScrollColumn = 77
ActiveWindow.ScrollColumn = 75
ActiveWindow.ScrollColumn = 74
ActiveWindow.ScrollColumn = 73
ActiveWindow.ScrollColumn = 72
ActiveWindow.ScrollColumn = 71
ActiveWindow.ScrollColumn = 68
ActiveWindow.ScrollColumn = 67
ActiveWindow.ScrollColumn = 66
ActiveWindow.ScrollColumn = 65
ActiveWindow.SmallScroll Down:=27
Range("CA69:DZ81").Select
Selection.ClearContents
ActiveWindow.ScrollColumn = 111
ActiveWindow.ScrollColumn = 110
ActiveWindow.ScrollColumn = 105
ActiveWindow.ScrollColumn = 99
ActiveWindow.ScrollColumn = 97
ActiveWindow.ScrollColumn = 94
ActiveWindow.ScrollColumn = 90
ActiveWindow.ScrollColumn = 88
ActiveWindow.ScrollColumn = 86
ActiveWindow.ScrollColumn = 82
ActiveWindow.ScrollColumn = 81
ActiveWindow.ScrollColumn = 79
ActiveWindow.ScrollColumn = 78
ActiveWindow.ScrollColumn = 77
ActiveWindow.ScrollColumn = 74
ActiveWindow.ScrollColumn = 73
ActiveWindow.ScrollColumn = 72
ActiveWindow.ScrollColumn = 71
ActiveWindow.ScrollColumn = 70
ActiveWindow.ScrollColumn = 68
ActiveWindow.ScrollColumn = 67
ActiveWindow.ScrollColumn = 66
ActiveWindow.ScrollColumn = 65
ActiveWindow.ScrollColumn = 62
ActiveWindow.ScrollColumn = 61
ActiveWindow.ScrollColumn = 60
ActiveWindow.ScrollColumn = 59
ActiveWindow.SmallScroll Down:=21
Range("CA86:DZ98").Select
Selection.ClearContents
ActiveWindow.ScrollColumn = 116
ActiveWindow.ScrollColumn = 114
ActiveWindow.ScrollColumn = 113
ActiveWindow.ScrollColumn = 110
ActiveWindow.ScrollColumn = 107
ActiveWindow.ScrollColumn = 105
ActiveWindow.ScrollColumn = 103
ActiveWindow.ScrollColumn = 99
ActiveWindow.ScrollColumn = 98
ActiveWindow.ScrollColumn = 95
ActiveWindow.ScrollColumn = 93
ActiveWindow.ScrollColumn = 92
ActiveWindow.ScrollColumn = 90
ActiveWindow.ScrollColumn = 87
ActiveWindow.ScrollColumn = 85
ActiveWindow.ScrollColumn = 84
ActiveWindow.ScrollColumn = 82
ActiveWindow.ScrollColumn = 81
ActiveWindow.ScrollColumn = 80
ActiveWindow.ScrollColumn = 79
ActiveWindow.ScrollColumn = 78
ActiveWindow.ScrollColumn = 77
ActiveWindow.ScrollColumn = 75
ActiveWindow.ScrollColumn = 73
ActiveWindow.ScrollColumn = 72
ActiveWindow.ScrollColumn = 71
ActiveWindow.ScrollColumn = 70
ActiveWindow.ScrollColumn = 68
ActiveWindow.ScrollColumn = 67
ActiveWindow.ScrollColumn = 66
ActiveWindow.ScrollColumn = 65
ActiveWindow.ScrollColumn = 64
ActiveWindow.SmallScroll Down:=18
Range("CA102:DZ114").Select
Selection.ClearContents
ActiveWindow.ScrollColumn = 117
ActiveWindow.ScrollColumn = 116
ActiveWindow.ScrollColumn = 113
ActiveWindow.ScrollColumn = 108
ActiveWindow.ScrollColumn = 105
ActiveWindow.ScrollColumn = 104
ActiveWindow.ScrollColumn = 101
ActiveWindow.ScrollColumn = 99
ActiveWindow.ScrollColumn = 98
ActiveWindow.ScrollColumn = 97
ActiveWindow.ScrollColumn = 95
ActiveWindow.ScrollColumn = 94
ActiveWindow.ScrollColumn = 93
ActiveWindow.ScrollColumn = 92
ActiveWindow.ScrollColumn = 91
ActiveWindow.ScrollColumn = 90
ActiveWindow.ScrollColumn = 88
ActiveWindow.ScrollColumn = 87
ActiveWindow.ScrollColumn = 86
ActiveWindow.ScrollColumn = 85
ActiveWindow.ScrollColumn = 84
ActiveWindow.ScrollColumn = 82
ActiveWindow.ScrollColumn = 81
ActiveWindow.ScrollColumn = 80
ActiveWindow.ScrollColumn = 79
ActiveWindow.ScrollColumn = 78
ActiveWindow.ScrollColumn = 77
ActiveWindow.ScrollColumn = 75
ActiveWindow.ScrollColumn = 73
ActiveWindow.ScrollColumn = 72
ActiveWindow.ScrollColumn = 71
ActiveWindow.ScrollColumn = 70
ActiveWindow.ScrollColumn = 68
ActiveWindow.ScrollColumn = 67
ActiveWindow.SmallScroll Down:=24
Range("CA118:DZ130").Select
Selection.ClearContents
ActiveWindow.ScrollColumn = 116
ActiveWindow.ScrollColumn = 114
ActiveWindow.ScrollColumn = 112
ActiveWindow.ScrollColumn = 110
ActiveWindow.ScrollColumn = 108
ActiveWindow.ScrollColumn = 107
ActiveWindow.ScrollColumn = 106
ActiveWindow.ScrollColumn = 105
ActiveWindow.ScrollColumn = 104
ActiveWindow.ScrollColumn = 103
ActiveWindow.ScrollColumn = 101
ActiveWindow.ScrollColumn = 100
ActiveWindow.ScrollColumn = 99
ActiveWindow.ScrollColumn = 98
ActiveWindow.ScrollColumn = 97
ActiveWindow.ScrollColumn = 95
ActiveWindow.ScrollColumn = 94
ActiveWindow.ScrollColumn = 93
ActiveWindow.ScrollColumn = 92
ActiveWindow.ScrollColumn = 91
ActiveWindow.ScrollColumn = 90
ActiveWindow.ScrollColumn = 88
ActiveWindow.ScrollColumn = 87
ActiveWindow.ScrollColumn = 86
ActiveWindow.ScrollColumn = 85
ActiveWindow.ScrollColumn = 84
ActiveWindow.ScrollColumn = 82
ActiveWindow.ScrollColumn = 81
ActiveWindow.ScrollColumn = 80
ActiveWindow.ScrollColumn = 78
ActiveWindow.ScrollColumn = 75
ActiveWindow.ScrollColumn = 74
ActiveWindow.ScrollColumn = 73
ActiveWindow.ScrollColumn = 72
ActiveWindow.ScrollColumn = 71
ActiveWindow.ScrollColumn = 70
ActiveWindow.ScrollColumn = 68
ActiveWindow.ScrollColumn = 67
ActiveWindow.ScrollColumn = 66
Range("CA134:DZ146").Select
Selection.ClearContents
ActiveWindow.ScrollColumn = 118
ActiveWindow.ScrollColumn = 116
ActiveWindow.ScrollColumn = 112
ActiveWindow.ScrollColumn = 111
ActiveWindow.ScrollColumn = 106
ActiveWindow.ScrollColumn = 105
ActiveWindow.ScrollColumn = 101
ActiveWindow.ScrollColumn = 100
ActiveWindow.ScrollColumn = 99
ActiveWindow.ScrollColumn = 98
ActiveWindow.ScrollColumn = 97
ActiveWindow.ScrollColumn = 95
ActiveWindow.ScrollColumn = 94
ActiveWindow.ScrollColumn = 93
ActiveWindow.ScrollColumn = 92
ActiveWindow.ScrollColumn = 91
ActiveWindow.ScrollColumn = 90
ActiveWindow.ScrollColumn = 88
ActiveWindow.ScrollColumn = 87
ActiveWindow.ScrollColumn = 86
ActiveWindow.ScrollColumn = 85
ActiveWindow.ScrollColumn = 84
ActiveWindow.ScrollColumn = 82
ActiveWindow.ScrollColumn = 81
ActiveWindow.ScrollColumn = 80
ActiveWindow.ScrollColumn = 79
ActiveWindow.ScrollColumn = 78
ActiveWindow.ScrollColumn = 77
ActiveWindow.ScrollColumn = 75
ActiveWindow.ScrollColumn = 74
ActiveWindow.ScrollColumn = 73
ActiveWindow.ScrollColumn = 72
ActiveWindow.ScrollColumn = 71
ActiveWindow.ScrollColumn = 67
ActiveWindow.ScrollColumn = 65
ActiveWindow.ScrollColumn = 62
ActiveWindow.ScrollColumn = 61
ActiveWindow.ScrollColumn = 60
ActiveWindow.ScrollColumn = 59
ActiveWindow.ScrollColumn = 58
ActiveWindow.ScrollColumn = 59
ActiveWindow.ScrollColumn = 60
ActiveWindow.ScrollColumn = 61
ActiveWindow.ScrollColumn = 62
ActiveWindow.ScrollColumn = 64
ActiveWindow.SmallScroll Down:=30
Range("CA150:DZ162").Select
Selection.ClearContents
End Sub
Hoosier03
08-31-2011, 04:54 PM
How can I clean this up and get it to run across multiple sheets at the same time?
Thanks,
Rodney
Paul_Hossler
08-31-2011, 06:03 PM
1. Using the VBA tags makes it easier to read
2. The macro recorder will make executable code, but not effecient of flexible code
3. This is one way. It's pretty brute force, but has the advantages of being easy to read and to maintain
4. The macro recorder probably saved the code in PERSONAL.XLSB, but in your workbook, goto the VBE and insert a Module and paste this in to try
Option Explicit
Sub DataWipe()
Application.ScreenUpdating = False
Call DeleteRanges("Sheet1")
Call DeleteRanges("Sheet2")
Call DeleteRanges("Sheet3")
Call DeleteRanges("Sheet4")
Call DeleteRanges("Sheet5")
Application.ScreenUpdating = True
End Sub
Private Sub DeleteRanges(sWorksheet As String)
With ActiveWorkbook.Worksheets(sWorksheet)
.Range("CA5:Z17").ClearContents
.Range("CA21:Z33").ClearContents
.Range("CA37:Z49").ClearContents
.Range("CA53:Z65").ClearContents
.Range("CA69:Z81").ClearContents
.Range("CA86:Z98").ClearContents
.Range("CA102:Z114").ClearContents
.Range("CA118:Z130").ClearContents
.Range("CA134:Z146").ClearContents
.Range("CA150:Z162").ClearContents
End With
End Sub
This is not tested
Paul
Hoosier03
09-01-2011, 09:14 AM
I inserted what you put and changed the sheet names to reflect my worksheets. I got an error message though with
With ActiveWorkbook.Worksheets(sWorksheet)
highlighted.
VBA
Option Explicit
Sub DataWipe()
Application.ScreenUpdating = False
Call DeleteRanges("Rev.")
Call DeleteRanges("COS")
Call DeleteRanges("Labor Dollars")
Call DeleteRanges("OE")
Call DeleteRanges("Labor Hours Hourly")
Call DeleteRanges("Labor Hours Salary")
Call DeleteRanges("Transfers")
Application.ScreenUpdating = True
End Sub
Private Sub DeleteRanges(sWorksheet As String)
With ActiveWorkbook.Worksheets(sWorksheet)
.Range("CA5:Z17").ClearContents
.Range("CA21:Z33").ClearContents
.Range("CA37:Z49").ClearContents
.Range("CA53:Z65").ClearContents
.Range("CA69:Z81").ClearContents
.Range("CA86:Z98").ClearContents
.Range("CA102:Z114").ClearContents
.Range("CA118:Z130").ClearContents
.Range("CA134:Z146").ClearContents
.Range("CA150:Z162").ClearContents
End With
End Sub
Thanks,
Rodney
Hoosier03
09-01-2011, 09:16 AM
The error says "Subscript out of range"
CatDaddy
09-01-2011, 10:47 AM
Sub DataWipe()
Application.ScreenUpdating = False
Dim ws as Worksheet
For each ws in ActiveWorkbook.Worksheets
ActiveWorkbook.ws.Activate
DeleteRanges
next ws
Application.ScreenUpdating = True
End Sub
Private Sub DeleteRanges()
With ActiveWorkbook.ActiveSheet
.Range("CA5:Z17").ClearContents
.Range("CA21:Z33").ClearContents
.Range("CA37:Z49").ClearContents
.Range("CA53:Z65").ClearContents
.Range("CA69:Z81").ClearContents
.Range("CA86:Z98").ClearContents
.Range("CA102:Z114").ClearContents
.Range("CA118:Z130").ClearContents
.Range("CA134:Z146").ClearContents
.Range("CA150:Z162").ClearContents
End With
End Sub
Hoosier03
09-01-2011, 11:00 AM
CatDaddy,
I got an error on this one that says "Object won't support this property or method."
Also, will this work for specific worksheets? I don't want every worksheet to have the data deleted only the ones I listed (Rev, COS, Labor Dollars, etc.)
CatDaddy
09-01-2011, 11:02 AM
no that will do all of the sheets...you could reference your sheets by numbers if the names arent working (from the code paul gave you)
Hoosier03
09-01-2011, 11:21 AM
The names did not work. How do I assign them a number? Is is just the order they are in the workbook?
Paul's code gave me the "Subscript out of range" error message.
Thanks,
Rodney
frank_m
09-01-2011, 11:38 AM
Pauls code runs fine for me.
"Subscript out of range" error occurs if one or more of the sheet names is spelled incorrectly within the code..
Hoosier03
09-01-2011, 11:58 AM
I checked the names of the worksheets twice and they are correct. The error message highlights "With ActiveWorkbook.Worksheets(sWorksheet)" not the names of the worksheets.
Greetings Hoosier,
Barring that there are leading/trailing spaces in the actual strings on the workbook tabs, I would look at whether the correct workbook is active.
Just by example/for testing:
Option Explicit
Sub DataWipe()
Dim wb As Workbook, wkb As Workbook
Const FILE_NAME As String = "MyBook.xls"
For Each wb In Workbooks
On Error Resume Next
Set wkb = Workbooks(FILE_NAME)
On Error GoTo 0
If Not wkb Is Nothing Then
Call DeleteRanges("Rev.", wkb)
Call DeleteRanges("COS", wb)
Call DeleteRanges("Labor Dollars", wkb)
Call DeleteRanges("OE", wkb)
Call DeleteRanges("Labor Hours Hourly", wkb)
Call DeleteRanges("Labor Hours Salary", wkb)
Call DeleteRanges("Transfers", wkb)
Exit Sub
End If
Next
MsgBox "It does not appear that " & FILE_NAME & " is open.", vbCritical, vbNullString
End Sub
Private Sub DeleteRanges(sWorksheet As String, wb As Workbook)
With wb.Worksheets(sWorksheet)
.Range("CA5:Z17").ClearContents
.Range("CA21:Z33").ClearContents
.Range("CA37:Z49").ClearContents
.Range("CA53:Z65").ClearContents
.Range("CA69:Z81").ClearContents
.Range("CA86:Z98").ClearContents
.Range("CA102:Z114").ClearContents
.Range("CA118:Z130").ClearContents
.Range("CA134:Z146").ClearContents
.Range("CA150:Z162").ClearContents
End With
End Sub
Where did you put the code, in a Standard Module as Paul said?
If yes, is the code in the same workbook as the worksheets are?
Hoosier03
09-01-2011, 02:14 PM
I entered everything you put in except I replaced "MyBook.xls" with the name of my file "Forecast Project.xlsm" I got the same error message.
This is the workbook that the worksheets are in. There are 14 worksheets in this workbook. The sheets listed are sheets 2 through 8. The code is in module 1 of the workbook that these worksheets are in.
Thanks,
Rodney
CatDaddy
09-01-2011, 02:35 PM
yes the numbers start at 1 at the leftmost side and onward, should eliminate whatever spelling errors you are having
Paul_Hossler
09-01-2011, 05:27 PM
Sorry you're having problems with my suggestions.
This has a little error catching. It might help track down the issue
Option Explicit
Sub DataWipe()
Application.ScreenUpdating = False
Call DeleteRanges("Sheet1")
Call DeleteRanges("Sheet2")
Call DeleteRanges("Sheet3")
Call DeleteRanges("Sheet4")
Call DeleteRanges("Sheet5")
Application.ScreenUpdating = True
End Sub
Private Sub DeleteRanges(sWorksheet As String)
Dim i As Long
i = -1
On Error Resume Next
i = ActiveWorkbook.Worksheets(sWorksheet).Index
On Error GoTo 0
If i = -1 Then
MsgBox "Sorry!!! --- '" & sWorksheet & "' does not exist"
Exit Sub
End If
With ActiveWorkbook.Worksheets(sWorksheet)
.Range("CA5:Z17").ClearContents
.Range("CA21:Z33").ClearContents
.Range("CA37:Z49").ClearContents
.Range("CA53:Z65").ClearContents
.Range("CA69:Z81").ClearContents
.Range("CA86:Z98").ClearContents
.Range("CA102:Z114").ClearContents
.Range("CA118:Z130").ClearContents
.Range("CA134:Z146").ClearContents
.Range("CA150:Z162").ClearContents
End With
End Sub
Hoosier03
09-01-2011, 05:41 PM
Thanks Paul. That one actually runs with no error message, but it does not delete the data.
Thanks Paul. That one actually runs with no error message, but it does not delete the data.
Please attach your workbook with the code as you currently have it. Redact any sensitive info, but leave the sheet layout and code as they are.
frank_m
09-01-2011, 08:16 PM
Those ranges seem a little backwards to me
It does still work for me in Excel 2003, my wild guess is that perhaps 2007 doesn't recognize those ranges.
Test
Range("CA5:Z17").Select 'Selects Range("Z5:CA17")<-is this the proper range to be cleared?
Those ranges seem a little backwards to me
It does still work for me in Excel 2003, my wild guess is that perhaps 2007 doesn't recognize those ranges.
Test
Range("CA5:Z17").Select 'Selects Range("Z5:CA17")<-is this the proper range to be cleared?
Nicely spotted:thumb
Hoosier:
Might this---> .Range("CA5:Z17").ClearContents
Supposed to be---> .Range("CA5:DZ17").ClearContents ???
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.