Macro to get/copy data from multiple secondary sheets to main sheet

( Newbie here )

Hello ,

Straight to the ( my ) problem :

* I need a macro that gets / copy ( not cut ) the data from sheet n+1 , n+2 , n+3 ( let's call those sheets : TOM ) and so on into sheet n ( main one = BILL ) .
- I have 3 collumns ( A/B/C) with data in each sheet TOM , that is starting from 2nd row and will have to be copied into BILL also from 2nd row downwards
- every sheet TOM has a different numbers of rows with data all the time ( this happens every time I run another macro ... )
- I want to copy just the values , not the entire formula form cells , if I will implement that sort of thing in the future

* How do I want this macro to be done :
- all the data (values) form sheet TOM 1 , collumn A row 2 to be copied in BILL collumn A row 2 , same thing , separately , with collumn B and C
- then , all the data (values) form sheet TOM 2 , collumn A row 2 to be copied in BILL collumn A row 2 , same thing , separately , with collumn B and C but with the catch : all the data has to be under the data already copied from TOM1
- and so on till I reach TOM n

- Another catch : BILL has to be an exception , I mean the data already in it should not be read and also copied like the data from TOM's ... in it ( hope you get this one )

- And another one : please write the macro divided like this :
-> main stuff
-> text for sheet TOM 1 divided for each collumn ( A/B/C )
-> text for sheet TOM 2 divided for each collumn ( A/B/C )
-> and so on till I reach TOM n ( beacause it has to be easier to replicate code , because I don't know how meny TOM's I will have in the end )
-> obviously ... end of code

I need this macro to work for the " Test Excel.xlsm " that is uploaded here ...

Hope I didn't wondered so far from my problem and I was understood .

Thanks in advance and I wish you all ... all the best in the world !

Cheers !

_________________________________________________

You make our world easier :D to work with !

Hey !!!

It's awsome !

I had just a few minutes to check it out and it's almost what I was asking for ... bottom line : AWSOME JOB mate / GJ ! and thank you for your help !

I'm not gonna have access to the PC in this week-end , but I want to change a little bit the macros and .. then .. maybe .. ( for sure ) post what I managed to do with them ( if this thread will remain active 4 future posts ) .

PS : and also no access to the internet ... darn

Again , thank you a lot !!!

Cheers all !

Hey all ,

I finally had some time to work / play with your " answers " and I found that they are not what I am looking for , well , not almost

* D_Step

- your code is the closest to my " needs " ( I think .. because is made step by step and I can change the code how I want , reffering to the TOM's ) but it lack's something very important ... you didn't put 1 condition that I wanted the most , and that condition is :

- every sheet TOM has a different numbers of rows with data all the time ( this happens every time I run another macro ... )

- in the " distant past ( not land ) .. far far away " , I made a code like yours but didn't know how to " forge " it to put the data exactly how I wanted it ....
= example : today I have 32 rows with data in TOM 1 , tomorow I'll have 21 rows with data in TOM 1 , the next day I'll have 57 rows with data in TOM 1 , allways a diffrent number of rows with data in TOM 1 ... and this same thing will happen with the other TOM's also

- bottom line , I need a code for BILL that will put the copyed data one after another downwards from each TOM and that will not care how much rows with data is in every TOM
- if you somehow manage to " forge " something within the first macro that you made .. that will be more than awsome for me

PS : thank you for the sort code of data , I didn't want it ... but it's good for the future

* mike7952

- first of all , thank you too for your help , but ... your code doesn't work with my excel .. I get the error " Compile error : Expected Array " when I try to run it ... it get's me into VBA and it's highlighting the " UBound(a) " code ( followed by " ,UBound(a,2) " code ) ...
- second ... , I don't know how to change it or combine it with the one that D_Step made .. to make it work in the end ... sorry for me beeing still a child in this VBA coding

- if you somehow manage to make a connection with my TOM's ( a code for each TOM I choose to run the macro .. ) within your code I will gladly give it a try ... you never know ... in the future how your code might help me with someting else .. but onestly , the macro that D_Step made , is more closer to what I need

( I know that writing a smaller code is better and faster , but I don't need this type of code right now .. no offense )

Thank you in advance for your help !!! and again ... you make our world much more easier ... to work with !

Cheers all !

Hey ,

Thank you mike7952 , now it's working how I want it ! with a small problem ... can I write a code that is making a reference to certain sheets from excel , instead of

Please Login or Register  to view this content.

???

Let's say I write down in a certain collumn all the names ( downwards ) from the sheets that I want to " extract " the data ( like a list ) ... and the macro will work only for them ... I know this is possible , but I don't know how to do it .. so .. in the end , can you help me .

I have a different macro that uses the code :

Please Login or Register  to view this content.

.. isn't there a code that takes the info from a list ( made by me in excel ) and then activate and do it's thing in that sheet and then passing to the next one ?

For the code above I sincerly thank you from all my beeing !!! * YOUR THE MAN !!!

Cheers all !

What can I say more ... your awsome man !

It's perfect ! I'm speechless ... really ... It's what I need !

Thank you for all your help and time !

This thread can be closed now .

Cheers all !

PS : this forum it's awsome and I'm glad that I found mike7952 who helped me a LOT !

Later ...