Copy multiple sheets data with format to another workbook

[arijitirf]

I am trying to copy first 200 sheets with same format to another workbook with each of the copied worksheet to have their own tab with the same sheet name.

I am not expert in VBA so I have tired many vba script found using google but can't figure it out.

In my excel there are about 1200 sheets from which I want to make 6 different workbook using 200 sheets for each workbook. Now for the first workbook I need to copy first 200 sheets and paste it a workbook with same format and sheets name. In same manner the second workbook will be created from next 200 sheets and go on.

Help required.

Thanks in advance.

 

[Fluff]

How about

Sub copyShts()
   Dim ary() As Variant
   Dim i As Long, j As Long
   For i = 0 To ThisWorkbook.Sheets.Count - 1 Step 200
      For j = 1 To 200
         ReDim Preserve ary(1 To j)
         ary(j) = ThisWorkbook.Sheets(i + j).Name
      Next j
      ThisWorkbook.Sheets(ary).Copy
      ReDim ary(1 To 1)
   Next i
End Sub

 

[arijitirf]

Thank you so much. Works fine and this is what I actually need.

 

[arijitirf]

Sir, I am searching one more thing and posted the same on this forum and awaiting reply. Could you please help me on this (link below)

https://www.mrexcel.com/forum/excel...ring-paste-another-excel-sheet-reference.html

Thanks once again.

 

[Fluff]

Thank you so much. Works fine and this is what I actually need.

Glad to help & thanks for the feedback