Very complicated, copy and paste sheets in a loop

[ss6857]

Hello everyone. I run a report where there is a summary report and then a report for individuals. WHen the report is ran, it adds sheets to the active workbook and the sheets are called the persons name. So by the end of the report I have a sheet for (for example):

Smith, Joe
Johnson, Sue
Wagner, Henry
Armstrong, Jerry
Summary

I would then like to extract the individual's sheet along with the summary and save it in a new workbook then save it. So I would have four workbooks including:

Smith, Joe and Summary
Johnson, Sue and Summary
Wagner, Henry and Summary
Armstrong, Jerry and Summary

However I am having a hard time extracting the individual sheet. To name the sheets, a range in a control sheet has the list of names and it loops to each name. I would like to use that same range and loop through to identify the name of the sheet and extract it.

Dim originalBook As String
Dim ws As Worksheet

originalBook = ThisWorkbook.Name

Set vRegRg = Sheets("Library").Range("B2").End(xlDown)
For Each vRegRg In Sheets("Library").Range("B2:B15")
Workbooks.Add
Workbooks(originalBook).Sheets("Master").Copy Before:=ActiveWorkbook.Sheets(1)
For Each ws In ThisWorkbook.Worksheets
ws.Activate
With ActiveSheet.Name = Range(vRegRg)
.Value
.Copy Before:=ActiveWorkbook.Sheets(2)
End With
Next ws
Next


Thank you for any and all help.

[mikerickson]

You have another list of people, the names of the sheets. Perhaps something like this will work
Dim oneSheet As Worksheet

For Each oneSheet In ThisWorkbook.Worksheets
If oneSheet.Name <> "Master" Then
oneSheet.Copy
ThisWorkbook.Sheets("Master").Copy after:=ActiveSheet
End If
Next oneSheet

[ss6857]

This works well!! except that I have sheets in the book such as a control sheet and a data sheet that I do not want to copy over. I want to stop the process once all the individuals names are done.

Should I just include the name of the sheets with this line:

If onesheet.Name <> "Master" Then

or is there a more efficient way?

[ss6857]

Actually. I figure out my above reply. But now I'm having a problem because when it goes through the first time it repeats the master sheet on the original workbook. It only does it with the first one though.

[mikerickson]

If you post your code, it should be alterable. I would use a conditional like
If Not IsNumeric(Application.Match(oneSheet.Name, Array("Master", "Control", "Data"), 0)) Then
oneSheet.Copy
ThisWorkbook.Sheets("Master").Copy after:=ActiveSheet
End if

[ss6857]

Perfect. Thank you!