combine selected worksheet into one worksheet

[YOYO]

I need combine selected formatted data from different worksheet into one worksheet.

tab 1 called " statement" where all the combined data need go to
tab 2 "summary" is where the "statement" header where. This tab will not be including when I combine the data

tab 3 called " cash" which is formatted already ( using vba)
tab 4 called " check" which is formatted already (using vba)
tab 5.... tab 6.... tab 6...

I need: 1. show the formatted header from "summary"
2. add on tab 3 and tab 4, tab5, tab 6.... data after the header
3. all those tab won't be delete after it combined into "statement"


I have this code here which works before but not working properly now .

please help!!!!

code:

Sub CombineData()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Worksheets


    If Sht.Name <> "Data" And Sht.Range("A2").Value <> "" Then
        Sht.Select
        lastrow = Range("A65536").End(xlUp).Row
        Range("A1", Cells(lastrow + 1, "E")).Copy
        Sheets("Statement").Select
        Range("A65536").End(xlUp).Offset(1, 0).Select
        ActiveSheet.Paste
        Sht.Select
        Range("A2", Cells(lastrow, "M")).ClearContents
        Else
    End If


Next Sht


End Sub

[mdmackillop]

Sub CombineData()
    Dim Sht As Worksheet, Tgt As Range, LastRow As Long
    'Get headers
    Sheets("Summary").Rows(1).Copy Sheets("Statement").Cells(1, 1)
    'get data
    For Each Sht In ActiveWorkbook.Worksheets
        If Sht.Name <> "Statement" And Sht.Name <> "Summary" And Sht.Range("A2").Value <> "" Then
            Set Tgt = Sheets("Statement").Cells(Rows.Count, 1).End(xlUp)(2)
            LastRow = Sht.Range("A65536").End(xlUp).Row
            Sht.Range("A1").Resize(LastRow + 1, 5).Copy Tgt
        End If
    Next Sht
End Sub

[YOYO]

Hi Madmackillop,
It combined the data but its just not formatted...
I have the code for formatted the data from tab 3, tab 4, tab 5, so when i run the code, tab 3, tab 4 and tab 5 will be formatted and after they formatted, it goes to "Statement" tab, and i delete "get header" part from your code, because i have the code for formatted the header from tab" Summary", so on "Statement" it should be formatted header ( which i already have the code and it works), then formatted data from tab 3, 4, 5 ( which I already have the code and it works)

I used "Call sub combineData" and "Call sub formatedeposits" to combine the codes together.
I am not sure if that is the correct way to do.

Thanks again for your help!!!!

[YOYO]

Hi Mdmackillop,
I just made a small change and it works perfectly. I put " Call Deposits" first and then " Call combineData" so it works !!! Thanks you sooo much.
but i do have another problem now, i am trying to format the tab 3, the raw data look at below:

Column A Column B Column C Column D Column E
DATE DESCRIPTION AMOUNT ACCOUNT ACCOUNT NAME
5/1 ORIG CO NAME: CITI****** ***XX ***X 5.00 0000000000000000001 ABC
5/2 ORIG CO NAME: DALEY, DEVORAH ***XX ***X 5.00 0000000000000000001 ABC
5/3 ORIG CO NAME: PAYMENTECH***XX ***XX ***X 5.00 0000000000000000001 ABC
5/4 BOOK TRANSFER DEBIT A/C:*********XX 5.00 0000000000000000001 ABC

Need to change it to below:

Column A Column B Column C Column D
DATE DESCRIPTION DEBIT AMOUNT (add underline on Header)
5/1 ORIG CO NAME: CITI****** (Keep what is before the space) CITI 5.00
5/2 ORIG CO NAME: AMERICAN EXPRESS***XX (Keep what is before the space) AMEX 5.00
5/3 ORIG CO NAME: PAYMENTECH***XX (Keep what is before the space) PAYMENTECH 5.00
Subtotal DEBIT (BOLD) 15.00 ( add top and bottom double border here)
-----------INSERT A BLANK ROW

DATE DESCRIPTION DEBIT AMOUNT (add underline on Header)
5/4 BOOK TRANSFER DEBIT A/C (Keep what is before ":") AMEX 5.00
Subtotal DEBIT (BOLD) 5.00 ( add top and bottom double border here)


Thank you sooo much for your help!!!!

[mdmackillop]

Please post this as a new question with sample workbook showing before and after data