help with VBA program to import and add formatted files to one file

[carobiscuit]

Hi, I have a few things going on here -- i want to simplify and make it more elegant. I'm pretty new at this and am still learning.

This hodgepodge works pretty well in a step-through environment and adds all formatted data to the file I open each loop, but when I run the macro, it leaves off the last file each time. Can anyone help?



Sub AppendDataFinal()
'
' AppendData Macro
'
' Keyboard Shortcut: Ctrl+Shift+A
'


Application.Run "PERSONAL.XLSB!module14.importfile"

(this macro is:

Sub importfile()
'
Dim MyFolder As String
Dim MyFile As String
MyFolder = "C:\Users\Public\Documents\Cisco Reports"
MyFile = Dir(MyFolder & "\*.csv")
Do While MyFile <> ""
Workbooks.Open Filename:=MyFolder & "\" & MyFile
MyFile = Dir
Loop

End Sub
)


For Each wb In Workbooks
wb.Activate

Columns("A:O").Select
Selection.Delete Shift:=xlUp
Columns("O:AS").Select
Selection.Delete Shift:=xlUp


Selection.CurrentRegion.Select
Selection.Copy
Workbooks.Open ("filename.xlsx")
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Activesheet.Paste , False
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
ActiveWorkbook.Close savechanges:=False

Next wb

Workbooks.Open ("filename.xlsx")

Application.Run "PERSONAL.XLSB!Module11.DateFormat"



End Sub