Sheets Array

[Boffa]

Hi guys, have had a look online but couldn't find anything particular to my situation.

I want to copy an array of sheets where the sheet name ends in the word Log - The Sheet name could have anything at the start of the sheet name but it ends in Log - usually there will only be 2 sheets that end in the name 'log' but there are times there is only one hence would like to copy an array

below is what I have attempted but does not work

Sheets(Array("*LOG")).Copy

Any assistance would be appreciated..

 

[Michael M]

I think you will need to loop through the sheets to copy them....something like this

Sub MM2()
Dim ws As Worksheet
For Each ws In Worksheets
    If Right(ws.Name, 3) = "LOG" Then
        ws.Copy
        'Do more code here
    End If
Next ws
End Sub

 

[Peter_SSs]

I think you will need to loop through the sheets to copy them....something like this

Sub MM2()
Dim ws As Worksheet
For Each ws In Worksheets
    If Right(ws.Name, 3) = "LOG" Then
        ws.Copy
        'Do more code here
    End If
Next ws
End Sub

That would create one new workbook for each 'LOG' sheet whereas the OP's attempted code appears to be trying to create a new workbook containing all 'LOG' sheets.

My suggestion (also allowing for upper/lower case letters for the 'LOG' sheets) is:

Sub CopyLogSheets()
  Dim ws As Worksheet
  Dim LogSheets As String
  
  For Each ws In Worksheets
    If UCase(Right(ws.Name, 3)) = "LOG" Then LogSheets = LogSheets & "/" & ws.Name
  Next ws
  If Len(LogSheets) > 0 Then Sheets(Split(Mid(LogSheets, 2), "/")).Copy
End Sub

 

[Michael M]

@Peter_SSs
I disagree ...the one line in my code that allows the OP to decide what they want to do is

'Do more code here

They can then either save each sheet to a new workbook or each sheet to the one new workbook.
I don't think I specified in the code anywhere that it was a new workbook for each worksheet, I simply said

ws.copy

 

[dmt32]

You can build an array to copy required sheets

Untested but maybe something like following

Sub CopyLogSheets()
    Dim SheetsArray() As String
    Dim i As Integer
    Dim sh As Worksheet

    For Each sh In ThisWorkbook.Worksheets
        If UCase(sh.Name) Like "*LOG" Then
        i = i + 1
        ReDim Preserve SheetsArray(1 To i)
        SheetsArray(i) = sh.Name
        End If
    Next sh

    If i > 0 Then Worksheets(SheetsArray()).Copy
End Sub

Dave

 

[Peter_SSs]

I don't think I specified in the code anywhere that it was a new workbook for each worksheet, I simply said

ws.copy

But that is exactly what ws.Copy in a loop will do.

However, I am happy to wait & see if any of the suggestions is actually what the OP wants.

 

[Boffa]

Thanks for the quick reply's guys much appreciated

dmt32 your code worked perfectly for what i need it to do

 

[dmt32]

Thanks for the quick reply's guys much appreciated

dmt32 your code worked perfectly for what i need it to do

Glad forum was able to help you & found a solution - many thanks for feedback, very much appreciated by all.

Dave

 

[Peter_SSs]

Thanks for the quick reply's guys much appreciated

dmt32 your code worked perfectly for what i need it to do

Glad you got something that worked for you. I agree that dmt32's code does what you wanted, though I believe the code I posted does also.