Vba Macro Code running partially when i trying it from another worksheet

[Harmandeep]

Hello Everyone,

I am looking for a quick response form VBA experts. I am new to VBA coding and i have written a VBA code in a worksheet name(Data) and the same is running perfectly on the Data worksheet. I am trying to set a button on another work sheet named(Summary). When i am assign the same Macro Code written in Data worksheet to Summary worksheet with button. The same is running on Summary worksheet only. How can i assign the worksheet name in Code so that it can run from any worksheet in the same file. I am sharing my code below.

Thanks in advance.



''''Sub Report()

lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row

Range("u1").FormulaR1C1 = "Check1"
Range("U1").Select
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 15773696
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("u2").Formula = "=IFERROR(VLOOKUP(P2,'BillDesk_Setllement Report'!C:P,14,0),"""")"
Range("u2").AutoFill Range("u2:u" & lr)


Sheets("Data").Select
Range("v1").FormulaR1C1 = "Diff1"
Range("V1").Select
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 15773696
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("v2").Formula = "=IFERROR(Q2-U2,"""")"
Range("v2").AutoFill Range("v2:v" & lr)

Sheets("Data").Select
Range("w1").FormulaR1C1 = "Duplicate Flag"
Range("w1").Select
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 15773696
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("w2").Formula = "=IF(COUNTIF($P$2:$P$300000,$P2)>1,""Duplicate"",""Not Duplicate"")"
Range("w2").AutoFill Range("w2:w" & lr)

Sheets("Data").Select
Range("x1").FormulaR1C1 = "Remarks1"
Range("x1").Select
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 15773696
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("x2").Formula = "=IFERROR(IF(AND(Q2=U2),""Matched"",IF(AND(Q2>U2),""Discount"",IF(Q2<>U2,""Not Bill Desk""))),""Not Bill Desk"")"
Range("x2").AutoFill Range("x2:x" & lr)



End Sub

 

[HaHoBe]

Hi Harmandeep,

if you don' assign a worksheet to the range the code will be performed on the ActiveSheet. So if yoiu assign a button to each sheet the code would run on that sheet.

For Columns V to X you restrict the code to run on Sheet Data only.

Code for ActiveSheet may look like

Sub Report_mod()
' https://www.mrexcel.com/board/threads/vba-macro-code-running-partially-when-i-trying-it-from-another-worksheet.1224996/
Dim lr As Long

With ActiveSheet
  lr = .Cells.Find("*", .Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
  
  With .Range("U1:X1")
    .Value = Array("Check1", "Diff1", "Duplicate Flag", "Remarks1")
    .Font.Bold = True
    With .Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 15773696
      .TintAndShade = 0
      .PatternTintAndShade = 0
    End With
  End With
  .Range("u2:u" & lr).Formula = "=IFERROR(VLOOKUP(P2,'BillDesk_Setllement Report'!C:P,14,0),"""")"
  .Range("v2:v" & lr).Formula = "=IFERROR(Q2-U2,"""")"
  .Range("w2:w" & lr).Formula = "=IF(COUNTIF($P$2:$P$300000,$P2)>1,""Duplicate"",""Not Duplicate"")"
  .Range("x2:x" & lr).Formula = "=IFERROR(IF(AND(Q2=U2),""Matched"",IF(AND(Q2>U2),""Discount"",IF(Q2<>U2,""Not Bill Desk""))),""Not Bill Desk"")"
End With
End Sub

 

[Harmandeep]

Hey HahoBe

Thanks for replying i tried this code and same is working perfectly fine on Data sheet. How can i assign this code with button to Summary sheet. So that it can run from summary sheet to data sheet. I am attaching image of Summary sheet.

​

 

[HaHoBe]

Hi Harmandeep,

add another button from the Forms Control, assign the macro to it and rename it - how did the other buttons find their way into the sheet?

And I would slightly alter the code by passing the sheet to work on as a parameter to the procedure

Sub Report_mod(wSheet As Worksheet)
' https://www.mrexcel.com/board/threads/vba-macro-code-running-partially-when-i-trying-it-from-another-worksheet.1224996/
Dim lr As Long

With wSheet
  lr = .Cells.Find("*", .Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
  
  With .Range("U1:X1")
    .Value = Array("Check1", "Diff1", "Duplicate Flag", "Remarks1")
    .Font.Bold = True
    With .Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 15773696
      .TintAndShade = 0
      .PatternTintAndShade = 0
    End With
  End With
  .Range("u2:u" & lr).Formula = "=IFERROR(VLOOKUP(P2,'BillDesk_Setllement Report'!C:P,14,0),"""")"
  .Range("v2:v" & lr).Formula = "=IFERROR(Q2-U2,"""")"
  .Range("w2:w" & lr).Formula = "=IF(COUNTIF($P$2:$P$300000,$P2)>1,""Duplicate"",""Not Duplicate"")"
  .Range("x2:x" & lr).Formula = "=IFERROR(IF(AND(Q2=U2),""Matched"",IF(AND(Q2>U2),""Discount"",IF(Q2<>U2,""Not Bill Desk""))),""Not Bill Desk"")"
End With
End Sub

which would be called like

Sub CallReport()
Report_mod ActiveSheet
End Sub

The calling macro would enable you to run the procedure on sheets that are not active by calling it like

Sub CallReport()
Report_mod Worksheets("Non_Active Sheet")
End Sub

Holger