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

C

CapRavOr

New Member
Joined
Apr 26, 2022
Messages
7
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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
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
 
Upvote 1
Solution
Carl Stephens said:
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
Click to expand...
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
CapRavOr said:
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!
Click to expand...
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
Carl Stephens said:
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.
Click to expand...
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
You must log in or register to reply here.

Similar threads

C
  • Question Question
Please help clean up my convoluted VBA code. It seems very redundant and repetitive.
Replies
1
Views
441
myall_blues
myall_blues
D
  • Solved
Insert formula in last column macro
2
Replies
13
Views
221
MARK858
MARK858
willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
400
willow1985
willow1985
S
  • Question Question
Dynamic Address by VBA
Replies
8
Views
212
MikeVol
MikeVol
R
  • Question Question
I keep getting the error 91 but i cant figure out what is wrong here.
Replies
2
Views
61
mumps
mumps

Forum statistics

Threads
1,224,880
Messages
6,181,532
Members
453,054
Latest member
ezzat

Share this page

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