Question
I'm trying to do something that may be very simple using Excel but I can't find how to do it.
I have several worksheets, each one contains a single table. To give you an example, here are two of my worksheets :
First worksheet. Please note that lines are green. Some data. Without 's', sorry :-)
Second one. Lines are grey but cells can be red. Headers are NOT the same
I would like to :
- merge those tables into another worksheet
- keep style formatting (line color, etc)
- when I update a table, the merged one should update too (may be a macro)
Here is a possible output :
Note that same headers are correctly merged.
Can I do this with Excel ? I know that I can do a query (UNION ALL) but doing this doesn't preserve style formatting.
Edit 1
I've tried to build the table using microsoft queries. Here is my query :
SELECT * FROM `C:\Users\cflodrops\Downloads\comptes.xlsx`.`Purchases$` `Purchases$` UNION ALL SELECT * FROM `C:\Users\cflodrops\Downloads\comptes.xlsx`.`Sales$` `Sales$` UNION ALL SELECT * FROM `C:\Users\cflodrops\Downloads\comptes.xlsx`.`Trades$` `Trades$` UNION ALL SELECT * FROM `C:\Users\cflodrops\Downloads\comptes.xlsx`.`Transfers$` `Transfers$` This request works great, here is the result :
But I still have issues :
- style formatting is incorrect (background colors and numbers format)
- datas are not synchronized between arrays. It's not an issue, I can create a macro to execute the request whenever I click on a button.
3 Answers
Answers 1
you have a few problems...union can work. Assuming union path from your first edit.
Add placeholder columns on both sheets to allow proper union, OR force placeholders columns in with your t-sql queries.
Add columns to each sheet representing the source OR add these in with your t-sql queries. e.g. 'Sheet1'
On your unioned sheet with the results, add conditional formatting, where the row with the cell of the source type is evaluated, then the entire row is formatted as necessary. e.g. cell A2 has 'Sheet1', then row A is colored green.
Answers 2
If i understand you well, you want to:
- copy data preserving formatting from existing sheets and put them into another sheet (new or existing; let's call it "merged data"),
- keep one-way synchronization betweem source sheets and "merged sheet", which means when source sheet is changing, a "merged sheet" changes too.
The answer for both issues is YES depending on a way you choose to merge/synchronize/display data...
Method #1 - using copy & paste data one below another
This method will preserve formatting, but there's no chance to update destination table (sheet) when source data have been changed. You'll need to create it every time you want to see merged data.
Method #2 - using Range.CopyFromRecordset method together with UNION statement
If you want to use this method, you have to change your data set into model of relational database. There's no chance for preserving source formatting, but "merged sheet" may be quickly reloaded (a'ka updated).
Both methods of copying data between sheets you'll find here: Copy Data Between Excel Sheets using VBA, but this tip does not provide information about copying data within its originall format. So, i decided to clarify how to achieve that and show/provide code which create destination sheet with merged data.
In my example i have 3 sheets. First and second sheet is used as source of data and the 3. one is used to display merged data. Only 3 columns (A-C) contain data, so ranges to copy are hard-coded.
Option Explicit 'method #1 Private Sub CopyWithFormatting(srcSh As Worksheet, dstSh As Worksheet, Optional sCol As String = "A") Dim e1 As Long, e2 As Long On Error GoTo Err_CopyWithFormatting 'last row in src and dst sheet e1 = srcSh.Range(sCol & srcSh.Rows.Count - 1).End(xlUp).Row e2 = dstSh.Range(sCol & dstSh.Rows.Count - 1).End(xlUp).Row + 1 'do not refresh screen Application.ScreenUpdating = False 'copy defined range srcSh.Range("A2:C" & e1).Copy 'paste below existing data With dstSh.Range("A" & e2) .PasteSpecial xlPasteAllUsingSourceTheme .PasteSpecial xlPasteFormats .PasteSpecial xlPasteValidation End With Exit_CopyWithFormatting: On Error Resume Next 'restore default settings With Application .CutCopyMode = False .ScreenUpdating = True End With Exit Sub Err_CopyWithFormatting: MsgBox Err.Description, vbExclamation, "Err no. " & Err.Number Resume Exit_CopyWithFormatting End Sub How to use it?
Sub TestMethod1() Dim CopiedSheet As Worksheet, DestSheet As Worksheet 'as i mentioned -> 3. sheet is used to merge data 'you can change it by using sheet's name or its index Set DestSheet = ThisWorkbook.Worksheets(3) 'copy data from sheets into destination sheet For Each CopiedSheet In ThisWorkbook.Worksheets 'ignore destination sheet If CopiedSheet Is DestSheet Then GoTo SkipNext CopyWithFormatting CopiedSheet, DestSheet SkipNext: Next End Sub Finall note: Feel free to change above code to your needs.
Answers 3
In the third sheet I would have a macro in the Activate event, that pulls the data from from the first two sheets. I would add a hidden column in the third sheet that contains to originating sheet. In your conditional formatting for the third sheet you would need to base your formatting on the value in the hidden column. I can post a sample to clarify if you wish.
EDIT: Added sample
Private Sub Worksheet_Activate() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim Sheet1 As Worksheet Dim Sheet2 As Worksheet Dim Sheet3 As Worksheet Set Sheet1 = Worksheets(1) Set Sheet2 = Worksheets(2) Set Sheet3 = Worksheets(3) Sheet1.Range("A2:D101").Copy Sheet3.Cells(2, 1).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Sheet2.Range("A2:D101").Copy Sheet3.Cells(102, 1).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Sheet3.Cells(1, 1).Select ActiveWorkbook.Worksheets("Sheet3").AutoFilter.Sort.SortFields.Clear ActiveWorkbook.Worksheets("Sheet3").AutoFilter.Sort.SortFields.Add Key:=Range _ ("A1:A201"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Sheet3").AutoFilter.Sort .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub Here is an example file. ConcatSheets Example.xlsm
0 comments:
Post a Comment