Dynamic Worksheet Name

[bostonrudi1993]

Hello,

I am trying to create a dynamic worksheet name in my dynamic range borders macro.

I got the range borders to be dynamic but now I am struggling with creating a dynamic worksheet name that I do not have to change for each report I run.

This is the code I have:

Sub DynamicRangeBorders()
'Best used when first column has value on last row and first row has a value in the last column

Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range


Set sht = Worksheet("A1")
Set StartCell = Range("A2")


'Find Last Row and Column
LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column


'Select Range
sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select

'Apply the borders
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With


End Sub

The red text above is the line I cannot figure out. I am new to VBA and trying to teach myself. Any advice is more than welcome!

Thanks in advance!

 

[ajamess]

No, the ".value" is implied, if left out.

Ah, learn something new every day!

 

[ajamess]

No, the ".value" is implied, if left out.

I just did some testing.

If I try:

Sub Test()
    Dim mine As Worksheet
    
    Set mine = Workbooks("macro.xlsm").Worksheets(Range("H1"))
    
    mine.Activate
End Sub

I get a mismatch error on the "Set" line.

However if I do

Sub Test()
    Dim mine As Worksheet
    
    Set mine = Workbooks("macro.xlsm").Worksheets(Range("H1").value)
    
    mine.Activate
End Sub

It works as intended.

If i do it declaring a string variable beforehand, it works with or without the .value .

 

[Joe4]

The error is subscript out of range. In cell A1 I have "SW PFW Open Rental Contracts", the sheet name is the same as cell A1.

That means that you do not, in fact, have a sheet with that name - they are not exactly the same - something is different!
Note that something as simple as an extra space on the end of one and not the other will cause them to be different - check for extra spaces.

 

[Joe4]

This works fine for me with the sheet name "SW PFW Open Rental Contracts":

Sub Test()

Dim sht As Worksheet
Dim shtName As String

shtName = Sheets("Sheet1").Range("A1")
Set sht = Sheets(shtName)
sht.Activate

End Sub

 

[ajamess]

That means that you do not, in fact, have a sheet with that name - they are not exactly the same - something is different!
Note that something as simple as an extra space on the end of one and not the other will cause them to be different - check for extra spaces.

Punctuation or a space is common. Also it could be a different workbook is active that does not contain that sheet name. I usually like to declare my workbooks and worksheets just to avoid this confusion, instead of just the worksheets.

 

[Joe4]

Also it could be a different workbook is active that does not contain that sheet name.

Based on the original question, I was assuming that it was all in the same workbook, but maybe I shouldn't assume that (I like to think people wouldn't leave such a critical detail out!).

 

[bostonrudi1993]

There is only one worksheet in the workbook so I don't think that would be the problem?

 

[Joe4]

If there is only one sheet in the workbook, what do you need the sheet reference at all for?
You should be able to just leave it off or use "ActiveSheet".

 

[Scott T]

Sounds like there is no Sheet1 try

Sub Test()

Dim sht As Worksheet
Dim shtName As String

shtName = Sheets("[COLOR=#333333]SW PFW Open Rental Contracts"[/COLOR]).Range("A1")
Set sht = Sheets(shtName)
sht.Activate

End Sub

 

[ajamess]

Sub Test()

Dim sht As Worksheet
Dim shtName As String

shtName = Workbooks("Workbook1").Sheets("Sheet1").Range("A1")
Set sht = Workbooks("Workbook2").Sheets(shtName)
sht.Activate

End Sub

Where Workbook1 and Sheet1 is the workbook and sheet where you have the name in A1.
And Workbook 2 is the workbook that has the tab, with the name we set in Sht.