Please help me clean up my VBA script, I'm having trouble with redundancies

CapRavOr said:

VBA Code:

Code:

Sub macGL_Detail_ImportAndFormat()
'
' macGL_Detail_ImportAndFormat() Macro
'
Dim fileLocation As String
Dim fileToOpen As Workbook

Application.ScreenUpdating = False
fileLocation = "\\company.corp\files\KDrive\DM Monthly Close\2024\04 2024\GL Detail\04 DVH Detail.xlsx" 

'I would love to be able to loop or for/while or if/else, something  ^^^        ^^^
'    to get the file location open without me having to update the vba script each month
Workbooks.Open fileLocation

Windows("04 DVH Detail.xlsx").Activate
Sheets("GL Data").Select
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Windows("GL Detail Tieout v11").Activate
Sheets("DVH Detail Stage").Visible = True
Sheets("DVH Detail Stage").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Columns("A:C").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("N:Y").Select
Selection.Delete Shift:=xlToLeft
Columns("O:O").Select
Selection.Delete Shift:=xlToLeft
Columns("P:T").Select
Selection.Delete Shift:=xlToLeft
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("GL Detail").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Range("A1").Select
Sheets("DVH Detail Stage").Select
Range("A1").Activate
Selection.CurrentRegion.Select
Selection.ClearContents
Range("A1").Select
Windows("04 DVH Detail.xlsx").Activate
ActiveWorkbook.Close
Windows("GL Detail Tieout v11").Activate
ActiveWorkbook.RefreshAll
Sheets("DVH Detail Stage").Visible = False
Sheets("DVH Detail Import").Select
Range("C1").Select
Selection.FormulaR1C1 = "=NOW()"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("A1").Select
Sheets("DVH Detail Row-Count Log").Select
Rows("2:2").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A2").Select
Selection.FormulaR1C1 = "=TODAY()"
Sheets("DVH Detail Import").Select
Range("Import_ReportVersion").Select
Selection.Copy
Sheets("DVH Detail Row-Count Log").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Sheets("DVH Detail Import").Select
Range("Import_Rows").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("DVH Detail Row-Count Log").Select
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Range("A2:C2").Select
Application.CutCopyMode = False
Selection.Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Sheets("DVH Detail Import").Select
Range("A1").Select
Application.CutCopyMode = False
Selection.FormulaR1C1 = ""
Range("A1").Select
ImportComplete = MsgBox("Import Complete", , "GL Detail Data Import")
   

End Sub

Click to expand...

Try this.

Sub macGL_Detail_ImportAndFormat()
Dim fileLocation As String
Dim fileToOpen As Workbook
Dim sourceWorkbook As Workbook
Dim targetWorkbook As Workbook
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim lastRow As Long
Dim lastColumn As Long
Dim i As Long

Application.ScreenUpdating = False

' Define file location
fileLocation = "\\company.corp\files\KDrive\DM Monthly Close\2024\04 2024\GL Detail\04 DVH Detail.xlsx"

' Open workbook
Set fileToOpen = Workbooks.Open(fileLocation)

' Set source workbook and sheet
Set sourceWorkbook = fileToOpen
Set sourceSheet = sourceWorkbook.Sheets("GL Data")

' Find last row and column in source sheet
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
lastColumn = sourceSheet.Cells(1, sourceSheet.Columns.Count).End(xlToLeft).Column

' Copy data from source to target
Set targetWorkbook = Workbooks("GL Detail Tieout v11")
Set targetSheet = targetWorkbook.Sheets("DVH Detail Stage")
sourceSheet.Range("A1").CurrentRegion.Copy
targetSheet.Range("A1").PasteSpecial Paste:=xlPasteValues

' Delete unnecessary columns
For i = 3 To 21
If i <> 3 And i <> 14 And i <> 15 Then ' Exclude columns B, N, O, P, and Q
targetSheet.Columns(i).Delete
End If
Next i

' Copy data from target to GL Detail sheet
targetSheet.Range("A2", targetSheet.Cells(lastRow, lastColumn)).Copy
targetWorkbook.Sheets("GL Detail").Range("A2").PasteSpecial Paste:=xlPasteValues

' Clear contents in DVH Detail Stage
targetSheet.Cells.ClearContents

' Close source workbook
sourceWorkbook.Close

' Refresh data in target workbook
targetWorkbook.RefreshAll

' Update timestamp in DVH Detail Import sheet
targetWorkbook.Sheets("DVH Detail Import").Range("C1").Value = Now()

' Update timestamp and row count in DVH Detail Row-Count Log sheet
targetWorkbook.Sheets("DVH Detail Row-Count Log").Rows(2).Insert Shift:=xlDown
targetWorkbook.Sheets("DVH Detail Row-Count Log").Range("A2").Value = Date
targetWorkbook.Sheets("DVH Detail Row-Count Log").Range("B2").Value = "Import_ReportVersion"
targetWorkbook.Sheets("DVH Detail Row-Count Log").Range("C2").Value = "Import_Rows"

' Inform user that import is complete
MsgBox "Import Complete", , "GL Detail Data Import"

Application.ScreenUpdating = True
End Sub