[RESOLVED] Moving worksheets from one app to another

[Botillier]

I am building a macro in which I need to move tabs from one open application to another. However, I only can use part of the application name, because certain aspects change regularly. At the same time there are several applications open at a time. I need to move a tab titled “MC” from an open application with part of the title being “CER” to another open application with part of the title being “MAC.” They are all Excel workbooks

[Botillier]

I am new to VB so this will be code and procedure that I am unfamiliar with. Using Excel, a minimum of two applications/workbooks will already be open and visible in the window menu at a time. I need my macro via VB to recognize one of the applications by only part of the name, for example the first three letters of the name of the app/workbook will be "CER" after my macro recognizes and activates this workbook I need it to move a tab/worksheet who's name is "MC" to another open app/workbook who's name has the first three letters starting with "MAC." It is important that VB recognizes just the first part of the workbook names, because the ends of the names are always changing. The worksheets however are simpler because the name is always same.

[Botillier]

I am sorry for confusion
One instance of Excel with multiple workbooks.

[Webtest]

Do you Open these 'applications' (Excel Workbooks?) in your code? If so, that makes it easier ... but in any case, it is not particularly difficult.

Option Explicit
Sub Macro1()
Dim aBook As Workbook    'Loop Index
Dim SrcBook As Workbook  'Source Workbook Handle
Dim DstBook As Workbook  'Destination Workbook Handle
Dim i As Integer         'Book Counter = 2 if 1 source book and 1 destination book

i = 0
For Each aBook In Application.Workbooks
    MsgBox aBook.Name  '<< TEST
    If InStr(1, aBook.Name, "CER") Then SrcBook = aBook: i = i + 1
    If InStr(1, aBook.Name, "MAC") Then DstBook = aBook: i = i + 1
Next
If i <> 2 Then MsgBox "We do NOT have proper Source and Destination Workbooks!": Exit Sub
'Do your sheet move here ... you can get this from recording a Macro
'SrcBook.Sheets(sheetname_stringvar) is how you reference the source sheet to be moved
'  or  SrcBook.Sheets("MC") ...
End Sub

Good Luck and Good Learning! I'll keep an eye on this thread.

[Botillier]

The user will open all of the necessary workbooks.

This type of code block is new to me. I don't quite understand what each line is doing, but I will try it to the best of my ability.

[Botillier]

I plugged in the code above, played it, it gave me three message boxes for the projects that are listed in the VB explorer window which includes the two correct workbooks, also Personal.xls which is where my macro is located, but is not a workbook. Then it went to debug mode with this:

InStr(1, aBook.Name, "CER") Then SrcBook = aBook: i = i + 1[/Highlight]

[Botillier]

If InStr(1, aBook.Name, "CER") Then SrcBook = aBook: i = i + 1

[Botillier]

"'SrcBook.Sheets(sheetname_stringvar) is how you reference the source sheet to be moved or SrcBook.Sheets("MC") ..."

After running a macro and plugging in the SrcBook.she....
This is what I have so far to move the sheet, how do I reference the destination workbook?

SrcBook.Sheets("MC").Move After:=Workbooks("fullWrkBkname").Sheets(3)

[Webtest]

Botillier ...

OK ... I forgot to put "Set" in my object assignments. I do that a LOT! I tested the code this time, in '97. I also added some comments and made the error detection a little more bullet-proof.

Option Explicit
Sub Macro1()
Dim aBook As Workbook    'Loop Index
Dim SrcBook As Workbook  'Source Workbook Handle
Dim DstBook As Workbook  'Destination Workbook Handle
Dim i As Integer         'Source Book counter
Dim j As Integer         'Destination Book counter

'Initialize the Book Flags
i = 0
j = 0
'Iterate through all open Workbooks
For Each aBook In Application.Workbooks
    'Demo code to show the name of every open workbook
    MsgBox aBook.Name  '<< TEST
    'Count all Source Books and set a handle
    If InStr(1, aBook.Name, "CER") Then Set SrcBook = aBook: i = i + 1
    'Count all Destination Books and set a handle
    If InStr(1, aBook.Name, "MAC") Then Set DstBook = aBook: j = j + 1
Next
'Make sure we only have 1 Source and 1 Destination books open
If (i = 1 And j = 1) Then
    MsgBox "We have our 2 books:  " & SrcBook.Name & "  " & DstBook.Name
Else
    MsgBox "We do NOT have proper Source and Destination Workbooks!": Exit Sub
End If

'Do your sheet move here ... you can get this from recording a Macro
'SrcBook.Sheets(sheetname_stringvar) is how you reference the source sheet to be moved
'  ... or ... SrcBook.Sheets("MC")
End Sub

Personal.xls IS a Workbook! Good Luck and Good Learning!

[Botillier]

Your code is good as far as I can tell, but I may have a problem on my end it tests good, but then returns a "We do NOT have proper Source and Destination Workbooks" Msg.

[Botillier]

Perhaps the VBAProject(PERSONAL.Xls) might be the problem, if it is necessary to only have two projects in the project window.

[Webtest]

Please do not dribble multiple messages for replies. This is not a Chat Room. Take your time and compose ONE sensible reply. That's what the 'Preview' button is for.

Remember that InStr is CASE SENSITIVE, and so your filenames have to reflect that. If you want them to be case INsensitive, use something like the following:

'Case INsensitive test for filename containing "CER"
InStr(1, LCase(aBook.Name), "cer")
    ... or
InStr(1, UCase(aBook.Name), "CER")

If this doesn't solve your problem, please post a list of ALL of the filenames reported in the popup boxes.

[Webtest]

Incidentally, you can have a hundred books open (well, memory does impose limits on most machines!), as long as only ONE contains the substring "CER" and only ONE contains the substring "MAC". (Gads! Here I am in chat mode!)

[Botillier]

Perfect!