Combing fileds from multiple workseets to a single worksheet

Dragon 71

New Member
Joined
Apr 18, 2013
Messages
10
HI

I have been asked to set up a function that automatically combines three separate worksheets into a another single worksheet , ordinarily I would copy and paste the required fields into the other workseet but my recipeints require this to be an automatic function.

Each of the three wokshhets only need fields A to L to be copied into the other workheet in the same workbook. The workseet will need the information to overwrite every time it is used. Also the info to be copied cotains formulae but I only need th values in the cells to be copied over.

Is there a formula that can do this?

I think there is a macro for this, but not having used macros before I could use soem siistance in how to set ths up please
.Thanks
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
This might work.
Code:
Sub combWS()
Dim lr As Long, rng As Range, dSh As Worksheet
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Consolidate").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set dSh = Sheets.Add(After:=Sheets(Sheets.Count))
dSh.Name = "Consolidate"
For i = 1 To 3
    With Sheets(i)
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        Set rng = .Range("A2:L" & lr)
        If Application.CountA(dSh.Range("1:1")) = 0 Then
            .Range("A1:L1").Copy dSh.Range("A1")
        End If
        rng.Copy dSh.Cells(Rows.Count, 1).End(xlUp)(2)
    End With
Next
End Sub
 
Upvote 0
Forgot about the formulas, use this one.
Code:
Sub combWS2()
Dim lr As Long, rng As Range, dSh As Worksheet
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Consolidate").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set dSh = Sheets.Add(After:=Sheets(Sheets.Count))
dSh.Name = "Consolidate"
For i = 1 To 3
    With Sheets(i)
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        Set rng = .Range("A2:L" & lr)
        If Application.CountA(dSh.Range("1:1")) = 0 Then
            .Range("A1:L1").Copy dSh.Range("A1")
        End If
        rng.Copy
        dSh.Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues
    End With
Next
End Sub
 
Upvote 0
Hi Dragon 71. A macro could certainly do this but we would need more information in order to help. The best help would be if we could see your actual file which you could upload to a free site such as www.box.com. The site will give you a link to the file which you could post on this forum.
 
Upvote 0
You might also want to add a line at the end as shown below:
Code:
Next
Application.CutCopyMode = False
End Sub
That will clear any crawling ants from the worksheets.
 
Upvote 0
HI
Thanks for that, Much appreciated I've managed to modify it ab it as there were hidden worksheets in the file a wasn't awre of thao I have changed the Line "For i = 1 To 3" to "For i = 3 To 5" and changed the sheet name form "Consolidate" to "Master Data 2".
However, contained in the origianal workbook is a sheet caled Master Data, is there any way of getting this code to consolidate on that existing sheet rather then creating a new one?
 
Upvote 0
H I Thaks for looking into this.
Unfortunately I cant release the detials on the file as its company specific data. The code I was sent above works. Although itcreates a new sheet where tha data is cosildated whereas ai really need to consolidate on as existin wrkshhet on the workbook. The sheet is called MAster data"...Any ideas?
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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