Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |