Rubber Beaked Woodpecker
Board Regular
- Joined
- Aug 30, 2015
- Messages
- 205
- Office Version
- 2021
Any kind sole able to give advice on how to make my code run more efficiently?
I'm guessing that I have too many range events and these could maybe merged into one event?
Thanks
RBW
I'm guessing that I have too many range events and these could maybe merged into one event?
Thanks
RBW
VBA Code:
Option Explicit
Dim currentMarket As String
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then
Application.EnableEvents = False 'Turn off events so changes to cell don't retrigger event
Range("Q1").Value = WorksheetFunction.CountIf(Range("H5:H50"), ">0") ' this counts cells above zero
Range("Q2") = Range("W2")
Range("U1") = Range("U2")
Range("T1") = Range("T2")
Range("AA1") = Range("Z1")
If Range("S1") = 1 Then
Call ThisSheetlogBalance
Call logEvent
Call logBalance
Call logNumbers
Call logPrice
Call logHighest
ElseIf Range("X2").Value = "Yes" Then
Call R1
End If
End If
Application.EnableEvents = True 'Turn on events again
End Sub
Sub logBalance()
Dim source As Worksheet
Dim destination As Worksheet
Dim emptyColumn As Long
Set source = Sheets("Sheet1")
Set destination = Sheets("Sheet8")
source.Range("Z5:Z16").Copy
emptyColumn = destination.Cells(31, destination.Columns.Count).End(xlToLeft).Column
If IsEmpty(destination.Range("A28")) Then
destination.Cells(1, 1).PasteSpecial Transpose:=True
Else
emptyColumn = emptyColumn + 1
destination.Cells(31, emptyColumn).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
End Sub
Sub logPrice()
Dim source As Worksheet
Dim destination As Worksheet
Dim emptyColumn As Long
Set source = Sheets("Sheet1")
Set destination = Sheets("Sheet8")
source.Range("Y5:Y16").Copy
emptyColumn = destination.Cells(44, destination.Columns.Count).End(xlToLeft).Column
If IsEmpty(destination.Range("A43")) Then
destination.Cells(1, 1).PasteSpecial Transpose:=True
Else
emptyColumn = emptyColumn + 1
destination.Cells(44, emptyColumn).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
End Sub
Sub logHighest()
Dim source As Worksheet
Dim destination As Worksheet
Dim emptyColumn As Long
Set source = Sheets("Sheet1")
Set destination = Sheets("Sheet8")
source.Range("T1:T1").Copy
emptyColumn = destination.Cells(30, destination.Columns.Count).End(xlToLeft).Column
If IsEmpty(destination.Range("A28")) Then
destination.Cells(1, 1).PasteSpecial Transpose:=True
Else
emptyColumn = emptyColumn + 1
destination.Cells(30, emptyColumn).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
End Sub
Sub ThisSheetlogBalance()
Dim source As Worksheet
Dim destination As Worksheet
Dim emptyColumn As Long
Set source = Sheets("Sheet1")
Set destination = Sheets("Sheet1")
source.Range("Z5:Z16").Copy
emptyColumn = destination.Cells(5, destination.Columns.Count).End(xlToLeft).Column
If IsEmpty(destination.Range("AA5")) Then
destination.Cells(1, 1).PasteSpecial Transpose:=True
Else
emptyColumn = emptyColumn + 1
destination.Cells(5, emptyColumn).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
End Sub
Sub logEvent()
Dim source As Worksheet
Dim destination As Worksheet
Dim emptyColumn As Long
Set source = Sheets("Sheet1")
Set destination = Sheets("Sheet8")
source.Range("A1:A1").Copy
emptyColumn = destination.Cells(29, destination.Columns.Count).End(xlToLeft).Column
If IsEmpty(destination.Range("A28")) Then
destination.Cells(1, 1).PasteSpecial Transpose:=True
Else
emptyColumn = emptyColumn + 1
destination.Cells(29, emptyColumn).PasteSpecial Transpose:=False
Application.CutCopyMode = False
End If
End Sub
Sub logNumbers()
Dim source As Worksheet
Dim destination As Worksheet
Dim emptyColumn As Long
Set source = Sheets("Sheet1")
Set destination = Sheets("Sheet8")
source.Range("U1:U1").Copy
emptyColumn = destination.Cells(28, destination.Columns.Count).End(xlToLeft).Column
If IsEmpty(destination.Range("A28")) Then
destination.Cells(1, 1).PasteSpecial Transpose:=True
Else
emptyColumn = emptyColumn + 1
destination.Cells(28, emptyColumn).PasteSpecial Transpose:=False
Application.CutCopyMode = False
End If
End Sub
Sub R1()
'
' R1 Macro
'
'
Sheets("Sheet1").Select
Range("R1").Select
Application.CutCopyMode = False
Selection.Copy
Range("R2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("T1").Select
End Sub