VBA Function
 

How to combine 3 macros to run together. First, I would like to unprotect
all sheets for which I am using the following macro:

Sub unprotectAll()
Dim myCount
Dim i
myCount = Application.Sheets.Count
Sheets(1).Select
For i = 1 To myCount
ActiveSheet.Unprotect Password:="1"
If i = myCount Then
End
End If
ActiveSheet.Next.Select
Next i
End Sub

Then, I would like to copy and paste values for the entire sheets 22-26 for
which I am trying to use this macro but getting errors:

Sub CopyRange()
Dim myCount
Dim i
myCount = Application.Sheets.Count
Sheets(22).Select
For i = 22 To myCount
ActiveSheet.Range("A1:IV5000").Paste values Destination:=Range("A1:IV5000")
End Sub

Then, I would like delete sheets 1-21, for which I am not sure about the
routine. Some important things to note are that the sheets are not called
€œsheet 1€�, €œsheet 2€�, etc, but have specific names. The sequence of the
sheets in the file does not change however, so I believe it would be simpler
to identify them by number, correct? Finally, how to run this as one
routine, rather than 3 separate macros?
--
iperlovsky

VBA Function
 

try this

Sub fixAll()'Notice the . dots
For i = 22 To sheets.count
with sheets(i)
.Unprotect Password:="1"
.UsedRange.value =.usedrange.value
.protect passwork:="1"
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"IPerlovsky" wrote in message
...
How to combine 3 macros to run together. First, I would like to unprotect
all sheets for which I am using the following macro:

Sub unprotectAll()
Dim myCount
Dim i
myCount = Application.Sheets.Count
Sheets(1).Select
For i = 1 To myCount
ActiveSheet.Unprotect Password:="1"
If i = myCount Then
End
End If
ActiveSheet.Next.Select
Next i
End Sub

Then, I would like to copy and paste values for the entire sheets 22-26
for
which I am trying to use this macro but getting errors:

Sub CopyRange()
Dim myCount
Dim i
myCount = Application.Sheets.Count
Sheets(22).Select
For i = 22 To myCount
ActiveSheet.Range("A1:IV5000").Paste values
Destination:=Range("A1:IV5000")
End Sub

Then, I would like delete sheets 1-21, for which I am not sure about the
routine. Some important things to note are that the sheets are not called
€œsheet 1€�, €œsheet 2€�, etc, but have specific names. The sequence of the
sheets in the file does not change however, so I believe it would be
simpler
to identify them by number, correct? Finally, how to run this as one
routine, rather than 3 separate macros?
--
iperlovsky

VBA Function
 

That function is giving me the following Compile error: Next without For.
Additionally, does this routine do all 3 tasks:
1) unprotect all sheets
2) copy and paste over with values for sheets 22-26
3) delete sheets 1-21

--
iperlovsky


"Don Guillett" wrote:

try this

Sub fixAll()'Notice the . dots
For i = 22 To sheets.count
with sheets(i)
.Unprotect Password:="1"
.UsedRange.value =.usedrange.value
.protect passwork:="1"
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"IPerlovsky" wrote in message
...
How to combine 3 macros to run together. First, I would like to unprotect
all sheets for which I am using the following macro:

Sub unprotectAll()
Dim myCount
Dim i
myCount = Application.Sheets.Count
Sheets(1).Select
For i = 1 To myCount
ActiveSheet.Unprotect Password:="1"
If i = myCount Then
End
End If
ActiveSheet.Next.Select
Next i
End Sub

Then, I would like to copy and paste values for the entire sheets 22-26
for
which I am trying to use this macro but getting errors:

Sub CopyRange()
Dim myCount
Dim i
myCount = Application.Sheets.Count
Sheets(22).Select
For i = 22 To myCount
ActiveSheet.Range("A1:IV5000").Paste values
Destination:=Range("A1:IV5000")
End Sub

Then, I would like delete sheets 1-21, for which I am not sure about the
routine. Some important things to note are that the sheets are not called
€œsheet 1€�, €œsheet 2€�, etc, but have specific names. The sequence of the
sheets in the file does not change however, so I believe it would be
simpler
to identify them by number, correct? Finally, how to run this as one
routine, rather than 3 separate macros?
--
iperlovsky

VBA Function
 

forgot the sheets delete part
Sub fixAll()'Notice the . dots
application.displayalerts=false
For i = 22 To sheets.count
with sheets(i)
.Unprotect Password:="1"
.UsedRange.value =.usedrange.value
.protect password:="1"
Next i

Sheets([Transpose(Row(1:21))]).delete

application.displayalerts=true
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
try this

Sub fixAll()'Notice the . dots
For i = 22 To sheets.count
with sheets(i)
.Unprotect Password:="1"
.UsedRange.value =.usedrange.value
.protect passwork:="1"
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"IPerlovsky" wrote in message
...
How to combine 3 macros to run together. First, I would like to
unprotect
all sheets for which I am using the following macro:

