How to refer to a variable with BLANKs

macropod · #6 11-05-2015, 02:30 AM

So, given that today's date is 05.11.2015, if a sheet named "Data Input 05.11.2015" doesn't exist, you'll get a 'subscript out of range' error... There's also the question of whether the Date keyword on your system returns dates expressed in the DD.MM.YYYY format.

Even so, you shouldn't need to activate a worksheet to work with it. For example:

Code:

Sub Test()
Dim v_Sheetname As String
v_Sheetname = "Data Input " & Format(Date, "DD.MM.YYYY")
MsgBox Sheets(v_Sheetname).Range("A1").Value
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Counting Blanks in 1 Column and Non-Blanks in Another	dogwood705	Excel	4	02-07-2015 08:45 AM
Run Time Error '91': Object variable or With block variable not set using Catalogue Mailmerge	Berryblue	Mail Merge	1	11-13-2014 05:36 PM
Run-time error 91 object variable or with block variable not set	JUST ME	Word VBA	4	03-25-2014 06:56 AM
object variable or with block variable not set	MJP143	Excel	1	02-11-2013 05:07 AM
Run-time error '91': Object variable or With block variable not set	tinfanide	Excel Programming	2	06-10-2012 10:17 AM