Combine multiple workbooks and preserve source formatting?

[EvolvedFromQuincy]

Hi all,

I am trying to combine the data from daily .xlsm reports into one sheet with all of that data for each calendar year. The catch is that the data is color coded via row highlights, which is the basis of its usefulness. Using "Get Data>From File>From Folder," is there a way to preserve the color highlights? Or is there any other method available? The text data alone is unfortunately useless to us without the relevant color highlights.

I realize that color highlights are perhaps not the best way to convey this necessary information, but it is what I have to work with at present.

Many thanks

 

[QuestionBaker]

I am not really aware of the

"Get Data>From File>From Folder,"

Will like to learn what it is.

I have gathered data from other files earlier.
I tend to use copy method if I want to preserve the original fomatting.

syntax is something like this.
SourceWorkbook.Sheet.Range(<range to copy>).Copy DestinationWorkbook.Sheet.Range(<Starting cell where to copy>)

To make this work, I have to open the workbook (don't forget to close without saving it).

While pasting, make sure you keep on changing the sheet of the cell so that you don't overwrite the previous data.

WARNING: Always have backup of your data, if you accidentally overwrite something with a macro, you can't undo to get the original data back

I've pasted the code that I use. (might not run as I deleted everything that wasn't necessary, but it conveys the message)
The code assumes all files from where we want to extract data are located in a folder call Source and the xlms file is located outside the folder.

Option Explicit
Public i As Integer
Public srcWb As Workbook, wb As Workbook
Public srcWs As Worksheet, datWs as Worksheet
Public oFSO As Object, oFolder As Object, oFile As Object


Sub define_wb(temp As Integer)
    Set wb = ThisWorkbook
    Set datWs = wb.Sheets("destination")
End Sub


Sub upData()
    Call define_wb(1)
    Application.ScreenUpdating = False
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder(wb.Path & "\Source")
    i = 0
    For Each oFile In oFolder.Files
        Set srcWb = Workbooks.Open(wb.Path & "\Source\" & oFile.Name)
        Set srcWs = srcWb.Sheets("source")

        srcWs.Range("A1:D5").Copy datWs.Cells(1, 1 + 5 * i)

        srcWb.Close SaveChanges:=False
        i = i + 1
    Next oFile
    Application.ScreenUpdating = True
End Sub

 

[EvolvedFromQuincy]

Thank you for replying. This is what I was trying to use at first without success:

All of the files have the same structure, so it works great for compiling the text data, but unfortunately it stripped all highlighting.

I have minimal VBA experience, but I will try to see if I can modify the macro to run from my end and mark as solution if so. Also, before I run it I should ask, is it coded to create multiple worksheets or just one? I'm trying to get all data into a single worksheet.

Thanks again

 

[SpillerBD]

Highlights is not a data point. Any formatting is ignored on data import.
If the same reason for the formatting can be calculated, it should be be done as another field. There should be a macro available to give your formatting a value, though that's not in my repertoire

 

[QuestionBaker]

srcWs.Range("A1:D5").Copy datWs.Cells(1, 1 + 5 * i)

It copies in the same sheet,
play around with the ranges.