I would like to specify a variable instead of using a repetitive expression.
I wonder if you could please help me to fix this code below.
Instead of keep writing Thisworkbook.sheets("certainsheet") I would like to define a variable to express this.
Sub specificsheet()
Dim i As Integer
Dim v As Sheets
For i = 1 To 10
v = ThisWorkbook.Sheets("certainsheet")
v.Cells(i, 1).Value = i
Next
End Sub
Thank you so much for any comments.
You can look on your VBE for the code name of that sheet and call that.
Let's assume it is called "ws_CertainSheet".
You can edit that codename by checking the properties of the that "Microsoft Excel Object"
On a side note, a frequent practice we do at the company I work at is to have specialized function calls for ListObjects, named ranges or other Excel Objects.
So, on our framework we have an mListObjects module with calls to all ListObjects of interest.
Something like:
Public Function tblSomeTable() As ListObject: Set tblSomeTable= ws_CertainSheet.ListObjects("tblSomeTable"): End Function
v. Before the loop:Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("certainsheet"). In the loop:ws.Cells(i, 1).Value = i. If you want to get rid of the loop then use:Dim i As Long: i = 10: ws.Range("A1:A" & i) = ws.Evaluate("Row(1:" & i & ")"). If you meant something different, please do clarify.