F
Faraz A. Qureshi
Uptil now I had developed and been using the following code so as to create a
copy of the selected sheets to a new workbook without formulas. However,
today when I had some formulas with the INDIRECT function referring to other
sheets and names the result was an error.
Any guidance/suggestion/sample/advice in respect of making a code to copy
the entire RANGE (as by Ctrl+C mode) of the selected/grouped sheets
one-by-one and pasting the same specially (i.e. only values & formats) to a
new workbook's sheets, shall be highly obliged.
Thanx in advance.
Sub PstSpcl2(CONTROL As IRibbonControl)
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
ActiveWorkbook.Windows(1).SelectedSheets.Copy
ActiveWorkbook.Sheets.Select
For Each Sh In ActiveWorkbook.Sheets
SLC = Selection.Address
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range(SLC).Select
Next
ActiveSheet.Select
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
copy of the selected sheets to a new workbook without formulas. However,
today when I had some formulas with the INDIRECT function referring to other
sheets and names the result was an error.
Any guidance/suggestion/sample/advice in respect of making a code to copy
the entire RANGE (as by Ctrl+C mode) of the selected/grouped sheets
one-by-one and pasting the same specially (i.e. only values & formats) to a
new workbook's sheets, shall be highly obliged.
Thanx in advance.
Sub PstSpcl2(CONTROL As IRibbonControl)
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
ActiveWorkbook.Windows(1).SelectedSheets.Copy
ActiveWorkbook.Sheets.Select
For Each Sh In ActiveWorkbook.Sheets
SLC = Selection.Address
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range(SLC).Select
Next
ActiveSheet.Select
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub