asked on

VBA copy from multiple sheets one hidden

I am using the following to combine data from sheets.
'Ex', 'Ground', 'Intl' data should move to Summary sheet

I am stuck on some issues:
1. there is a blank between the data from these sheets.
2. I also need to copy from hidden sheet "Templates" row 2-4 to "Summary" row 1-2
What-if-scenario-v4.xlsm

Roy Cox

Are you trying to combine the data into one table. i.e. one header row but all the data from each sheet below it with no empty rows.
Also, will you be adding to this periodically?

Euro5

ASKER

Yes Roy - exactly. I need to have the header from 'Templates' and the data from each sheet with no spaces between.

The code should clear each time before compiling.
The entry/additions would only take place on each individual sheet.
Then this compilation would come before processing the data.

Does this help?

Roy Cox

I really think that you would be better having all the data on one sheet with an additional column that identifies the "type".

You have MergedCells which make this more difficult. You really should avoid Mergedcells.

The Total Row also interferes.

I would suggest that you convert the data to a Table, this would be simpler

Euro5

ASKER

Thanks @Roy

I have it set up this way because it is necessary for the users.
The data can be overwhelming, so we enter on 3 different sheets, but I need to combine for the code.
The code looks only at the "Summary".

I need those total rows, so they can see the totals of each group.

ASKER CERTIFIED SOLUTION

Roy Cox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Euro5

ASKER

I had it working, but now stuck again!
Can anyone help work with the way it is set up now?

Sub collate()
Dim wb As Workbook
Dim sh As Worksheet
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As Worksheet
Dim lr As Long, shAry
Dim reBD As Range
Sheet15.Visible = xlSheetVisible
Sheet15.Cells.Clear



Set sh = Sheets("Summary")
Set th = Sheets("Templates")

Sheets("Templates").Select
Range("a4:aj5").Select
With Selection.Copy
End With
Sheets("Summary").Select
Range("A1").Select
ActiveCell.PasteSpecial (xlPasteAll)


    For i = 1 To 1
        Set sh1 = Sheets("Dom Ex") 'Edit sheet name
        Set sh2 = Sheets("Ground") 'Edit sheet name
        Set sh3 = Sheets("Intl") 'Edit sheet name
        shAry = Array(sh1, sh2, sh3)
            For j = LBound(shAry) To UBound(shAry)
                lr = shAry(j).UsedRange.Rows.Count
                shAry(j).Range("A3:A" & lr).EntireRow.Copy _
                sh.Cells(Rows.Count, 1).End(xlUp)(1)
            Next
    Next
Application.ScreenUpdating = False
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Rows(1).RowHeight = 15
Rows(2).RowHeight = 57.75
Application.ScreenUpdating = True


End Sub

Open in new window

What-if-scenario-v4.xlsm