copy several sheets in workbook and move to one workbook

[camandab]

Is there a way I can grab multiple sheets that contain the same name (in this case "*Dec*") and copy into a new workbook?

This is what I have so far but it copies the sheets into separate workbooks. Please help!



Dim i As Integer

Application.DisplayAlerts = False

For i = Worksheets.Count To 1 Step -1

If Sheets(i).Name Like "Dec*" Then Sheets(i).Copy
Next

 

[Jon von der Heyden]

Try;

[COLOR=blue]Option[/COLOR] [COLOR=blue]Explicit[/COLOR]
[COLOR=#0000ff][/COLOR] 
[COLOR=blue]Sub[/COLOR] MoveSheets()
 
    [COLOR=blue]Dim[/COLOR] wkbSource [COLOR=blue]As[/COLOR] Workbook, wkbDestination [COLOR=blue]As[/COLOR] Workbook
    [COLOR=blue]Dim[/COLOR] wks [COLOR=blue]As[/COLOR] Worksheet
    [COLOR=blue]Const[/COLOR] strName [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR] = "dec"
 
    [COLOR=blue]With[/COLOR] Application
        .ScreenUpdating = [COLOR=blue]False[/COLOR]
        .EnableEvents = [COLOR=blue]False[/COLOR]
    [COLOR=blue]End[/COLOR] [COLOR=blue]With[/COLOR]
 
    [COLOR=blue]Set[/COLOR] wkbSource = ThisWorkbook
    [COLOR=blue]Set[/COLOR] wkbDestination = Workbooks.Add
 
    [COLOR=blue]For[/COLOR] [COLOR=blue]Each[/COLOR] wks [COLOR=blue]In[/COLOR] wkbSource.Worksheets
        [COLOR=blue]If[/COLOR] [COLOR=blue]InStr[/COLOR]([COLOR=blue]LCase$[/COLOR](wks.Name), strName) > 0 [COLOR=blue]Then[/COLOR]
            wks.Copy Before:=wkbDestination.Sheets(1)
        [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
    [COLOR=blue]Next[/COLOR] wks
 
    [COLOR=blue]With[/COLOR] Application
        .ScreenUpdating = [COLOR=blue]True[/COLOR]
        .EnableEvents = [COLOR=blue]True[/COLOR]
    [COLOR=blue]End[/COLOR] [COLOR=blue]With[/COLOR]
 
    [COLOR=blue]Set[/COLOR] wks = [COLOR=blue]Nothing[/COLOR]
    [COLOR=blue]Set[/COLOR] wkbSource = [COLOR=blue]Nothing[/COLOR]
    [COLOR=blue]Set[/COLOR] wkbDestination = [COLOR=blue]Nothing[/COLOR]
[COLOR=blue][/COLOR] 
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

 

[camandab]

Thanks for the quick reply!!
This code works to create a new workbook but it is not copying the sheets the way I would expect. It may be because the names vary between the worksheets. For example, I may have a sheet called "Dec 09" and another sheet called "Dec ck reg". How would I grab both of those sheets and copy into the same new workbook? I will have to do this for several files within a folder and the worksheet names can very quite a bit but should ultimately contain some form of "Dec" in the sheet name.

 

[Norie]

Try something like this, though you need to clarify exactly what you are trying to do.

You seem to be mixing up worksheets and workbooks, and perhaps even copying and moving.

Option Explicit
Sub CrtDecWb()
Dim ws As Worksheet
Dim arrShts()
Dim I As Long
 
    For Each ws In Worksheets
        If LCase(ws.Name) Like "*dec*" Then
            ReDim Preserve arrShts(I)
            arrShts(I) = ws.Name
            I = I + 1
        End If
    Next ws
    
    Worksheets(arrShts()).Copy
    
End Sub

 

[camandab]

Thanks Norie, that worked like a charm!

 

[camandab]

I'm getting stuck on opening all the workbooks in a folder to perform this action. Essentially here's what I need to do.

1. Open each workbook in folder "Recs 2009"
2. Copy "Dec" worksheets into new workbook (using code from this post)
3. Copy Sheet 1 within the new workbook to the end of the workbook
4. Save workbook as a value found in the last sheet and within another folder called "Recs 2010" (code I have uses 'Cells.Find)
5. Close the newly created workbook and open the next workbook in the "Recs 2009" folder.

Any ideas?

 

[Norie]

This is totally different from what you originally asked.

What do you mean by 'Dec' worksheets?

Sheets with that as part of the name?

 

[camandab]

Yes it is different but I was trying to do the code in piecemeal with little success.

But yes, any sheet containing "Dec" needs to be copied. I've struggled with this issue because there are a couple of way to accomplish what I need and I don't know which would be easier to code.

Option 1: Do step 1-5 as I described above or
Option 2: Copy all workbooks as is from folder "Recs 2009" to folder "Recs 2010" and open each worksheet and keep only sheets that contain "Dec".

Thoughts? Thanks!