I am basically extracting a few sheets from a big workbook into a new workbook. The main reason for wanting to copy all these sheets at one go is because of the "Named Ranges" errors i keep getting if i do copy them one by one.
For example: Once i have copied the first sheet in the new workbook, lets say Book1.xls
Then when i go onto copy the next sheet, it starts giving me error "Named Range" exists...
If there is away to avoid it then, running a loop to copy over sheets would be much desirable?
Are you copying sheets with red Tabs? If so then try this
- Option Explicit
- Public Sub CopyREDTABS2()
- Dim WBSource As Workbook
- Dim sht As Worksheet
- Set WBSource = ActiveWorkbook
- For Each sht In WBSource.Sheets
- ' Debug.Print Sheet.Name
- If sht.Tab.Color = vbRed Then sht.Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
- Call Copypastevalues
- Next Sheet
- End Sub