Articles

View data in one sheet from sheets not yet created (excel)

April 22, 2012 at 04:25:05
Specs: Windows Vista

I am in the process of creating a workbook to track the progress over a number of measures for individual team members. Each worksheet is designated to an inidividual, it compiles their results over around 20 measures into a monthly, quarterly and annual pefrormance percentage score based on points achieved vs points available. Finally a 'team performance' sheet references each of the individual sheets, copies the performance percentage for each month and quarter over and calculates the rank of the person within the team.

The formulas on the sheets themselves work well, however to set the workbook up manually for other teams would be very time consuming as the 'team performance' sheet uses =(JOHNSMITH!F143) to pull the data across to it. It would require each formula to be changed based on the name of the worksheet.

So, what I am now attempting to do is use a 'setup' worksheet that takes a list of names and then copies a 'template' worksheet and auto populates the worksheet name. The macro I'm using for this (found on this forum) works fine, it creates duplicates of the template based on a list of names:

Private Sub CommandButton1_Click()
Dim strCol As String
Dim strRow As String
Dim rngStart As Range
Dim rngEnd As Range
Dim rngCell As Range
Dim strWsName As String
Dim strSrcName As String

On Error GoTo ErrHnd

'setup column letter and first row number containing names
'column
strCol = "A"
'row (number is in double quotes)
strRow = "1"

'turn off screen updating to stop flicker & increase speed
Application.ScreenUpdating = False

'save this worksheet's name, so we can go back to it later
strSrcName = ActiveSheet.Name

'set start of data in selected column
Set rngStart = ActiveSheet.Range(strCol & strRow)
'find end of data in selected column
Set rngEnd = ActiveSheet.Range(strCol & CStr(Application.Rows.Count)) _
.End(xlUp)

'loop through cells in used range
For Each rngCell In ActiveSheet.Range(rngStart, rngEnd)
'ignore empty cells in range
If rngCell.Text <> "" Then
'get worksheet name
strWsName = rngCell.Text
'test if worksheet exists
On Error Resume Next
If Worksheets(strWsName) Is Nothing Then
'copy worksheet named "Template"
Worksheets("Template").Copy After:=Worksheets(Worksheets.Count)
'name new sheet
Worksheets(Worksheets.Count).Name = strWsName
Else
'worksheet already exists
'reinstate error handling
On Error GoTo ErrHnd
End If
End If
Next rngCell

'go back to the source worksheet
Worksheets(strSrcName).Activate

'reinstate screen updating
Application.ScreenUpdating = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
'go back to the source worksheet
Worksheets(strSrcName).Activate
'reinstate screen updating
Application.ScreenUpdating = True
End Sub

What I now need to do is create the 'team performance' worksheet that will scrape data from each of the other worksheets, without knowing the title of the work sheets that original macro will create.


See More: View data in one sheet from sheets not yet created (excel)

Report •


#1
April 27, 2012 at 04:37:11

Easy. use a for/next that refers to sheet index numbers rather than names

The new sheet you add always has the highest index ID so when looping thru the sheets, stop one short of the sheet count.

for i = 1 to workbook.sheets.count-1
... sheet(i).cells(x,y)... blah, blah, blah
next i


Report •
Related Solutions


Ask Question