ss6857
03-22-2011, 08:21 AM
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.
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.