Sub unprotectAll()
Dim myCount
Dim i
myCount = Application.Sheets.Count
Sheets(1).Select
For i = 1 To myCount
ActiveSheet.Unprotect Password:="1"
If i = myCount Then
End
End If
ActiveSheet.Next.Select
Next i
End Sub

Then, I would like to copy and paste values for the entire sheets 22-26
for
which I am trying to use this macro but getting errors:

Sub CopyRange()
Dim myCount
Dim i
myCount = Application.Sheets.Count
Sheets(22).Select
For i = 22 To myCount
ActiveSheet.Range("A1:IV5000").Paste values
Destination:=Range("A1:IV5000")
End Sub

Then, I would like delete sheets 1-21, for which I am not sure about the
routine. Some important things to note are that the sheets are not
called
€œsheet 1€�, €œsheet 2€�, etc, but have specific names. The sequence of the
sheets in the file does not change however, so I believe it would be
simpler
to identify them by number, correct? Finally, how to run this as one
routine, rather than 3 separate macros?
--
iperlovsky

VBA Function
 

I forgot the end with
with sheets(i)
.Unprotect Password:="1"
.UsedRange.value =.usedrange.value
.protect passwork:="1"
END WITH
Next i

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"IPerlovsky" wrote in message
...
That function is giving me the following Compile error: Next without For.
Additionally, does this routine do all 3 tasks:
1) unprotect all sheets
2) copy and paste over with values for sheets 22-26
3) delete sheets 1-21

--
iperlovsky


"Don Guillett" wrote:

try this

Sub fixAll()'Notice the . dots
For i = 22 To sheets.count
with sheets(i)
.Unprotect Password:="1"
.UsedRange.value =.usedrange.value
.protect passwork:="1"
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"IPerlovsky" wrote in message
...
How to combine 3 macros to run together. First, I would like to
unprotect
all sheets for which I am using the following macro:

Sub unprotectAll()
Dim myCount
Dim i
myCount = Application.Sheets.Count
Sheets(1).Select
For i = 1 To myCount
ActiveSheet.Unprotect Password:="1"
If i = myCount Then
End
End If
ActiveSheet.Next.Select
Next i
End Sub

Then, I would like to copy and paste values for the entire sheets 22-26
for
which I am trying to use this macro but getting errors:

Sub CopyRange()
Dim myCount
Dim i
myCount = Application.Sheets.Count
Sheets(22).Select
For i = 22 To myCount
ActiveSheet.Range("A1:IV5000").Paste values
Destination:=Range("A1:IV5000")
End Sub

Then, I would like delete sheets 1-21, for which I am not sure about
the
routine. Some important things to note are that the sheets are not
called
€œsheet 1€�, €œsheet 2€�, etc, but have specific names. The sequence of
the
sheets in the file does not change however, so I believe it would be
simpler
to identify them by number, correct? Finally, how to run this as one
routine, rather than 3 separate macros?
--
iperlovsky

VBA Function
 

Sub fixAll()'Notice the . dots
application.displayalerts=false
For i = 22 To sheets.count
with sheets(i)
.Unprotect Password:="1"
.UsedRange.value =.usedrange.value
.protect password:="1"
end with
Next i

Sheets([Transpose(Row(1:21))]).delete

application.displayalerts=true
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...

forgot the sheets delete part
Sub fixAll()'Notice the . dots
application.displayalerts=false
For i = 22 To sheets.count
with sheets(i)
.Unprotect Password:="1"
.UsedRange.value =.usedrange.value
.protect password:="1"
Next i

Sheets([Transpose(Row(1:21))]).delete

application.displayalerts=true
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
try this

Sub fixAll()'Notice the . dots
For i = 22 To sheets.count
with sheets(i)
.Unprotect Password:="1"
.UsedRange.value =.usedrange.value
.protect passwork:="1"
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"IPerlovsky" wrote in message
...
How to combine 3 macros to run together. First, I would like to
unprotect
all sheets for which I am using the following macro:

Sub unprotectAll()
Dim myCount
Dim i
myCount = Application.Sheets.Count
Sheets(1).Select
For i = 1 To myCount
ActiveSheet.Unprotect Password:="1"
If i = myCount Then
End
End If
ActiveSheet.Next.Select
Next i
End Sub

Then, I would like to copy and paste values for the entire sheets 22-26
for
which I am trying to use this macro but getting errors:

Sub CopyRange()
Dim myCount
Dim i
myCount = Application.Sheets.Count
Sheets(22).Select
For i = 22 To myCount
ActiveSheet.Range("A1:IV5000").Paste values
Destination:=Range("A1:IV5000")
End Sub

