Activate a worksheet using a macro variable

[Kenneth in Dundee]

I am building a macro to create and name a variable set of worksheets, depending on a table of values. This part is done.

The next step is to combine data from a set of other worksheets into each of the new worksheets.

To do this I need to be able to activate worksheets based on a variable.

This seems to work some of the time, but not all of the time.

eg
the statement
Set FeatID = Sheets(UnitFeatStr).Cells(RowCount, ColCount)
works (UnitFeatStr, RowCount, ColCount are all variables) and the value taken on by FeatID is as expected.

the statement
Worksheets(FeatID).Activate
does not work

When I replace FeatID with the fixed value of the variable, the code executes. Why should it not work with the variable?

I am possibly overlooking a simple mistake, but appreciate any help.

Thanks

Kenneth

 

[GlennUK]

What is the value of FeatID when this doesn't work? Is it a number? Are you trying to Activate a sheet named "2" or some other numberic? If so, Excel may be trying to access the index number of the sheet instead of the sheet name.

 

[Kenneth in Dundee]

Glen
The value is F000 (ie F followed by 3 zeros)

K

 

[Andrew Poulsom]

Maybe try:

Worksheets(FeatID.Value).Activate

A worksheet with that name must be in the ActiveWorkbook.

 

[Kenneth in Dundee]

Andrew

Using your idea Worksheets(FeatID.Value).Activate got me past the sticking points.

Thanks for your help.

Kenneth

 

[Kenneth in Dundee]

SOLVED - Activate a worksheet using a macro variable

Thanks

 

[Norie]

Kenneth

You shouldn't actually need to activate the worksheet(s) to do this.

Can you post some more of your code?

 

[Kenneth in Dundee]

Full code I am working with is shown below. I have made a number of modifications that show I can work without the Activate step. I have also eliminated the .value qualifier by declaring variables at the start of the module.

My latest debug problem is that I can get the subroutine CombFeatBoMs() to execute the Do..Loop first time round, but it fails (Macro Error) on the second loop. All variables are changing to show expected values. Any ideas?

Thanks

Kenneth


Dim UnitFeatStr As String
Dim UnitID As String
Dim FeatID As String
Dim ColCount As Integer
Dim RowCount As Integer

Sub CreateProtoBoMv3()
'ABoM contains a worksheet for each Feature - Fxxx convention. Worksheet carries Fxxx as name
'set variable to store name of sheet with Unit Feature Strings
UnitFeatStr = ActiveSheet.Name: ColCount = 0: UnitID = "Null"

'select unit from list
'Worksheet(UnitFeatStr).Select
'set variable UnitID to cell value where cell contains unit ID
Do Until UnitID = ""
ColCount = 1
'Set UnitID = Sheets(UnitFeatStr).Cells(1, ColCount)
UnitID = Sheets(UnitFeatStr).Cells(1, ColCount)

If UnitID = "" Then GoTo Label1:
'create worksheet for unit using UnitID
Sheets(1).Select ' add a sheet in first, leftmost, place
Worksheets.Add
Sheets(1).Name = UnitID
Call CombFeatBoMs
Label1:
Loop

'select unitfeature

'set variable Feat to cell value where cell contains Feature ID
'copy Feature BoM to worksheet UnitID
'next feature
'create pivot table to summarise parts and quantities - new worksheet
'next unit

End Sub

Sub CombFeatBoMs()
'this subroutine combines the list of parts for each feature worksheet
'into a single worksheet for the unit/buildtype identified.
'select unitfeature

'set variable Feat to cell value where cell contains Feature ID
'copy Feature BoM to worksheet UnitID
'next feature
'create pivot table to summarise parts and quantities - new worksheet
'next unit
RowCount = 2
'Set FeatID = Sheets(UnitFeatStr).Cells(RowCount, ColCount)
FeatID = Sheets(UnitFeatStr).Cells(RowCount, ColCount)
MsgBox (FeatID & RowCount & ColCount & UnitID)
' copy headings


