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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

JoeMo

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

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,133
Messages
5,509,353
Members
408,729
Latest member
Rajesh M

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top