Extracting data from a different workbook and creating multiple workbooks - how to format

[Holley]

Thanks for all the past help I've received from the forum! It has been a lifesaver! I have a question I hope you can help with. I have a very large file with usually 2,000 - 3,000 rows. I have a macro that will extract all the rows with matching data in column C and put them into a new workbook and save the file with the value of column C. My problem is that the format is lost when the new file is created. Can anyone shed any light to help me retain the format? Its mixed with date, currency, etc.

Thanks again!

 

[QuietRiot]

Do you have a snippet of the code? Probably just need to include the formatting depending on how it's putting data in the new workbook

 

[Holley]

I run this first...

then this

 

[Holley]

Its set to email them, but I don't want to do that, so I've removed it from the code.

 

[QuietRiot]

So I think you just need to change it from Cells.PasteSpecial xlPasteValues to Cells.PasteSpecial xlPasteAllUsingSourceTheme

 

[Holley]

Thanks!!! I'll try that!

 

[Holley]

The data is perfect!! But, it didn't retain the layout of the page. I need for it to be landscape, narrow margins and fit all columns to one page. Would that be possible?

 

[QuietRiot]

You'll want to add this right before you save it.

  With Destwb.PageSetup
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .Orientation = xlLandscape
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With

 

[Holley]

I'm sure I haven't entered this in the correct place. I get a Run-time error '438': Object doesn't support this property or method. I entered it just before:

'Save the new workbook, email it, and close it
'Set otlNewMail = otlApp.CreateItem(olMailItem)
With Destwb
.SaveAs FolderName _
& "\" & Destwb.Sheets(1).Name & FileExtStr, _
FileFormat:=FileFormatNum
End With

Thanks again for your help and please pardon my ignorance.

 

[QuietRiot]

I'm sure I haven't entered this in the correct place. I get a Run-time error '438': Object doesn't support this property or method. I entered it just before:

Thanks again for your help and please pardon my ignorance.

I forgot the sheet..

     With Destwb.Sheets(1).PageSetup
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .Orientation = xlLandscape
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With