'Sheets(FeatID.Value).Activate
Sheets(FeatID).Activate
Range("A5").EntireRow.Select
' Selection.Copy Destination:=Sheets(UnitID.Value).Range("A1")
Selection.Copy Destination:=Sheets(UnitID).Range("A1")

'HELP - the Do loop below executes the first time round and
'HELP returns the expected values of the variables
'HELP On the second traverse of the loop it fails on line
'HELP Worksheets(FeatID).Range("A5").Select
'HELP with a 'Macro Error'

' work through sheets copying the data from each to the UnitID tab
Do Until FeatID = ""
MsgBox (FeatID & RowCount & ColCount & UnitID)
' Worksheets(FeatID.Value).Activate ' make the sheet active
' Worksheets(FeatID.Value).Range("A5").Select
Worksheets(FeatID).Range("A5").Select
Selection.CurrentRegion.Select ' select all cells in this sheets

' select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

' copy cells selected in the new sheet on last line
' Selection.Copy Destination:=Sheets(UnitID.Value).Range("A65536").End(xlUp)(2)
Selection.Copy Destination:=Sheets(UnitID).Range("A65536").End(xlUp)(2)
'increment row counter for next FeatID
' Set RowCount = RowCount + 1
' Set FeatID = Sheets(UnitFeatStr).Cells(RowCount, ColCount)
RowCount = RowCount + 1
FeatID = Sheets(UnitFeatStr).Cells(RowCount, ColCount)
Loop

End Sub

 

[Kenneth in Dundee]

Please ignore previous posting - I have resolved issue - amazing what happens when you start sharing things - it forces you to think from a different angle and you sometimes get results.

While this statement worked once
Worksheets(FeatID).Range("A5").Select and did not work on next loop, by splitting the instruction into two
Worksheets(FeatID).Select
Range("A5").Select it did work.

I have now got my full code to work, and done some tidying up to remove instant exit from do loop.

Full Code is....

Dim UnitFeatStr As String
Dim UnitID As String
Dim FeatID As String
Dim ColCount As Integer
Dim RowCount As Integer

Sub CreateProtoBoMv3()
'ABoM contains a worksheet for each Feature - Fxxx convention. Worksheet carries Fxxx as name
'set variable to store name of sheet with Unit Feature Strings

'Initialise variables
UnitFeatStr = ActiveSheet.Name: ColCount = 1: UnitID = "Null"
UnitID = Sheets(UnitFeatStr).Cells(1, ColCount)

'select unit from list
Do Until UnitID = ""
If UnitID = "" Then GoTo Label1:
'create worksheet for unit using UnitID
Sheets(1).Select ' add a sheet in first, leftmost, place and rename with UnitID
Worksheets.Add
Sheets(1).Name = UnitID

'Compile all the featurePPLs into a single worksheet for the unit
Call CombFeatBoMs

'select new values of variables for next unit
ColCount = ColCount + 1
UnitID = Sheets(UnitFeatStr).Cells(1, ColCount)
Label1:
Loop



'create pivot table to summarise parts and quantities - new worksheet


End Sub

Sub CombFeatBoMs()
'this subroutine combines the list of parts for each feature worksheet
'into a single worksheet for the unit/buildtype identified.

'initialise values
RowCount = 2
FeatID = Sheets(UnitFeatStr).Cells(RowCount, ColCount)

' copy headings
Sheets(FeatID).Activate
Range("A5").EntireRow.Select
Selection.Copy Destination:=Sheets(UnitID).Range("A1")

' work through sheets copying the data from each feature ppl to the UnitID tab
Do Until FeatID = ""
' use this line for tracking progress if sub fails
' MsgBox (FeatID & RowCount & ColCount & UnitID)
Worksheets(FeatID).Select
Range("A5").Select 'change if BoM for Feature does not start in Cell A5
Selection.CurrentRegion.Select ' select all cells in data region on sheet
' select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets(UnitID).Range("A65536").End(xlUp)(2)
'increment row counter and assign new value to FeatID
RowCount = RowCount + 1
FeatID = Sheets(UnitFeatStr).Cells(RowCount, ColCount)
Loop

End Sub



Thanks all for assistance.

Kenneth