Won't paste formatting for some reason...

[amazingkarma]

First off I want to say that I've been a LONG time visitor of this site and can't thank the people here enough for all the help over the years. I've learned quite a bit, but am still a work in progress when it comes to VBA in excel.

The problem I have is that my macro will paste the values, but not the formatting of the copied area. I've tried to break it into two steps, pastespecial values and formatting, but it errors out. Any help would be MUCH appreciated.

So far running it from workbook A, it searches a selected folder for the newest excel file (workbook B), opens it, performs some filtering, deleting, then copies, closes workbook B and pastes it into workbook A. But it only pastes the data, not the formatting. The reason the naming conventions are odd is because while workbook A will always have the same name, workbook B will always change.

Thanks again!

Option Explicit
Const TXTFILE_FOLDER = "xxxxxxxxxxxxx"    'Put actual path here.
 
Sub OpenNewestCTBFile()
Dim fs As Object
Dim objFolder As Object
Dim objFile As Object
Dim mostRecentFile As Object
Dim mostrecentCreationDate As Date
mostrecentCreationDate = DateValue("1/1/1900")
 
Set fs = CreateObject("Scripting.FileSystemObject")
Set objFolder = fs.GetFolder(TXTFILE_FOLDER)
 
For Each objFile In objFolder.Files
'Change ".txt" to the correct extension if needed, like ".xls" or ".csv"
If InStr(objFile.Name, ".xls") > 0 Then
If objFile.DateLastModified > mostrecentCreationDate Then
mostrecentCreationDate = objFile.DateCreated
Set mostRecentFile = objFile
End If
End If
Next
If Not mostRecentFile Is Nothing Then
Workbooks.Open Filename:=mostRecentFile
    Range("A1:Y65535").Select
        Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers
    Range("F2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-4],'[062210 GE Commit test.xls]Customer Codes'!R1C1:R25C1,1,FALSE)"
    Selection.AutoFill Destination:=Range("F2:F65535"), Type:=xlFillDefault
    Range("F2:F65535").Select
    Range("A1").Select
    Selection.AutoFilter Field:=6, Criteria1:="#N/A"
    Rows("2:65535").Select
    Selection.Delete Shift:=xlUp
    Selection.AutoFilter Field:=6
    Columns("F:F").Select
    Selection.Delete Shift:=xlToLeft
    Rows("1:10000").Select
    Selection.Copy
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
    ThisWorkbook.Activate
    Sheets("Complete CTB").Activate
    Rows("3:10002").Select
    ActiveSheet.Paste
    Range("F4").Select
    
End If
End Sub

 

[JoeMo]

If the code runs ok except for not pasting the formatting, you might try not closing the source workbook until the paste is completed to see if that resolves the formatting issue.

 

[amazingkarma]

I tried that, but couldn't figure out how to get and forth between workbooks because workbook B had a different name each time and was now the value mostRecentFile. Every time I attempted to reference it, it would error out. Hence the copy then immediate close.

Thanks for the help.