Code changes automatically with the sheet name.

[RAJESH1960]

Hello Experts

I have a query. How is it possible to change the name of the sheet in the code? For instance, the name of the sheet is “Kotak” then when I run the code it should show “Kotak” in the code. If I change the name of the bank to “ICICI Bank” then the code should automatically select / detect the sheet name and change to “ICICI Bank”.

 

[Joe4]

You can also refer to sheet by their index instead of their name in VBA code, i.e.

Sheets(1).Activate

Or, if the sheet is the active sheet at some point, you can dynamically capture it in a worksheet object, i.e.

Dim ws as Worksheet
Set ws = ActiveSheet

Then you can refer to it by that object reference in your code, i.e.

ws.Activate

 

[RAJESH1960]

You can also refer to sheet by their index instead of their name in VBA code, i.e.

Sheets(1).Activate

Or, if the sheet is the active sheet at some point, you can dynamically capture it in a worksheet object, i.e.

Dim ws as Worksheet
Set ws = ActiveSheet

Then you can refer to it by that object reference in your code, i.e.

ws.Activate

It is difficult for me to understand. Can you please alter the code wherever necessary to get the derived result. I have edited the code in bold where the bank name needs to be as per the sheet name. The code passes through 3 different sheets - Bank, A and B

 

[RAJESH1960]

Sub separate_multiple_entries_new()
Dim a As Variant, b As Variant, c As Variant
Dim Fnd As Range
Dim i As Long, j As Long, k As Long, ini As Long

With Sheets("Bank")
.UsedRange.UnMerge
Set Fnd = .Range("A:A").Find("Date", , , xlPart, xlByRows, xlNext, False, , False)
If Not Fnd Is Nothing And Fnd.Row > 1 Then ini = Fnd.Row + 2 Else ini = 1
a = .Range("A" & ini, .Range("I" & Rows.Count).End(3)).Value
End With

ReDim b(1 To UBound(a), 1 To 7)
ReDim c(1 To UBound(a), 1 To 7)
For i = 1 To UBound(a) - 3
If LCase(a(i, 3)) <> LCase("(as per details)") And a(i, 6) <> "" Then
j = j + 1
b(j, 1) = i 'Line
b(j, 2) = a(i, 1) 'Date
b(j, 3) = a(i, 6) 'Vch Type
b(j, 4) = a(i, 7) 'Vch No.
b(j, 5) = a(i, 3) 'Particulars
b(j, 6) = a(i, 8) 'Debit
b(j, 7) = a(i, 9) 'Credit
Else
k = k + 1
c(k, 1) = i 'Line
c(k, 2) = a(i, 1) 'Date
c(k, 3) = a(i, 6) 'Vch Type
c(k, 4) = a(i, 7) 'Vch No.
c(k, 5) = a(i, 3) 'Particulars
c(k, 6) = a(i, 8) 'Debit
c(k, 7) = a(i, 9) 'Credit
End If
Next

With Sheets("Bank")
.Cells.Clear

.Range("A1:G1").Value = Array("Line", "Date", "Vch Type", "Vch No.", "Particulars", "Debit", "Credit")
.Range("A2").Resize(j, 7).Value = b
.Range("A" & j + 3).Resize(k, 7).Value = c
.Columns("F:G").NumberFormat = "0.00"

With .Range("A" & j + 3).Resize(k, 7).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With

With .Cells
.EntireColumn.AutoFit
.Borders.LineStyle = xlNone
.HorizontalAlignment = xlLeft
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False

With .Font
.Bold = False
.Name = "Calibri"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With 'font
End With 'cells

.Range("A:A").NumberFormat = "General"
.Range("B:B").NumberFormat = "dd-mm-yyyy"
End With

Dim rngReferenceRange As Range, rngToCopy As Range
Set rngReferenceRange = ActiveSheet.Range("A1").CurrentRegion 'assumes that the first data entry is in cell A1
Set rngToCopy = Cells(rngReferenceRange.Rows.Count + 2, 1).CurrentRegion 'assumes that new data ALWAYS starts one row after the blank row
rngToCopy.Copy
With Sheets("A")
.Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Columns("B:B").NumberFormat = "dd-mm-yyyy"
End With

Sheets("A").Select
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Columns("G:H").Select
Selection.Insert Shift:=xlToRight
Selection.Clear
Range("G2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=IF(RC[2]="""","""",-RC[2])"
Range("H2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=IF(RC[2]="""","""",RC[2])"
Range("G2:H2").Select
Selection.AutoFill Destination:=Range("G2:H23")
Range("G2:H23").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range("A2:H23").Select
Selection.Copy
Sheets("B").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cells.Select
Cells.EntireColumn.AutoFit
Columns("B:B").Select
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yyyy"
Columns("F:F").Select
Selection.Replace What:="(as per details)", Replacement:="Bank", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("K2").Select
End Sub

 

[Oaktree]

I might be misunderstanding what's changing... if the sheet name itself is changing, you can reference the sheet's codename (instead of its index or its name as it appears in the tabs).

With a sheet selected, if you go to the immediate window in VBA and run ?activesheet.codename, it will return Sheet12 or similar. Then, you can use With Sheet12 instead of With Sheets("Bank"). Sheet12 will always point to the sheet regardless of what it's named.

 

[RAJESH1960]

I might be misunderstanding what's changing... if the sheet name itself is changing, you can reference the sheet's codename (instead of its index or its name as it appears in the tabs).

With a sheet selected, if you go to the immediate window in VBA and run ?activesheet.codename, it will return Sheet12 or similar. Then, you can use With Sheet12 instead of With Sheets("Bank"). Sheet12 will always point to the sheet regardless of what it's named.

Once the job is done of that sheet it will be deleted and another sheet will be inserted with a different bank name and the sheet name will change. So, I think, it will not be possible to use the sheet name in the code. I will try some other way. Thanks Oaktree.

 

[Joe4]

If the new sheet is always inserted at the end, you can just get the last sheet dynamically using the worksheet objects, like I mentioned.
Here is an easy way to do that:

    Dim ws As Worksheet
    Set ws = Sheets(Sheets.Count)

 

[RAJESH1960]

If the new sheet is always inserted at the end, you can just get the last sheet dynamically using the worksheet objects, like I mentioned.
Here is an easy way to do that:

    Dim ws As Worksheet
    Set ws = Sheets(Sheets.Count)

Thanks Joe4. I have missed a few steps in my project. So there will be some major changes regarding the range and all. I have to do it again from the beginning. But I will keep that in mind. Thanks. See you soon.

 

[RAJESH1960]

I might be misunderstanding what's changing... if the sheet name itself is changing, you can reference the sheet's codename (instead of its index or its name as it appears in the tabs).

With a sheet selected, if you go to the immediate window in VBA and run ?activesheet.codename, it will return Sheet12 or similar. Then, you can use With Sheet12 instead of With Sheets("Bank"). Sheet12 will always point to the sheet regardless of what it's named.

"The greatest challenge to any thinker is stating the problem in a way that will allow a solution." I love this quote.

 

[Joe4]

Thanks Joe4. I have missed a few steps in my project. So there will be some major changes regarding the range and all. I have to do it again from the beginning. But I will keep that in mind. Thanks. See you soon.

Yes, I make use of worksheet objects all the time.
They are a great way to use dynamic sheet references that are not dependent upon some specific sheet name.