Then, I would like delete sheets 1-21, for which I am not sure about the
routine. Some important things to note are that the sheets are not
called
€œsheet 1€�, €œsheet 2€�, etc, but have specific names. The sequence of the
sheets in the file does not change however, so I believe it would be
simpler
to identify them by number, correct? Finally, how to run this as one
routine, rather than 3 separate macros?
--
iperlovsky

VBA Function
 

thanks that worked without a hitch!
--
iperlovsky


"Don Guillett" wrote:

Sub fixAll()'Notice the . dots
application.displayalerts=false
For i = 22 To sheets.count
with sheets(i)
.Unprotect Password:="1"
.UsedRange.value =.usedrange.value
.protect password:="1"
end with
Next i

Sheets([Transpose(Row(1:21))]).delete

application.displayalerts=true
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...

forgot the sheets delete part
Sub fixAll()'Notice the . dots
application.displayalerts=false
For i = 22 To sheets.count
with sheets(i)
.Unprotect Password:="1"
.UsedRange.value =.usedrange.value
.protect password:="1"
Next i

Sheets([Transpose(Row(1:21))]).delete

application.displayalerts=true
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
try this

Sub fixAll()'Notice the . dots
For i = 22 To sheets.count
with sheets(i)
.Unprotect Password:="1"
.UsedRange.value =.usedrange.value
.protect passwork:="1"
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"IPerlovsky" wrote in message
...
How to combine 3 macros to run together. First, I would like to
unprotect
all sheets for which I am using the following macro:

Sub unprotectAll()
Dim myCount
Dim i
myCount = Application.Sheets.Count
Sheets(1).Select
For i = 1 To myCount
ActiveSheet.Unprotect Password:="1"
If i = myCount Then
End
End If
ActiveSheet.Next.Select
Next i
End Sub

Then, I would like to copy and paste values for the entire sheets 22-26
for
which I am trying to use this macro but getting errors:

Sub CopyRange()
Dim myCount
Dim i
myCount = Application.Sheets.Count
Sheets(22).Select
For i = 22 To myCount
ActiveSheet.Range("A1:IV5000").Paste values
Destination:=Range("A1:IV5000")
End Sub

Then, I would like delete sheets 1-21, for which I am not sure about the
routine. Some important things to note are that the sheets are not
called
€œsheet 1€�, €œsheet 2€�, etc, but have specific names. The sequence of the
sheets in the file does not change however, so I believe it would be
simpler
to identify them by number, correct? Finally, how to run this as one
routine, rather than 3 separate macros?
--
iperlovsky

VBA Function
 

I'm glad this old man finally got it together for you.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"IPerlovsky" wrote in message
...
thanks that worked without a hitch!
--
iperlovsky


"Don Guillett" wrote:

Sub fixAll()'Notice the . dots
application.displayalerts=false
For i = 22 To sheets.count
with sheets(i)
.Unprotect Password:="1"
.UsedRange.value =.usedrange.value
.protect password:="1"
end with
Next i

Sheets([Transpose(Row(1:21))]).delete

application.displayalerts=true
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...

forgot the sheets delete part
Sub fixAll()'Notice the . dots
application.displayalerts=false
For i = 22 To sheets.count
with sheets(i)
.Unprotect Password:="1"
.UsedRange.value =.usedrange.value
.protect password:="1"
Next i

Sheets([Transpose(Row(1:21))]).delete

application.displayalerts=true
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
try this

Sub fixAll()'Notice the . dots
For i = 22 To sheets.count
with sheets(i)
.Unprotect Password:="1"
.UsedRange.value =.usedrange.value
.protect passwork:="1"
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"IPerlovsky" wrote in message
...
How to combine 3 macros to run together. First, I would like to
unprotect
all sheets for which I am using the following macro:

Sub unprotectAll()
Dim myCount
Dim i
myCount = Application.Sheets.Count
Sheets(1).Select
For i = 1 To myCount
ActiveSheet.Unprotect Password:="1"
If i = myCount Then
End
End If
ActiveSheet.Next.Select
Next i
End Sub

Then, I would like to copy and paste values for the entire sheets
22-26
for
which I am trying to use this macro but getting errors:

Sub CopyRange()
Dim myCount
Dim i
myCount = Application.Sheets.Count
Sheets(22).Select
For i = 22 To myCount
ActiveSheet.Range("A1:IV5000").Paste values
Destination:=Range("A1:IV5000")
End Sub

Then, I would like delete sheets 1-21, for which I am not sure about
the
routine. Some important things to note are that the sheets are not
called
€œsheet 1€�, €œsheet 2€�, etc, but have specific names. The sequence of
the
sheets in the file does not change however, so I believe it would be
simpler
to identify them by number, correct? Finally, how to run this as one
routine, rather than 3 separate macros?
--
iperlovsky