Activate a worksheet using a macro variable

Kenneth in Dundee

New Member
Joined
Jan 5, 2006
Messages
20
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Maybe try:

Worksheets(FeatID.Value).Activate

A worksheet with that name must be in the ActiveWorkbook.
 

Kenneth in Dundee

New Member
Joined
Jan 5, 2006
Messages
20

ADVERTISEMENT

Andrew

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

Thanks for your help.

Kenneth
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jan 5, 2006
Messages
20
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

New Member
Joined
Jan 5, 2006
Messages
20
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
 

Forum statistics

Threads
1,137,330
Messages
5,680,857
Members
419,936
Latest member
rphill48

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top