Merge Worksheets w/Comments & Hyperlinks

[Chilired]

I successfully used the example from http:/www.rondebruin.nl.copy2.htm site
to merge 10 worksheets into one Master sheet; however the issue is I have
comments and hyperlinks on the individual sheets. Is is possible for the
comments to come with the data and also have active hyperlinks in the merged
Master document? The desired result will be to use Autofilter on the header
row of the Master document and protect the sheet for use on our Intranet
site. Is this possible? Please advise.

Thanks,

 

[Ron de Bruin]

See the Tips below the macro on
http://www.rondebruin.nl/copy2.htm

Or Replace this :

CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With
With:

CopyRng.Copy DestSh.Cells(Last + 1, "A")

If you want to copy everything.

 

[Chilired]

That did it; thought I had replaced it.

Thanks,

See the Tips below the macro on
http://www.rondebruin.nl/copy2.htm

Or Replace this :

CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With
With:

CopyRng.Copy DestSh.Cells(Last + 1, "A")

If you want to copy everything.

 

[Chilired]

Since the Master Sheet is replaced with each update; how may I code
formatting to include a Worksheet Header and set widths for colunm/row?
Additionally the first two rows of each WS are headers, but they do not
populate since the "start row" is 3 for each sheet.

Thanks,

 

[Ron de Bruin]

I must go now for the weekend
Will reply sunday evening when I am back with a example

 

[Chilired]

I am having an issue with the Master worksheet. Everything appears okay,
however when I assign Auto Filter to Row 2 and sort Column A - Ascending, the
remaining Columns B-F do not sort with Column A. Am I doing something wrong?
Does Auto Filter not work on a Merged worksheet?

Thanks,

 

[Chilired]

Additional comments to previous reply......Each Worksheet's Column A data was
assigned a hyperlink by (Selected A3:A45,etc); Ctrl+K; paste in hyperlink.
Now it appears that the hyperlink attached to the Row and not the actual data
in the row. The rationale for this conclusion: Initially I thought the data
was not sorting properly; subsequent investigation revealed data sorted
properly, what is not sorting is the hyperlink that was assigned to the data.
For instance A4 has data and hyperlink for "Contractor"; when data sorted;
"Office" data is in A4, but the hyperlink for "Contractor" remained in A4
attached to different data. How do I correct?

Chilired

 

[Ron de Bruin]

I have seen more problems with Hyperlinks but it is diffecult
to see your problem without seeing it.

That's the reason why it is better only to merge the values of all sheets and formulas and ?

 

[Ron de Bruin]

That's the reason why it is better only to merge the values of all sheets and formulas and ?

Must be

That's the reason why it is better only to merge the values of all sheets and not formulas and ?

 

[Chilired]

Ron, could you still provide the example to the previous question?

 

[Ron de Bruin]

Sorry I forget you

I try to do it today for you

 

[Ron de Bruin]

Try this one


Sub CopyDataWithoutHeaders()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim shLast As Long
Dim CopyRng As Range
Dim StartRow As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Delete the sheet "RDBMergeSheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("RDBMergeSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "RDBMergeSheet"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "RDBMergeSheet"

'Fill in the start row
StartRow = 3

'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> DestSh.Name Then

If WorksheetFunction.CountA(DestSh.Cells) = 0 Then
sh.Rows("1:2").Copy DestSh.Range("A1")
End If

'Find the last row with data on the DestSh and sh
Last = LastRow(DestSh)
shLast = LastRow(sh)

'If sh is not empty and if the last row >= StartRow copy the CopyRng
If shLast > 0 And shLast >= StartRow Then

'Set the range that you want to copy
Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If

'This example copies values/formats, if you only want to copy the
'values or want to copy everything look below example 1 on this page
CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

End If

End If
Next

ExitTheSub:

Application.Goto DestSh.Cells(1)

'AutoFit the column width in the DestSh sheet
DestSh.Columns.AutoFit

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub


Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function