• 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.

Copy Multiple sheets to Single sheet with links ?

D

deardhanya1

New Member
Please help to Copy the data from multiple sheet having same ranges to single sheet with links to their individual base, so that can do the changes in Individual sheets and autoupdate to Summary Sheet. thanks
 
Hi deardhanya1,


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


Regarding uploading file,please see the below link:

http://chandoo.org/forums/topic/posting-a-sample-workbook


Now regarding your question, I would request you to please upload your workbook with sample data and tell us more about:


1)Criteria to copy data from multiple sheets, if any

2)Provide a manual output regarding how exactly you want the data to be displayed after consolidating in a single sheet


Looking forward to your sample workbook.


Kaushik
 
Thanks in advance for your support , please find below the Link to Sample File.


https://www.box.com/s/0bo680yvrkufgun6qhhx


Please see the Remarks & Review sheet for the Comments,as how we need the Data Presentation .


Hope somebody could help me to review this . Once again Many thanks for your time


Dhanya
 
Hi Dhanya,


I am really very sorry as your post got completely slipped out of my mind and I lost it.


Can you plz download the file from here?


http://speedy.sh/cUyWJ/Cosolidation.xlsm.xls


However, below here is the code to consolidate the data from "Summary-1" sheet onwards.I have added anew sheet "Consolidation-Sheet_new" where data would be consolidated.At this sheet, after Col AC I have placed a button (click me) which you need to click to run the macro.

[pre]
Code: 
Sub DataConsolidation()

Application.ScreenUpdating = False

Dim iCurWS As Integer
Dim WS As Worksheet
Dim i As Long

'Loop through worksheets from summary1 to end

'Number 5 corresponds to "Summary-1" where the data consolidation should start from

For iCurWS = 5 To Worksheets.Count

Set WS = Sheets(iCurWS)
WS.Range("A2:AC" & WS.Range("A" & Rows.Count).End(xlUp).Row).Copy
Worksheets("Consolidation-Sheet_new").Activate
Range("A" & Worksheets("Consolidation-Sheet_new").Range("A" & Rows.Count).End(xlUp).Row + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

Next iCurWS

'Remove the total payable, total paid and total variance data

For i = 2 To Worksheets("Consolidation-Sheet_new").Range("A" & Rows.Count).End(xlUp).Row + 1

If Worksheets("Consolidation-Sheet_new").Range("A" & (i)).Value = "" Then

Worksheets("Consolidation-Sheet_new").Range("A" & (i)).EntireRow.Delete

End If

Next

Application.ScreenUpdating = True

End Sub
[/pre]

This code is applicable to your original workbook where you may have > 50 sheets.


Regards,

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