How to paste link when combine worksheets into one

[UongSaki]

I got this code:

Sub Combine()
Dim J As Integer

On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"

' copy headings
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")

' work through sheets
For J = 2 To Sheets.Count ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Range("A1").Select
Selection.CurrentRegion.Select ' select all cells in this sheets

' select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub

from

http://excel.tips.net/Pages/T003005_Condensing_Multiple_Worksheets_Into_One.html

How do you paste link the data onto the combine sheet?

Thank you.

 

[NWBeaver]

Try replacing this:
' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)


With this:
' copy cells selected in the new sheet on last line
Selection.copy
Sheets(1).Range("A65536").End(xlUp)(2).Select
ActiveSheet.Paste Link:=True

 

[SkipVought]

Hi,

What do you want to do in the combine sheet (sheets(1))

Combine the DATA or list hyperlinks to the data on other sheets? I am confused, because you are COPYING data, which leads to a paste of the DATA, not a link!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Here's what I THINK that you might want to do...

Sub Combine()
    Dim J As Integer, ws As Worksheet

    Set ws = Worksheets.Add(Before:=Sheets(1))     ' add a sheet in first place
    ws.Name = "Combined"

    ' copy headings
    Sheets(2).Range("A1").EntireRow.Copy _
        Destination:=ws.Range("A1")

    ' work through sheets
    For J = 2 To Sheets.Count ' from sheet 2 to last sheet
        With Sheets(J).[A1].CurrentRegion
    
            ' COPY all lines except title
            .Offset(1, 0).Resize(.Rows.Count - 1).Copy _
            Destination:=ws.Range("A65536").End(xlUp).Offset(2)
        End With
    Next
    Set ws = Nothing
End Sub

This is WITHOUT using the Activate or Select methods, which ought to be avoided if at all possible.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UongSaki]

Thank you, Here’s what I want to accomplish:

I want to combine all data from different worksheets, all with same structure, into one big worksheet. The combine worksheet will be my master for analysis. I want to be able to change values in other worksheets and have the changes updated in the combine sheet. I hope it makes sense.

Thanks,

Saki

 

[SkipVought]

You have specified TWO different requirements.

1. "my master for analysis"

When you have one table, you want to be sure that its a valid table. The code you posted does not result in a VALID table, since it contains EMPTY ROWS. Change the Offset to ONE..

            .Offset(1, 0).Resize(.Rows.Count - 1).Copy _
            Destination:=ws.Range("A65536").End(xlUp).Offset([b]1[/b])

2. "have the changes updated in the combine sheet"

This takes much more complex code. Why do you have these separate sheets? Why can you not maintain ONE TABLE on ONE SHEET?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UongSaki]

Hi Skip:

I have over 100 cost center managers submitting their budget requests (in five different workbooks by division) and I need a way to combine them and analysis. I would review and make some changes to their requests and then send back to them. They (cost center managers) will have to resubmit their requests.

Thanks,

Saki

 

[NWBeaver]

Saki,

Note that your sample code, along with the changes I suggested, will only combine and update data contained on the worksheets in a single workbook.
Are you wanting to combine data from multiple workbooks?

 

[UongSaki]

Yes, eventually. I think I know how to move sheets from different work books into one & then combine them into one big worksheet.

Thanks,

Saki

 

[SkipVought]

I would review and make some changes to their requests and then send back to them."

What you have described can be done maintaining ONE TABLE. There are a number of techniques for selecting a subset of data...

Data > Filter

Data > PivotTable

Data > Get External Data > New database Query -- faq68-5829.

I would STRONGLY recommend having one table that you combine all data into and modify.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UongSaki]

Thank you Skip. Not sure how yet but I'll look into it.