CODEWITHSUNDEEP
vbaexcelexcel-formula
George
George

Reputation: 35

Dyanmic VBA code for changing the vba when a sheet name is changed

I have a vba code which specifies particular sheet names to look at for example sheet 2,

But what if, someone forgot to change the sheet name to sheet2, can I add a piece of dynamic code to automatically change the vba code for which ever the sheet name is called? for example the second sheet in from the left.

Code Module 1:

Sub Calculation()
 Range("P2:P800").Select
Application.CutCopyMode = False
Selection.ClearContents

Dim dict1 As Object
Dim c1 As Variant, k As Variant
Dim currWS As Worksheet
Dim i As Double, lastRow As Double, tot As Double
Dim number1 As Double, number2 As Double, firstRow As Double

Set dict1 = CreateObject("Scripting.Dictionary")
Set currWS = ThisWorkbook.Sheets("Trade data")

'get last row withh data in Column A
lastRow = currWS.Cells(Rows.Count, "M").End(xlUp).Row

'put unique numbers in Column A in dict1
c1 = Range("M2:V" & lastRow)
For i = 1 To UBound(c1, 1)
    If c1(i, 1) <> "" Then
        'make combination with first 4 characters
      dict1(Left(c1(i, 1), 4) & "," & Left(c1(i, 8), 4) & "," & Left(c1(i,
    6), 10) & "," & Left(c1(i, 10), 7)) = 1
    End If
Next i

'loop through all the numbers in column A
For Each k In dict1.keys
    number1 = Split(k, ",")(0)
    number2 = Split(k, ",")(1)
    tot = 0
    firstRow = 0

    For i = 2 To lastRow
        If k = Left(currWS.Range("M" & i).Value, 4) & "," & 
        Left(currWS.Range("T" & i).Value, 4) & "," & currWS.Range("R" &
     i).Value & "," & (currWS.Range("O" & i).Value) Then
            If firstRow = 0 Then
                firstRow = i
            End If
            tot = tot + currWS.Range("W" & i).Value
        End If
    Next i
    currWS.Range("P" & firstRow) = tot
Next k

Call Consolidate
Call SingleTradeMove
End Sub

Module 2 code: Sub SingleTradeMove()

 Dim wsTD As Worksheet
 Set wsTD = Worksheets("Trade data")

 Sheets("UnMatching").Range("A2:AK600").ClearContents

 With wsTD

 lastRow = .Range("A" & .Rows.Count).End(xlUp).Row

 For i = 2 To lastRow


If Left(.Cells(i, "M"), 4) <> Left(.Cells(i, "T"), 4) _
    Or .Cells(i, "O") <> .Cells(i, "V") _
    Or .Cells(i, "R") <> .Cells(i, "Y") Then

       .Cells(i, "J").EntireRow.Copy _
           Destination:=Sheets("UnMatching").Range("A" &   Rows.Count).End(xlUp).Offset(1)

     End If

 Next i

End With

End Sub

Upvotes: 3

Views: 1152

Answers (2)

LizbethEll
LizbethEll

Reputation: 86

Building off ian0411's answer since I can not comment yet. You can also change this name to short hand. I always change mine to CN and then an abbreviation or something short enough its not a hassle to type out. In the example the sheet name in excel is BlueMoon. So I used CNBM in VBA. This gives a reference to the sheet, and the sheet name on excel's side can be changed without effecting your code. To change the name, click the sheet you want to name in the properties box. Then below that alter the (Name) option.

Image of VBA properties

Upvotes: 2

ian0411
ian0411

Reputation: 4265

Say you have a sheet named "Work data" and you programmed as Sheets("Work data"). To make this dynamic, you can use the name before the parenthese that when you launch your Visual Basic editor.

Visual Basic Editor Screenshot

For example, you have this code:

Sheets("Work data").Select

Now you can change to this:

Sheet1.Select

And this way, no matter how users changed the sheet name, it will always work. BUT please remember, the Sheet1 can be also changed but that can only be done inside Visual Basic editor properties. You can password protected the VBA so no one can accidentally alter it.

Upvotes: 1

Related Questions