Macro to move sheet to another workbook if sheet name is equal to specified cell value

[mack321]

This one is a little complex and I am in way over my head on this one, but have come too far to turn back now. What I have is...
On workbook "1" I have tabs named for the inspectors and their order numbers are in column A. Then in workbook "2" each tab contains order details and is named with the order number. I would like a macro that would move the worksheets with corresponding numbers to workbook "1" adjacent to the inspectors sheet that has the order #.
Can this even be accomplished?

 

[igold]

Hi mack321,

Yes this can be done, but it would be so much easier if you could be a drop more explicit with your question. For instance the actual names of Workbooks 1 & 2. Do either of the workbooks contain other tabs other than "Inspectors" and "order number" in books 1 & 2 respectively. Is there a header row on the "Inspectors" tabs.

If you could show us graphically what we are dealing with, then the saying that "A Picture Is Worth a Thousand Words" really holds true in this instance.

Regards,

igold

 

[mack321]

Hi mack321,

Yes this can be done, but it would be so much easier if you could be a drop more explicit with your question. For instance the actual names of Workbooks 1 & 2. Do either of the workbooks contain other tabs other than "Inspectors" and "order number" in books 1 & 2 respectively. Is there a header row on the "Inspectors" tabs.

If you could show us graphically what we are dealing with, then the saying that "A Picture Is Worth a Thousand Words" really holds true in this instance.

Regards,

igold

Workbook 1 named "Inspection Report" and Workbook 2 named "Inspector1". Inspection report does contain additional tabs while Inspector1 only contains tabs relevant to this macro. And, there is a header row on each Inspectors tab.

EXAMPLE OF INSPECTOR TAB (PO is the number to match to named tabs in Inspector1

[TABLE="width: 1149"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]PO #[/TD]
[TD]Pickup #[/TD]
[TD]Member[/TD]
[TD]Load #[/TD]
[TD]Buyer[/TD]
[TD]Inspector[/TD]
[TD]Commodity[/TD]
[TD]Routing[/TD]
[TD]Carrier[/TD]
[TD]Contact[/TD]
[TD]Phone[/TD]
[TD]Ship Date[/TD]
[TD]Del. Date[/TD]
[TD]Vendor[/TD]
[TD]Shipper[/TD]
[TD]Pickup City[/TD]
[TD]Pickup State[/TD]
[TD]Complete Y/N[/TD]
[/TR]
[TR]
[TD]136887[/TD]
[TD]136887[/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]Radishes[/TD]
[TD]Vendor[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]9/18/2015[/TD]
[TD="align: right"]9/18/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD]Hartville[/TD]
[TD]Ohio [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]136887[/TD]
[TD]136887[/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]Lett Leaf[/TD]
[TD]Vendor[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]9/18/2015[/TD]
[TD="align: right"]9/18/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD]Hartville[/TD]
[TD]Ohio [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]136887[/TD]
[TD]136887[/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]Herbs[/TD]
[TD]Vendor[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]9/18/2015[/TD]
[TD="align: right"]9/18/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD]Hartville[/TD]
[TD]Ohio [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]136794[/TD]
[TD]TO24080[/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]Pineapple[/TD]
[TD]Vendor[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]9/18/2015[/TD]
[TD="align: right"]9/21/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD]Philadelphia[/TD]
[TD]Pennsylvania[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[igold]

Ok, since the Inspection Report Workbook contains tabs other that just the Inspectors, I am assuming that at least some of the other tabs are named with their PO#s, but there could also be a "Totals" page in there as well. I need a way to tell Excel to distinguish an "Inspector Name" tab from say a "Totals" tab. If it was just Inspectors and PO#s that would work.

Otherwise I would need the names of the other tabs (not the PO#'s, since we are going to be adding to them), or I would need the names of the Inspectors. If there is someplace in the workbook the lists the Inspectors I could get it from there or if you want to list them on a worksheet for the purpose of this macro I could get it from there as well (you have to tell me where).

Additionally, just to be clear, from your sample above this Inspector tab has multiple PO#s, which PO# tab has priority to sit adjacent to this Inspector tab.

Am I understanding what you want and do you understand what I need to work the logic...

igold

 

[mack321]

Ok, since the Inspection Report Workbook contains tabs other that just the Inspectors, I am assuming that at least some of the other tabs are named with their PO#s, but there could also be a "Totals" page in there as well. I need a way to tell Excel to distinguish an "Inspector Name" tab from say a "Totals" tab. If it was just Inspectors and PO#s that would work.

Otherwise I would need the names of the other tabs (not the PO#'s, since we are going to be adding to them), or I would need the names of the Inspectors. If there is someplace in the workbook the lists the Inspectors I could get it from there or if you want to list them on a worksheet for the purpose of this macro I could get it from there as well (you have to tell me where).

Additionally, just to be clear, from your sample above this Inspector tab has multiple PO#s, which PO# tab has priority to sit adjacent to this Inspector tab.

Am I understanding what you want and do you understand what I need to work the logic...

igold

The inspector names are the sheet names in the Inspection Report workbook. What I could do in that workbook is hide the additional tabs, could we then tell the macro to only consider "unhidden" tabs? The first column is where the PO is contained that can be matched to the Inspector1 workbook.

 

[igold]

You might want to PM me and I will give you my email address. I think we could square this away better via email than the forum.

I would like a macro that would move the worksheets with corresponding numbers to workbook "1" adjacent to the inspectors sheet that has the order #

The macro you want to do is going to add tabs to the Inspection Report Workbook as PO#s. That would mean you would have always go in and hide the new tabs before every run of the macro..

 

[mack321]

You might want to PM me and I will give you my email address. I think we could square this away better via email than the forum.



The macro you want to do is going to add tabs to the Inspection Report Workbook as PO#s. That would mean you would have always go in and hide the new tabs before every run of the macro..

Yes, these reports are run fresh daily so it's not a big deal. I already have a 3 step macro that I can build that into.