VBA Clean up & Speed up

[FawnCC]

I have the following code but the IF, ElseIf, ElseIf doesn't work and I want to get away from the Activate command but it keeps erroring with everything I've tried, possibily due to the Dir function.

Sub DataPull()


Dim Path, Name, Trend As String
Dim pos, FDate, M, Y, D As Integer
Dim wb, As Workbook
Dim NextCol As Long


FDate = Sheets("Summary").Range("H1").Value
M = WorksheetFunction.Text(FDate, "mm")
D = WorksheetFunction.Text(FDate, "dd")
Y = WorksheetFunction.Text(FDate, "yyyy")


'Designates the folder containing all of the files that will be affected by the macro
Path = "K:\MT\Data" & M & "-" & Y & "" & Y & "-" & M & "-" & D & ""
Name = Dir(Path & "*.xlsx")
Do While Name <> ""
Application.ScreenUpdating = False
Application.AskToUpdateLinks = False
Set wb = Workbooks.Open(Path & Name)


'Pulls the TrendAU from the file name
If VBA.Strings.Right(Name, 6) = "Growth" Then
Trend = "5980"
ElseIf VBA.Strings.Left(Name, 5) = "Manag" Then GoTo Skynet
ElseIf InStr(1, Name, "-") <> 0 Then
pos = InStr(1, Name, "-")
Trend = VBA.Strings.Left(Name, pos - 2)
End If


'Puts the TrendAU in where Actual or Forecast would go
Windows(Name).Activate
Sheets("Summary").Activate
Range("AE7:AP7").Value = Trend


'Finds the first empty column
ThisWorkbook.Activate
If Sheets("Data").Range("A1").Value = "" Then
NextCol = 1
Else
NextCol = Sheets("Data").Cells(1, Columns.Count).End(xlToLeft).Column + 1
End If


'Copies the data and pastes it as values in the location specified below
Windows(Name).Activate
Range("AE7:AP323").Copy
ThisWorkbook.Activate
Sheets("Data").Cells(1, NextCol).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


Skynet:
'Closes the Forecast file
Application.DisplayAlerts = False
Windows(Name).Activate
Workbooks(Name).Close SaveChanges:=False
Application.DisplayAlerts = True
Name = Dir
Loop
Application.ScreenUpdating = True




End Sub

 

[Joe4]

There are a bunch of things you can do to make your code more efficient.

1. TEXT and RIGHT are native Excel VBA functions. No need to preface them with "WorksheetFunction" or "VBA.Strings"

2. Your variable declarations aren't quite doing that you think:

[COLOR=#333333]Dim Path, Name, Trend As String[/COLOR]
[COLOR=#333333]Dim pos, FDate, M, Y, D As Integer[/COLOR]
[COLOR=#333333]Dim wb, As Workbook[/COLOR]

each one needs to be listed explicitly, like this (and note that you have an errant comma in your workbook declaration)

[COLOR=#333333]Dim Path[/COLOR][COLOR=#333333] As String[/COLOR][COLOR=#333333], Name[/COLOR][COLOR=#333333] As String[/COLOR][COLOR=#333333], Trend As String[/COLOR]
[COLOR=#333333]Dim pos[/COLOR][COLOR=#333333] As Integer[/COLOR][COLOR=#333333], FDate[/COLOR][COLOR=#333333] As Integer[/COLOR][COLOR=#333333], M[/COLOR][COLOR=#333333] As Integer[/COLOR][COLOR=#333333], Y[/COLOR][COLOR=#333333] As Integer[/COLOR][COLOR=#333333], D As Integer[/COLOR]
[COLOR=#333333]Dim wb As Workbook[/COLOR]

3. You shouldn't use reserved words (names or existing Properties, Methods, or Functions) like "Name" as the name of variables, procedures, or custom functions.

4. If you intend to run your code on other Workbooks or Worksheets, you may need to use "Activate" or "Select" in order to get to that sheet. If you are always running the code on the current sheet (the active sheet in the active workbook when the VBA is called), there is no reason to have to use these since you are already on the sheet your are running your procedure on.

5. When posting your code, it is very helpful to us if you use Code Tags so that your code is formatted and easier for us to read (see: https://www.mrexcel.com/forum/board...post-your-visual-basic-applications-code.html)