Merging different sheets of a same workbook

Dear All ,

This is a very exciting task wherein i myself will be also trying ....


I am attaching 2 files ,bal - A input ( i.e ->input file )and Bal A output (->output file see page 2 ) to explain what i want ,


Basically what i have in input sheet is a excel workbook with data in various sheets .....like page 1 ,page 2 ,page 3 page 4 (see attached input file name bal - A input )

Now in page 1 ,i specify which page i want all records to get added ......

Example :say in page 1 i specify that i want data from page 2 to page 24 added in single page 2 (i do it in specific cell like in input sheet )

How it will run ?

The macro will start reading from teh starting page i specify ,there (since it is page 2 now ) in page 2 ,the 7th row where it is written page 1 of 26 should be removed

Now from each of the page 3 - 24 ,the first row (Headings )and the last written row(Mostly always 6th row-> if you observe where it is written page no 3 of 26 ,x of y etc ) should be deleted and the cropped data from each of the pages i.e page 3,4,5,6,7,8 etc should be added to data in page 2 ,

Finally the output should look like Bal A -output (see page 2 ),


I will also try solving it ,but i would really appreciate different attempts by pros here ,its a interesting problem and once this is solved ,i need to do a vlookup macro extending this project but in a different thread ,

But we will go step by step ,lets first get this right !!!!!

Is it possible that automatic boundaries get selected of dimmer shade and formatting as i have done in page 2 of output be created ,

Thanks and Humble regards ,


Amlan Dutta

Dear Dave ,

Thanks for your response ...i a getting a error "subscript out of range "when i tried step through it is at this line ,


Set WB2 = Workbooks("Bal A output B.xlsm")


So i a unable to know what results the macro generate ,

Thanks still,

Amlan

Actually what i want is that data from all pages other than page 2 get added to the page 2 ....in each of the pages the first row and the last row should get deleted after which the data should get added .....since first row and last row are only merged and we actually delete them,i don't think that should be a problem ....atually the input data is from a pdf excel conversion software .....i wanna merge all data in one sheet and thus the exercise !!!!!

You will note that not all pages contain tabular data and so the input page specifying the start page and end page for tabular data to give the macro a feed point linked to the value in the referenced cell in the page 1 ....

Humble regards ,


Amlan Dutta

I changed WB2 to the new file name but i yet get the error ,

Cant change part of a merged cell at

Rng.Copy Destination:=WB2.Worksheets(C6).Range("B1048576").End(xlUp).Offset(1, -1),


Regards ,


Amlan dutta

Dear Dave ,

Sorry to inform that your code doesn't work for me and so i attempt and solve it myself with a friend's help . Also to address your above trailing message regarding merged cells ,Merged cells Should not be a problem here since there is a pattern in each embed page like i have said above ,its just the first and the last row so they can be easily got ridden by deleting first and last row of each sheet

Well ,i applied my brains and i had a friend to help me overcome this tough one ,but trust me guys ,this works like magic

For people who have been following this thread ,i attach both input file (with macro ) and
output file .Please note very carefully my initial thread as to what i had set to establish .I had in Page 1, 3 rows and according to my data entered there ,i have connected my loop to take values dynamically realtime from the input sheet on page 1 ,

When i say parent sheet in the code below ,it means the sheet where values will come and collect ,when i say start sheet and end sheet i mean the sheet from which values will get collected and added to parent sheet ,

so say i say parent sheet is 2 and start sheet and end sheet is 3 and 25 respectively ,it means that data from sheet 3 to 25 will get added in sheet 2 (parent ) and i have a provison to type all this in sheet 1 to make it happen realtime when i press the macro button there ...besides to keep my work clean i do use a format button and clean button (with macros )to take care of style rendering post final output


For people who wanna see the magic unfold i advise to

1.)Press "BOOM "button on sheet 1 (Of attached file test Input )
2.)Secomd press Format button on sheet 1 ...at this point you will be taken to sheet 2
3.)Press Clean button on sheet 2


You should see Test Output as attached .......


Note in output file ,see page 2 for results ....also in the input as well as output file ,done treun the macro since it will throw a error ,its a one time use since data gets collected and added to page 2 so if we run again ,then it will throw bounds of error ....rest it works like a dream ....


The code goes like this ,

Please Login or Register  to view this content.

If anybody needs help understanding ,i can explain ....but this is what i seeked out initially ....the job is half done ,this thread stands complete with the solution attached ,but the solution itself is now a background for another thrilling adventure in which i wish few pros could help me since i need to do a VLOOKUP amongst output for multiple sheets created using this macro ,


I seriously miss my friend Jaslake (

Humble regards ,


Amlan Dutta