TheRedCardinal
Board Regular
- Joined
- Jul 11, 2019
- Messages
- 243
- Office Version
- 365
- 2021
- Platform
- Windows
I am developing my macro skills now and it's coming along well. I am developing a sheet that removes stacks of manual work from my team's tasks.
I have completed 2 phases of 7, but I worry that the process is not the most efficient.
So in this example, the sheet fills in a whole series of columns based on either data from a table, or from other sources (ranges in the workbook, or simple IF statements based on other values in the table).
Would you be so kind as to have a look at it and comment on whether I have made this more complex and longwinded than I needed to?
For example I feel like there must be a better way to code something like this:
Thanks for any comments!
I have completed 2 phases of 7, but I worry that the process is not the most efficient.
So in this example, the sheet fills in a whole series of columns based on either data from a table, or from other sources (ranges in the workbook, or simple IF statements based on other values in the table).
Would you be so kind as to have a look at it and comment on whether I have made this more complex and longwinded than I needed to?
Code:
Sub GermanyCSV()
Dim PasteRange As Range
Dim NoTC As Range, Region As Range, Direction As Range, MoT As Range
Set WS1 = Sheets("5. Final CSV")
Set WS2 = Sheets("4. PivotTable")
Set WS3 = Sheets("Data Sheet")
Set PasteRange = WS1.Range("A1")
If Range("RepDirection") = "Arrivals" Then
Range("DEAFields").Copy
Else
Range("DEDFields").Copy
PasteRange.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If
Call LookupCommon
Counter = WS1.Range("F1").End(xlDown).Row
With WS1
For Each CellA In Range(.Cells(2, 1), .Cells(Counter, 1))
CellA.Offset(0, 1) = Range("RepMonth")
If CellA.Offset(0, 6) = "GB" Then
CellA.Offset(0, 3) = "1"
Else
CellA.Offset(0, 3) = "3"
End If
If CellA.Offset(0, 11) < 0 Then
CellA.Offset(0, 2) = "21"
CellA.Offset(0, 11) = -Round(CellA.Offset(0, 11), 0)
CellA.Offset(0, 13) = -Round(CellA.Offset(0, 13), 0)
Else
CellA.Offset(0, 2) = "11"
CellA.Offset(0, 11) = Round(CellA.Offset(0, 11), 0)
CellA.Offset(0, 13) = Round(CellA.Offset(0, 13), 0)
End If
CellA.Offset(0, 14) = CellA.Offset(0, 13)
CellA.Offset(0, 6).NumberFormat = "@"
CellA.Offset(0, 7).NumberFormat = "@"
If Range("RepDirection") = "Arrivals" Then
CellA.Value = "E"
CellA.Offset(0, 6) = "05"
CellA.Offset(0, 8) = CellA.Offset(0, 6)
Else
CellA.Value = "V"
CellA.Offset(0, 7) = "05"
End If
Next CellA
End With
End Sub
For example I feel like there must be a better way to code something like this:
Code:
For Each WS1 In Application.ActiveWorkbook.Worksheets
If WS1.Name <> "Intrastat - Start" And WS1.Name <> "1. Raw Data" And WS1.Name <> "2. Final Data" And WS1.Name <> "Data Sheet" And WS1.Name <> "Checklist" _
And WS1.Name <> "3. Data Reconciliation" And WS1.Name <> "4. Pivot Table" Then
WS1.Delete
End If
Next WS1
Thanks for any comments!