Loop not running through

**bk23** · 05-14-2019, 06:25 AM

Hi Forum experts, first post!

Trying to get 3 specified sheets from one work book copied over to another workbook.

Set up an array and loop and it runs through the first loop but gets stuck with a subscript out of range error at the Set in the second loop.

Dim array1(2) As String
array1(0) = "source1"
array1(1) = "source2"
array1(2) = "source3"

Dim mtabname As Worksheet
Dim i As Variant

For Each i In array1
Set mtabname = Worksheets(i)
Sheets(i).Select
Sheets(i).Copy after:=Workbooks("target.xlsm").Sheets(Workbooks("target.xlsm").Worksheets.Count)
Sheets(Sheets.Count).Name = i

Next

Any idea what I'm doing wrong?

**TMS** · 05-14-2019, 07:08 AM

I think this should do what you want … if that is to copy three sheets to the Target.xlsm workbook.

Sub Test()

Dim array1, i
array1 = Array("source1", "source2", "source3")

For Each i In array1
    ThisWorkbook.Sheets(i).Copy after:=Workbooks("target.xlsm").Sheets(Workbooks("target.xlsm").Worksheets.Count)
Next

End Sub

**Norie** · 05-14-2019, 07:30 AM

No need for a loop, this will copy 'source1', 'source2' and 'source3' from the active workbook to 'target.xlsm'.

array1(0) = "source1"
array1(1) = "source2"
array1(2) = "source3"

Sheets(array1).Copy after:=Workbooks("target.xlsm").Sheets(Workbooks("target.xlsm").Worksheets.Count)

**bk23** · 05-15-2019, 03:51 AM

used a variation of Norie's code, thanks to both!

**TMS** · 05-15-2019, 04:08 AM

You're welcome.

Loop not running through

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Loop not running through

Re: Loop not running through

Re: Loop not running through

Re: Loop not running through

Re: Loop not running through

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Loop keeps running

[SOLVED] VBA loop not running properly

[SOLVED] Loop not running on all worksheets

Do loop not running

Loop not running

For Each Loop - running three times

Bookmarks

Bookmarks

Posting Permissions