|
Merge Worksheets w/Comments & Hyperlinks
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, |
Merge Worksheets w/Comments & Hyperlinks
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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Chilired" wrote in message ... 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, |
Merge Worksheets w/Comments & Hyperlinks
That did it; thought I had replaced it.
Thanks, "Ron de Bruin" wrote: 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Chilired" wrote in message ... 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, |
Merge Worksheets w/Comments & Hyperlinks
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" wrote: 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Chilired" wrote in message ... 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, |
Merge Worksheets w/Comments & Hyperlinks
I must go now for the weekend
Will reply sunday evening when I am back with a example -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Chilired" wrote in message ... 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" wrote: 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Chilired" wrote in message ... 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, |
Merge Worksheets w/Comments & Hyperlinks
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, "Ron de Bruin" wrote: I must go now for the weekend Will reply sunday evening when I am back with a example -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Chilired" wrote in message ... 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" wrote: 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Chilired" wrote in message ... 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, |
Merge Worksheets w/Comments & Hyperlinks
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 "Chilired" wrote: 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, "Ron de Bruin" wrote: I must go now for the weekend Will reply sunday evening when I am back with a example -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Chilired" wrote in message ... 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" wrote: 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Chilired" wrote in message ... 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, |
Merge Worksheets w/Comments & Hyperlinks
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 ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Chilired" wrote in message ... 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 "Chilired" wrote: 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, "Ron de Bruin" wrote: I must go now for the weekend Will reply sunday evening when I am back with a example -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Chilired" wrote in message ... 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" wrote: 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Chilired" wrote in message ... 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, |
Merge Worksheets w/Comments & Hyperlinks
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 ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... 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 ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Chilired" wrote in message ... 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 "Chilired" wrote: 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, "Ron de Bruin" wrote: I must go now for the weekend Will reply sunday evening when I am back with a example -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Chilired" wrote in message ... 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" wrote: 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Chilired" wrote in message ... 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, |
Merge Worksheets w/Comments & Hyperlinks
Ron, could you still provide the example to the previous question?
"Ron de Bruin" wrote: I must go now for the weekend Will reply sunday evening when I am back with a example -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Chilired" wrote in message ... 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" wrote: 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Chilired" wrote in message ... 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, |
Merge Worksheets w/Comments & Hyperlinks
Sorry I forget you
I try to do it today for you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Chilired" wrote in message ... Ron, could you still provide the example to the previous question? "Ron de Bruin" wrote: I must go now for the weekend Will reply sunday evening when I am back with a example -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Chilired" wrote in message ... 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" wrote: 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Chilired" wrote in message ... 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, |
Merge Worksheets w/Comments & Hyperlinks
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Chilired" wrote in message ... Ron, could you still provide the example to the previous question? "Ron de Bruin" wrote: I must go now for the weekend Will reply sunday evening when I am back with a example -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Chilired" wrote in message ... 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" wrote: 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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Chilired" wrote in message ... 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, |
| All times are GMT +1. The time now is 04:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com