Copy Selected Sheets' Data On New Workbook!

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top