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

**CaatalyyN616** · 11-21-2012, 01:09 PM

( 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 !

**D_Step** · 11-21-2012, 01:39 PM

Better resolution below.

**CaatalyyN616** · 11-23-2012, 01:25 PM

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 !

**mike7952** · 11-23-2012, 01:39 PM

Here's another

Sub abc()
 Const shBill As String = "BILL"
 Dim ws As Worksheet
 Dim a
 
 For Each ws In Worksheets
    With ws
        If .Name <> "BILL" Then
            a = .Range("a2", .Cells(Rows.Count, "c").End(xlUp))
            With Worksheets(shBill)
                .Cells(Rows.Count, "a").End(xlUp).Offset(1).Resize(UBound(a), UBound(a, 2)) = a
            End With
        End If
    End With
 Next
End Sub

**CaatalyyN616** · 11-28-2012, 08:24 AM

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 !

**mike7952** · 11-28-2012, 09:09 AM

See if this will work for you

Sub abc()
 Const shBill As String = "BILL"
 Dim ws As Worksheet
 
 For Each ws In Worksheets
    With ws
        If .Name <> shBill Then
            With Worksheets(shBill)
                ws.Range("a2", ws.Cells(Rows.Count, "c").End(xlUp)).Copy .Range(.Cells(Rows.Count, "a").End(xlUp).Offset(1).Address)
            End With
        End If
    End With
 Next
End Sub

**CaatalyyN616** · 11-28-2012, 12:48 PM

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

If .Name <> shBill Then

???

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 :

Workbooks.Open Filename:=Cells(1, 15) & Cells(3, 4)

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

**mike7952** · 11-28-2012, 03:58 PM

See if this is what you what. Change the 2 areas in red to your needs.

Sub abc()
 Const shBill As String = "BILL"
 Dim ws As Worksheet
 Dim shList As Range, c As Range
 
 Set shList = Worksheets("Name of sheet with list").Range("a1:a10")
 
 For Each c In shList
    Set ws = Worksheets(c.Value)
    With ws
        With Worksheets(shBill)
            ws.Range("a2", ws.Cells(Rows.Count, "c").End(xlUp)).Copy .Range(.Cells(Rows.Count, "a").End(xlUp).Offset(1).Address)
        End With
    End With
 Next
End Sub

**CaatalyyN616** · 11-29-2012, 04:22 AM

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

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions