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

CapRavOr

New Member
Joined
Apr 26, 2022
Messages
5
Office Version
  1. 365
  2. 2021
  3. 2019
Platform
  1. Windows
VBA 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

I'm fairly new to writing VBA and I don't have a lot of experience writing any coding language other than SQL and I'm not even very well versed in that. That's all, I just want to know how you'd go about cleaning this code up and reducing the redundancies (if there even are any)
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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
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
 
Upvote 1
Solution
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
This potentially works, I just need to make some adjustments due to the layout of the source and target. Thank you so much, I learned more in 5 minutes reading this than I have reading other books and posts. Cheers!
 
Upvote 0
This potentially works, I just need to make some adjustments due to the layout of the source and target. Thank you so much, I learned more in 5 minutes reading this than I have reading other books and posts. Cheers!
Fingers crosssed it works OK for you. Happy to help. Remember to give a tick if the solution works, to show your query was answered.
 
Upvote 1
Fingers crosssed it works OK for you. Happy to help. Remember to give a tick if the solution works, to show your query was answered.
After some tweaks to the parts of the code that were proprietary, this worked like a charm. Thank you so much, I learned a lot from this!
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top