Merging Data

[VENKYS]

I have data in recd from various zones in a single file. The worksheets are named ... east, west, north, south, zonal, regional, etc.

I need to consolidate data in a new worksheet - "summary" which will contain data from COL A:F of Sheet 1 (EAST), and similarly, COL A:F of west, north, south ONLY. The data count (row count) may differ from every sheet every time. The data should be appended one below another, so that i get all the records in this sheet.

how should i go about...?

 

[asking]

Hi VENKYS, try ~

I would do it dynamically like this ~
As long as column A has no blanks. The first part counts how many rows and selects the range to be copied, copies it, then goes to the summary sheet and pastes the data in the first blank cell in column A. Then, just repeat the code for your other sheets.

Sub nnn()
Sheets("East").Select
Range("=OFFSET($A$1,0,0,COUNTA(OFFSET($A$1,0,0,9999)),6)").Select
        Selection.SpecialCells(xlCellTypeVisible).Select
            Selection.Copy
    Sheets("Summary").Select
    Range("$A65536").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    
    'Rest of code as Above, repeat for your other sheets just changing the references. (Sheets)
    
End Sub

Cheers.