vba merge multiple sheets into master workbook, copy , paste values & source FONT formatting

[powercell99]

Morning,

I am trying to merge several worksheets from different workbooks into a master workbook. Because the users of the source workbooks aren't particularly skilled at excel, I like to use the paste values method to avoid copying unintended data into the master (like external links, named ranges, etc). But a couple of the source workbooks prefer to use the strike-through font format to identify changes/deletions they have made to their workbook. When that data is then copied and pasted as values into the master workbook the strike-through font formatting is not copied, so we cannot tell that it was something that was intended to be displayed as something that was deleted. Is there a way to copy and paste values but keep the Font Strike-through formatting from the source? Or would i have to Paste All and worry about cleaning up any external links and named ranges etc. a different way??

    DataSheet1.Range("A2:X" & LastRowDataSheet1&).Copy    OutSheet1.Range("A" & LastRowOutSheet1&).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False    Application.CutCopyMode = False

Any suggestions would be greatly appreciated. Have you encountered this before??

larry

 

[dmt32]

With PasteSpecial you can specify one or more of the required PasteTypes needed.

e.g.

DataSheet1.Range("A2:X" & LastRowDataSheet1).Copy
 
    With OutSheet1.Range("A" & LastRowOutSheet1)
           .PasteSpecial Paste:=xlPasteValues
           .PasteSpecial Paste:=xlPasteFormats
           .PasteSpecial Paste:=xlPasteColumnWidths
    End With
    
    Application.CutCopyMode = False

You will need to adjust to meet specific project need - More info in your VBA helpfile

Dave

 

[powercell99]

Dave,
That works great. Thanks for the very quick response and suggestion. I appreciate it mucho!

Larry