VBA - Move worksheet (based on name) to another open workbook

[Ric1889]

Hello all,

I'm attempting to perform a relatively straightforward procedure in VBA with a certain nuance which is complicating things, my basic task is to Move a selection of tabs from one open workbook ('all.xls') to a new workbook also open ('new.xls').

However, where this becomes complicated is the criteria upon which a tab is deemed part of said selection to be moved.

For examples sake, lets say there are two 'selections' of tabs, one called Dog and one called Cat (we'll focus on 'Dog' here...)

Each of these tabs is named as follows - Dog, Dog(1), Dog(2), Dog(3) etc etc (i.e - the naming convention generated by Excel when you try to drop lots of worksheets with the same name into one workbook).

So, I want to move all the 'Dog' tabs into the new (already open) workbook. However, sometimes the range will be from Dog to Dog(20), sometimes from Dog to Dog(11) etc - ie, there will be different numbers of tabs each month.

I tried to create an array of tabs to move in VBA, however when the number of tabs there are changes (or rather, is less than that defined in the array) then VBA clicks into debug mode as obviously it cant find the referenced tab to move. Further, if there are more 'Dog' tabs than defined in the array then some will not be moved into the new workbook.

In my head, the solution to this is to somehow get the code to search for tabs starting with 'Dog' and move them, but I have no idea how to do this.

Any ideas?

Please let me know if I can provide info, or if the above doesn't make sense.

Thanks,
Richard

 

[kpark91]

Could you post the code that you've worked on so far?
As it is pain to write the code from scratch..

 

[doofusboy]

Have you tried something like this [customize to your workbooks]?

Dim ws As Worksheet
    For Each ws In Worksheets
        If Mid(ws.Name, 1, 3) = "Dog" Then
            ' your move code here
        End If
    Next ws

 

[Ric1889]

ok, trying to use doofusboys' code, however (I should have pre-emptively explained this) my comprehension of VB syntax is poor, I attempted to do this -

For Each ws In Worksheets
If Mid(ws.Name, 1, 15) = "COM_Conveniance" Then
Move Before:=Workbooks("990000 - Canning BU.xls").Sheets(1)
End If
Next ws

However, VBA debugs at my attempt to insert my specifications.

In my mind, the line highlighted bold was going to move the ws into the new book....

 

[Ric1889]

Kpark, for reference my entire code is;

On Error Resume Next
Workbooks.Open Filename:="XXXXXX", Password:="XXXXXXX"

Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In Worksheets
If ws.Name <> "Sheet1" Then ws.Delete
Next
Application.DisplayAlerts = True
Windows("Packs - All.xls").Activate


For Each ws In Worksheets
If Mid(ws.Name, 1, 15) = "COM_Conveniance" Then
Move Before:=Workbooks("990000 - Canning BU.xls").Sheets(1)
End If
Next ws



the code 'seems' ok up until my attempt to move the newly specified ws

 

[doofusboy]

Doesn't look like you told the macro WHAT to move. maybe something like this?

For Each ws In Worksheets
    If Mid(ws.Name, 1, 15) = "COM_Conveniance" Then
        [COLOR=red][B]ws.[/B][/COLOR]Move Before:=Workbooks("990000 - Canning BU.xls").Sheets(1)
    End If
Next ws

 

[Ric1889]

Doesn't look like you told the macro WHAT to move. maybe something like this?

For Each ws In Worksheets
    If Mid(ws.Name, 1, 15) = "COM_Conveniance" Then
        [COLOR=red][B]ws.[/B][/COLOR]Move Before:=Workbooks("990000 - Canning BU.xls").Sheets(1)
    End If
Next ws

It works!

Thanks for your help mate.


Edit - one last thing (which is perhaps unrelated) - when the sheets are copied over I get the message -

"a formula or sheet you want to move or copy contains the name 'xxxx' which already exists on the destination worksheet. Do you want to use this version of the name"

Is there a way I can get it to automatically say 'yes' to this rather than have to click yes over and over again?

(nb - this is a general message I always get when copying these tabs over, not one produced by the code above)