CODEWITHSUNDEEP
arraysexcelvba
nikhil kumar
nikhil kumar

Reputation: 53

Copy multiple sheets multiple times

Thanks for building up a strong community for novice people like us.

I was trying to build up a macro which copies multiple selected sheets multiple times based on user input through google search and I am facing some trouble.

Issue here is that while the code does copy multiple sheets multiple times but it loses linking.

For example - I have sheets titled as A, B and C in the workbook and sheet B has some cells linked to sheet A, similarly Sheet C has some values linked to sheet B, when this macro is used, it creates copies of the sheets one at a time. So if I mention 4 copies after selecting Sheet B and C, it will create 1st copy of B, then 1st copy of C, then second copy of B and then second copy of C and so on till the loop ends.

However what I want is that It selects the two sheets together and then create copies. This is because when we manually do it, the linking in the sheets gets revised to the newly created sheets. What I mean here is that when we select Sheet B and C and then manually create a copy, Sheet C will show linking to newly created sheet B.

I am not sure if this could be done through VBA but posts show that this can be done through some array function which I am not aware of.

Any help will be much appreciated.

I am not sure how to add a file or to add a code that I have to this forum I have hence added it here

Sub MultiSheetArray()

'allows you to store an array of sheets

Dim ws As Worksheet

Dim ShtArray() As String

Dim intA As Integer

Dim intB As Integer
Dim myArray() As Variant

 Dim i As Long
 On Error GoTo endit
 Application.ScreenUpdating = False
 shts = InputBox("How many times")

' First you need to enter the sheet names into an array

For Each ws In ActiveWindow.SelectedSheets

 intA = intA + 1

 ReDim Preserve ShtArray(intA)

 ShtArray(intA) = ws.Name

Next ws



' Now list the sheets we entered into our array "shtArray"

 For i = 1 To shts

For intB = 1 To intA

ActiveWorkbook.Worksheets(myArray(x)).Copy after:=ActiveSheet

Next intB

 Next i
 Application.ScreenUpdating = True
endit:


End Sub

Thanks in advance.

Upvotes: 1

Views: 518

Answers (1)

VBasic2008
VBasic2008

Reputation: 54807

Copy Multiple Instances of Selected Worksheets

  • To test this procedure, open a new workbook. In VBE CRTL+F11, insert a standard module and copy the code into it. Add a few worksheets. Now select some of them by clicking on the tab for the first one and CRTL-clicking on any others' tab which will create a group of worksheets. Now run the procedure which will firstly ask "How many times". For the first time do not enter more than 2 and press ENTER and see what has happened.

The Code

Option Explicit

' Copies selected sheets multiple times after the last sheet.
Sub MultiSheetArray()
    
    On Error GoTo endit
    Application.ScreenUpdating = False
    
    ' Input number of copies.
    Dim shts As Long
    shts = InputBox("How many times")
    
    ' Write the names of the selected sheets to an array.
    Dim sh As Object
    Dim ShtArray() As String
    ReDim ShtArray(1 To ActiveWindow.SelectedSheets.Count)
    Dim i As Long
    For Each sh In ActiveWindow.SelectedSheets
        i = i + 1
        ShtArray(i) = sh.Name
    Next sh
    
    ' Copy sheets after last sheet.
    For i = 1 To shts
        With ActiveWorkbook
            .Sheets(ShtArray).Copy After:=.Sheets(.Sheets.Count)
        End With
    Next i
    
    Application.ScreenUpdating = True

endit:

End Sub

Upvotes: 3

Related Questions