Move array of worksheets into another workbook

**jaryszek** · 08-16-2019, 06:06 AM

Hi Guys,

i want to select 3 dynamic worksheets names into another workbook:

So something like:

    Sheets(Array("Customer", "Development")).Select
    Sheets(Array("Customer", "Development")).move

but in "Customer" and "Development" i can have dynamic changed variable.
So worksheets can have different names.

How can i pass worksheet variables into this method?
I didnt found solution in web...

Please help,
Jacek

**jindon** · 08-16-2019, 07:03 AM

If you know sheet index of the worksheets then

Sheets(Array(1,2,3)).Move

will move left 3 worksheets.

Or simply use array variable like

Dim wsArray
wsArray=Array("Blabl1","Blabla2","etc")
Sheets(myArray).Move

**Kaper** · 08-16-2019, 07:44 AM

You can use standard array of strings to do that.
And any array element can be directly filled with sheet-to-be-moved name, like (just illustrative, simplest case):

Dim arr(1 To 3) As String
arr(1) = "Arkusz1"
arr(2) = "Arkusz2"
arr(3) = "Arkusz3"
Sheets(arr).Move

but of course you can first

Dim arr() As String, sheetstobemoved as long
redim arr(1 to 1)

and then dynamically grow the array

if someconditionformoving then
  sheetstobemoved = sheetstobemoved +1
  redim preserve arr(1 to sheetstobemoved) 
  arr(sheetstobemoved) = activesheet.name
end if

and finally, once the whole arr is ready move all "collectred" sheets:

Sheets(arr).Move

**jaryszek** · 08-19-2019, 04:00 AM

Thank you Guys,

very helpful.

Jacek

Move array of worksheets into another workbook

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Move array of worksheets into another workbook

Re: Move array of worksheets into another workbook

Re: Move array of worksheets into another workbook

Re: Move array of worksheets into another workbook

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] move all visible worksheets in a workbook all into new single workbook

move visible of worksheets in a workbook all into new workbook

Macro to move worksheets from a workbook to a new workbook

[SOLVED] VBA code to create a workbook and copy or move worksheets in to workbook through loop

How to move several worksheets in one workbook to several different workbooks

Move WorkSheets Within Same WorkBook!!

Move worksheets to a new workbook

Bookmarks

Bookmarks

Posting Permissions