help requested on VBA for combining data over multiple sheets

Kellens

New Member
Joined
Aug 21, 2014
Messages
41
Hello,

I have a large number of sheets in my workbook that i would like to combine into one sheet. Every sheet has a similar setup and has 2 rows:

  • the first has the coloumn headers in it
  • the second has a number of values


the first column contains a unique article number

What i would like to do is have a script that adds all these rows below each other on one sheet.
The values should however be filled in under the corresponding header...
You can find an example excell with the tabs i would like to combine here:

http://www.sonic-websites.be/blog/wp...t-bosch21.xlsx

In the tab result you can see what i'm trying to accomplish (mockup).
I really hope someone could help me with this!!!

For those that don't like to download stuff below a small sample...

Sheet 1
ID
attribute 1
attribute 2
attribute 3
attribute 4
654362168
value
654
value 545
5

<tbody>
</tbody>

Sheet 2
ID
attribute X
attribute 4
attribute 1
attribute Y
1255
value X
123
value 2
Value Y

<tbody>
</tbody>

Combination - result
ID
attribute 1
attribute 2
attribute 3
attribute 4
attribute X
attribute Y
654362168
value
654
value 545
5
1255
value 2
123
value X
value Y

<tbody>
</tbody>

Thanks,
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Ok so how I did it (and I'm sure there are alternatives out there) was to create a few named ranges and run a small bit of VBA. You could have the named ranges methodology in VBA but I like to be able to control ranges should I wish to adjust them later.

The first thing to note is that I have assumed that all of your data on every sheet starts in cell A1. I have also called your combination tab Combination
I then created the following Named ranges - there are quite a few to help break down each step which I find easier to manage:
xlBigText =REPT("z",255) (This provides a long string of text)
xlBigNum =10^308 (This provides a long number)
lng_CombineT =MATCH(xlBigText,Combination!$A:$A,1) (This looks for the long string of text in Column A and when it stops I know it is on the last cell - I need to do the same with numbers and then I know I definitely have the last cell)
lng_CombineN =IFERROR(MATCH(xlBigNum,Combination!$A:$A,1),1) (Slightly more needed as the header offers something to evaluate in lng_combineText but there are no values at all as of yet in column A)
lng_Combine =IF(lng_CombineN>=lng_CombineT,lng_CombineN,lng_CombineT)+1 (This gives me the next empty cell)
drng_Combine =INDEX(Combination!$A:$A,lng_Combine,1) (Provides the range in which to use in VBA as the next available cell)

Code:
Option Explicit

Public Sub Combine()
    Dim wks         As Excel.Worksheet
    Dim rngNextRow  As Excel.Range

    For Each wks In ThisWorkbook.Worksheets
        If Not wks.Name = "Combination" Then
            Set rngNextRow = Range("drng_Combine")
            Call wks.Range("A2:G2").Copy(Destination:=rngNextRow)
        End If
    Next

End Sub
 
Last edited:
Upvote 0
Quick glance at your names and I can see you wish to deliver them in a tab called RESULT - I called my tab Combination. Therefore if we use your RESULT tab the named ranges would be:
lng_CombineT =MATCH(xlBigText,RESULT!$A:$A,1)
lng_CombineN =IFERROR(MATCH(xlBigNum,RESULT!$A:$A,1),1)
drng_Combine =INDEX(RESULT!$A:$A,lng_Combine,1)


You would also need to slightly tweak your code to refer to the "Result" tab as this is where you are likely getting your runtime error.
I've also extended your range to include up to column V as I found one tab with lots of data in.

Code:
Option Explicit

Public Sub Combine()
    Dim wks         As Excel.Worksheet
    Dim rngNextRow  As Excel.Range

    For Each wks In ThisWorkbook.Worksheets
        If Not wks.Name = "RESULT" Then
            Set rngNextRow = Range("drng_Combine")
            Call wks.Range("A2:V2").Copy(Destination:=rngNextRow)
        End If
    Next

End Sub
 
Upvote 0
The name of the tab doesn't matter to me...
So i've created a tab called "Combination" and tried this (both a tab withouth data and one with some sample data) and tried to run the VBA.

I get the same error.... Range of object global failed
 
Last edited:
Upvote 0
You will get this error if the names aren't perfect - an alternative would be to adjust the VBA code:
Code:
Option Explicit

Public Sub Combine()
    Dim wks         As Excel.Worksheet
    Dim lngLastRow  As Long

    For Each wks In ThisWorkbook.Worksheets
        If Not wks.Name = "RESULT" Then
            With ActiveSheet
                lngLastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row + 1
                Call wks.Range("A2:V2").Copy(Destination:=.Cells(lngLastRow, 1))
            End With
        End If
    Next

End Sub

Now you don't need the names but you do need to select the Result tab before running the VBA (you also don't need the Combination tab anymore)
 
Upvote 0
ok this seems to work :) at least i'm getting the first result

The only issue now is that i don't see the headers yet... The values seem to get pasted correctly but i now don't know what headers there are.

Thanks again for your help
 
Upvote 0
The columns with different headers seem to be mixed...
For instance the header "title" and "specificatie" seem to be mixed by some reason.

If you want i can send a new version of the excel so you can see the result for yourself.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top