Won't paste formatting for some reason...

amazingkarma

New Member
Joined
Aug 5, 2010
Messages
2
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!

Code:
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,446
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Aug 5, 2010
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,718
Messages
5,660,482
Members
418,580
Latest member
IrishDave2137

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
Top