how to code "if tab name starts with a string, then move these tabs to a new workbook"

**lynnsong986** · 10-21-2019, 10:16 PM

Hello,

I have a workbook with over 60 tabs, example of their names:

111100
111100A
111100Detail
111100B
213100
213100Detail
213100A
131460
131460Detail
312504
312504Detail
312504Ele
312504Wat
312504Gas

so they all start with a 6-digit code and they all have a code+Detail tab, but other than that, some can have more tabs and others don't. I have the list of the 6-digit on Sheet1 such as:

111100
213100
312504

what I need is for the codes to copy tabs starting with each of these values, i.e.

111100
111100A
111100Detail
111100B
will be copied to a new workbook

213100
213100Detail
213100A
will be copied to a new workbook

312504
312504Detail
312504Ele
312504Wat
312504Gas
will be copied to a new workbook

131460 will be copied at all because it is not on the list.

I don't know how to code this line: "if the tab name start with the cell value, then copy it", I'm not sure how to use * in this case. can someone please show me how to code this part?

thanks so much

**AlphaFrog** · 10-21-2019, 10:40 PM

Originally Posted by lynnsong986

I don't know how to code this line: "if the tab name start with the cell value, then copy it", I'm not sure how to use * in this case. can someone please show me how to code this part?

Try something like this...

 Dim ws As Worksheet
 
 
 For Each ws In ThisWorkbook.Worksheets
    If ws.Name Like cell.Value & "*" Then
        'copy it
    End If
 Next ws

**lynnsong986** · 10-21-2019, 10:55 PM

oh the like operator...thanks so much!!

how to code "if tab name starts with a string, then move these tabs to a new workbook"

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

how to code "if tab name starts with a string, then move these tabs to a new workbook"

Re: how to code "if tab name starts with a string, then move these tabs to a new workbook"

Re: how to code "if tab name starts with a string, then move these tabs to a new workbook"

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Macro to extract specific number from string starts with "18"

[SOLVED] Code "suddenly" starts giving me error 6 overflow

How to use InStr function to search for a text that starts with "CTF" and ends with "."?

create text string: "NOT "[b2value]", NOT "[b3value]", ..., NOT "[b600value]"

Remove the first and last group in the pivot field that starts with character ">" and "<"

[SOLVED] vba code to replace the column names for those column name "Proposal that starts with "1."

[SOLVED] How to "Move but don`t size with cells" objects on several tabs?

Bookmarks

Bookmarks

Posting Permissions