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

Need a VBA Macro to consolidate multiple worksheets to one Master

A

ananthram

New Member
i have document which needs to be consolidated in one Sheet called
Code: 
Master
. Please find the data file below which has 305 tables with same columns but the rows Number change.


https://dl.boxcloud.com/bc/1/c84c83f08fa09e426213d081165fabf5/2Of-pgQHiFlfW6KldVyHAxtUIEW0l7HoOpi14-VGcxcSnn2CtobCO1UjJZLI58w_oCTNcPMKbUR_gn-ncDkAbw,,/edc6503ab54123d0defd05c635ac0547/
 
Tried your link and got:


The link you're trying to access can't be used to share files. Please ask the file owner to provide you with a shared link instead. Contact Box Support if you need help.


Regards,

H
 
Hi Ananthram ,


Can you see if this is OK ? Please keep a backup of your file , before you run the macro. You need to add a new sheet and name it Master before you run the macro ; copy this macro to the sheet section of this new sheet.


One more thing that I have done is to create a named range called Start_Cell , and in the Refers To box , put in =!$A$2

[pre]
Code: 
Public Sub Consolidate_Sheets()
Const FIRST_SHEET_NUM = 1
Const LAST_SHEET_NUM = 305
Const SHEET_NAME_TEXT = "Table "

Dim master As Worksheet, wks As Worksheet
Dim Start_Cell As String, snum As String
Dim i As Integer, j As Integer

Set master = ThisWorkbook.Worksheets("Master")
Start_Cell = ActiveSheet.Range("Start_Cell").Address

For i = FIRST_SHEET_NUM To LAST_SHEET_NUM
snum = SHEET_NAME_TEXT & i
Worksheets(snum).Activate
ActiveSheet.Range(Start_Cell).Select
ActiveSheet.Range(Selection, Selection.End(xlDown).End(xlToRight)).Copy
master.Range(Start_Cell).Offset(j).PasteSpecial xlPasteAll
Application.CutCopyMode = False
master.Activate
j = j + Selection.Rows.Count
Next
Set master = Nothing
End Sub
[/pre]
Narayan
 
hi Sir,


thank you for your quick response, as u said above i have created a sheet called Master and when i run the macro i am getting 400 error code.


and please let me know how can i combine sheets if the sheets consists with Merged cells and i need only the unmerged cell's content. it's in same format as file in above link but A1 to H1 will be merged and A1 to the A(end cell of content.
 
Hi Ananthram ,


Have you created the named range Start_Cell ?


If I need to upload your file , what website can you download from ? SkyDrive , Google Docs , DropBox , SpeedyShare ?


I don't understand your second statement ; How can A1 through H1 be merged when each cell contains its own data ?


Narayan
 
Hi Ananthram,


As per Narayank code its working perfectly.


Please download the below file and check


http://www.2shared.com/file/QpWM6klU/q_1_.html


Thanks,

Suresh Kumar S
 
Oh... thanks a lot Narayan and Suresh sir. But sir i have another files which has a criteria like second statement so i need a macro for them as well.
 
Hi,


Can you please download the below file and chekc.


http://www.2shared.com/file/GWHYhhEX/M_online.html


Thanks,

Suresh Kumar S
 
You must log in or register to reply here.
Back
Top