amy2722
06-22-2023, 02:41 PM
Hi there, hoping someone can help me before I pull my hair out!
I have a workbook that has 8 sheets in it, 2 of the worksheets have identical macros, is there a way so that the macros only run on the active worksheet, at the moment when I click on them in the second worksheet it is carrying out the action back on the first one.
here is my code below:
Sub Macro1()
' Macro1 add payment reference''
Sheets("Data").Select
Range("M3:AH4").Select
Selection.Copy
Sheets("Customer - 1").Select *I have two workbooks that have the Macro, Customer - 1 and Customer - 2
ActiveSheet.Range("A57").Select
ActiveSheet.Paste
Range("A57").Select
'proforma terms'
Sheets("Data").Select
Range("A13").Select
Selection.Copy
Sheets("Customer - 1").Select
Range("C52").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A53").Select
End Sub
Sub Macro2()
' Macro2 clear payment reference'
Range("A57:A58,C52:C54").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A59").Select
End Sub
Sub bankdets()
'bankdets Macro'
'see new bank details'
Sheets("Data").Select
Range("A30:V31").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Customer - 1").Select
Range("A47:V48").Select
ActiveSheet.Paste
Range("V54").Select
Sheets("Data").Select
Range("A16:G20").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Customer - 1").Select
Range("M55:M59").Select
ActiveSheet.Paste
Range("V54").Select
'account terms'
Sheets("Data").Select
Range("A10:A12").Select
Selection.Copy
Sheets("Customer - 1").Select
Range("C52").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A53").Select
End Sub
Sub Macro4()
'Export'
Range("S50").Select
ActiveCell.FormulaR1C1 = "0%"
Range("T50:V50").Select
ActiveCell.FormulaR1C1 = "EXPORT"
Range("T53").Select
End Sub
Sub vatcal()
'vatcal Macro'
Range("S50").Select
ActiveCell.FormulaR1C1 = "20%"
Range("T50:V50").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C*RC[-1])"
Range("T51:V51").Select
End Sub
Sub BrowseFile()
Dim Foldername As String
Foldername = "Q:\Order Processing\C of C\2023"
Shell "C:\WINDOWS\explorer.exe """ & Foldername & "", vbNormalFocus
End Sub
Sub clearacc()
' clearacc Macro'
Range("A47:V48,C52:C54,L55:T59").Select
Range("L55").Activate
Application.CutCopyMode = False
Selection.ClearContents
Range("U59").Select
End Sub
I have a workbook that has 8 sheets in it, 2 of the worksheets have identical macros, is there a way so that the macros only run on the active worksheet, at the moment when I click on them in the second worksheet it is carrying out the action back on the first one.
here is my code below:
Sub Macro1()
' Macro1 add payment reference''
Sheets("Data").Select
Range("M3:AH4").Select
Selection.Copy
Sheets("Customer - 1").Select *I have two workbooks that have the Macro, Customer - 1 and Customer - 2
ActiveSheet.Range("A57").Select
ActiveSheet.Paste
Range("A57").Select
'proforma terms'
Sheets("Data").Select
Range("A13").Select
Selection.Copy
Sheets("Customer - 1").Select
Range("C52").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A53").Select
End Sub
Sub Macro2()
' Macro2 clear payment reference'
Range("A57:A58,C52:C54").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A59").Select
End Sub
Sub bankdets()
'bankdets Macro'
'see new bank details'
Sheets("Data").Select
Range("A30:V31").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Customer - 1").Select
Range("A47:V48").Select
ActiveSheet.Paste
Range("V54").Select
Sheets("Data").Select
Range("A16:G20").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Customer - 1").Select
Range("M55:M59").Select
ActiveSheet.Paste
Range("V54").Select
'account terms'
Sheets("Data").Select
Range("A10:A12").Select
Selection.Copy
Sheets("Customer - 1").Select
Range("C52").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A53").Select
End Sub
Sub Macro4()
'Export'
Range("S50").Select
ActiveCell.FormulaR1C1 = "0%"
Range("T50:V50").Select
ActiveCell.FormulaR1C1 = "EXPORT"
Range("T53").Select
End Sub
Sub vatcal()
'vatcal Macro'
Range("S50").Select
ActiveCell.FormulaR1C1 = "20%"
Range("T50:V50").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-1]C*RC[-1])"
Range("T51:V51").Select
End Sub
Sub BrowseFile()
Dim Foldername As String
Foldername = "Q:\Order Processing\C of C\2023"
Shell "C:\WINDOWS\explorer.exe """ & Foldername & "", vbNormalFocus
End Sub
Sub clearacc()
' clearacc Macro'
Range("A47:V48,C52:C54,L55:T59").Select
Range("L55").Activate
Application.CutCopyMode = False
Selection.ClearContents
Range("U59").Select
End Sub