• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Compile Sheet

Hi aparvez007,


You have not specified how is your data is arranged in those 7 sheets? And how the data from these 7 sheets will be used for compilation?
 
Dear shrivallabha,


Data range is same in all sheet... i want compile all data in one sheet with the gap of 2 rows....


A1 to AP81... some row have formula...
 
Hi aparvez007,


You mean to consolidate data in one sheet as values with a gap 2 rows between 2 sheet's data.


Can you please tell us the layout e.g.

Headers are in row 1

Data starts from row 2 [the bottom row may vary] and is upto column Z etc.
 
Dear Shrivallabha,


earlier what i was doing just copy the data from Sheet1 range a1 to ap81 and paste in compile sheet. then from sheet2 same range and again paste in compile sheet a83 which go through ap164... repeating this 7 times.


Regards,

Parvez
 
There are two ways that we can go about this.


Macro as we are discussing. Test the macro on a backup. I have written comments for explaining the approach I have considered.

[pre]
Code: 
Public Sub Compile_Data()
'I have assumed:
'First Sheet is where you want to compile data from sheet no. 2 to 8
'Name of first sheet is 'Main'
Dim wsDest As Worksheet: Set wsDest = Sheets("Main")
Dim lngRow As Long

Application.ScreenUpdating = False

wsDest.Cells.ClearContents 'Empty the sheet Main before copying
lngRow = 1 'First sheet's data will go to row 1

For i = 2 To Sheets.Count 'Change to 8 if you have more sheets and you want only 7
With Sheets(i)
.Range("A1:AP81").Copy 'Range Reference is hard coded
wsDest.Range("A" & lngRow).PasteSpecial xlPasteValues
lngRow = lngRow + 82
End With
Next i

Application.ScreenUpdating = True

End Sub
[/pre]
2nd approach would be (since we are dealing with fixed ranges):

a. Goto 2nd sheet and Select range A1:AP81

b. Select Main sheet and select Range A1

c. Right Click | Paste Special | Paste Link

d. This will create a link to each cell

e. Goto 3rd sheet and Select range A1:AP81

f. Select Main sheet and select Range A83

g. Repeat process at step c.


do this recursively for all sheets.
 
@aparvez007,

Do you know about 3-D References in Excel?

You can get help from the link below

http://office.microsoft.com/en-us/excel-help/create-a-3-d-reference-to-the-same-cell-range-on-multiple-worksheets-HP010102346.aspx

or in Excel Press F1 and search for 3D References. By 3D Reference you can compile data from various sheets as you described in your problem.


Regards,


Muneer
 
You must log in or register to reply here.
Back
Top