2

I need to copy paste all my Worksheet name in the current workbook to a new workbook with same worksheet names. (without the datas. I only need the worksheet names.)

I tried following VBA but it shows the error

"The name is already taken." (Runtime Error 1004)

'Create new work book for Pivot
Dim Source As Workbook
Dim Pivot As Workbook
Set Source = ActiveWorkbook
Set Pivot = Workbooks.Add

Dim ws As Worksheet
For Each ws In Worksheets
    'Create new worksheet in new excel
    Dim Line As String
    Line = ActiveSheet.Name
    Pivot.Activate
    Sheets.Add
    ActiveSheet.Name = Line
    Source.Activate
Next
Community
  • 1
  • 1
shamli
  • 23
  • 3

1 Answers1

1

You never use ws so Line never changes. Also you do not need to select or activate anything. Finally, you should qualify your Worksheets etc with the workbook they come from.

Dim Source As Workbook
Dim Pivot As Workbook
Set Source = ActiveWorkbook
Set Pivot = Workbooks.Add

Dim ws As Worksheet
For Each ws In Source.Worksheets
    Pivot.Worksheets.Add.Name = ws.Name
Next

That will not protect you from a situation where there is already a sheet in the new workbook with the name identical to one of your sheets name (e.g. Sheet1), and it will leave any sheets the new workbook has by default (controlled by the Application.SheetsInNewWorkbook property).

GSerg
  • 76,472
  • 17
  • 159
  • 346
  • _You never use ws so Line never changes_ I didn't understand.. :( Cant we use Dim inside the loop, so that for each time it repeats a new variable will be put in the Dim.. Sorry for my language... I am trying to create a Pivot table from the Datas in Source in workbook. So far I am able to insert Pivot in every worksheet using loop. Now I am trying to create a separate Workbook for Pivot only. – shamli Dec 20 '17 at 07:07
  • @shamli You store `ActiveSheet.Name` in `Line`. `ActiveSheet` is the active sheet in `Source`. You never change the active sheet in `Source`, so `Line` never changes either. – GSerg Dec 20 '17 at 07:15
  • @shamli There is a worksheet collection in Excel (list of all the worksheet present in the workbook) which you have iterated using the `For Each` loop. What he meant was you iterated each `Worksheet` object but never really used or reference to it in your code within the loop and continuously used the `Activesheet` which as he already explained, never changed in the `Source` workbook. And since you used the `For Each` loop you need not use `Dim` since `ws` automatically reference to the next worksheet object in the collection. – L42 Dec 20 '17 at 07:17
  • Thanks @GSerg Thank you so much for helping me Appreciate it. – shamli Dec 20 '17 at 07:27
  • @L42, Now it is very much clear, As I told earlier, I am new to VBA. :) There are much to learn, I greatly appreciate the time you've taken to share your knowledge with me. – shamli Dec 20 '17 at 07:30