0 Replies - 544 Views - Last Post: 19 November 2008 - 08:58 AM Rate Topic: -----

#1 djessellis  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 18-January 08

Selecting Sheets in Excel based on declared variable

Post icon  Posted 19 November 2008 - 08:58 AM

Hi -

What I have is quite a complicated Macro that I'm building in VBA in Excel. The idea is that I have a range of 'Client Numbers' that I have used in a loop to create specific output in the form of pivot tables. The loop then goes on to create final 'Output' tabs, where the data from the pivot tables are pasted.

This is dynamic, meaning that the client numbers will change from user to user. I have managed to get the meat of the macro sorted, but what I am having a problem with is selecting the correct output tab.

I declared the variable as such:

ClientNum As Range


And have used this to create and name the new tabs as such... (this is just a snippet!)

	
	If Not SheetExists(ClientNum.Value) Then
		Sheets("OP Template").Select
		Sheets("OP Template").Copy After:=Sheets("OP Template")
		Sheets("OP Template (2)").Select
		ActiveSheet.Name = ClientNum.Value
	End If



My current line of thinking is to use something similar to select the correct output tab later in the code:

	Sheets(ClientNum.Value).Select



But this is where the whole thing trips up.... Is there another way of doing this?

Thanks

Jesse

Is This A Good Question/Topic? 0
  • +

Page 1 of 1