Change macro line to copy column within one range that has hyperlinks over to new range

S

sspatriots

Well-known Member
Joined
Nov 22, 2011
Messages
585
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have this line of code in my macro that sets a table range from another workbook (source) equal to a table range in a destination workbook that I'm bringing it into. However, the second column which is named "Job Name" has many hyperlinks in it that I would like to bring over into the destination workbook as well. I cannot figure out how to get the line of code below to bring over both all of the cell values from the source range as well as the hyperlinks that appear in the "Job Name" column at the same time or if I need to have 2 lines of code to do this. Any assistance would be much appreciated. Thanks, SS

VBA Code: 
ws1.Range("G2JobList[[#Data],[Job]:[Payment" & Chr(10) & "with" & Chr(10) & "Approval]]").Value2 = My_Range1.Value2
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
I came up with the following, although I was trying to avoid using a copy and paste method of getting the hyperlinks from one table to another. If someone has some insight on this, I would appreciate it.

VBA Code: 
Sub MigrateJobListTable()

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim file_path1 As String
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng1 As Range

Dim My_Range1, My_Range2, My_Range3, My_Range4, My_Range5, My_Range6, My_Range7, My_Range8, My_Range9, My_Range10 As Range
Dim My_Range11, My_Range12, My_Range13, My_Range14, My_Range15, My_Range16, My_Range17, My_Range18, My_Range19, My_Range20 As Range
Dim My_Range21, My_Range22, My_Range23, My_Range24, My_Range25, My_Range26, My_Range27, My_Range28, My_Range29, My_Range30 As Range
Dim My_Range31, My_Range32, My_Range33, My_Range34, My_Range35, My_Range36, My_Range37, My_Range38, My_Range39, My_Range40 As Range
Dim My_Range41, My_Range42, My_Range43, My_Range44, My_Range45 As Range
Dim LastRow1 As Long
Dim LastRow2 As Long
Dim LastRow3 As Long

Dim tb1 As ListObject

With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .CutCopyMode = False
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
End With

Set wb1 = ThisWorkbook
file_path1 = "H:\Jobs\00 ENGINEERING DATA\Job List.xlsm"      'Path to Job List.xlsm
Set wb2 = Workbooks.Open(Filename:=file_path1, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)     'Opens Job List.xlsm file in Read-Only mode
Set ws1 = wb1.Worksheets("Jobs")
Set ws2 = wb2.Worksheets("LogDetails")
Set ws3 = wb1.Worksheets("LogDetails")
Set ws4 = wb2.Worksheets("Jobs")

Set tbl = ws1.ListObjects("G2JobList")  'Destination table

LastRow1 = ws2.Range("A:A").SpecialCells(xlCellTypeLastCell).Row    'ws2 is the LogDetails sheet of the Source Workbook
LastRow2 = ws3.Range("A:A").SpecialCells(xlCellTypeLastCell).Row

Set My_Range42 = ws2.Range("A2", "F" & LastRow1)
Set My_Range43 = ws3.Range("A2", "F" & LastRow2)
Set My_Range44 = ws2.Range("G2", "G" & LastRow1)
Set My_Range45 = ws3.Range("G2", "G" & LastRow2)
LastRow3 = ws4.Range("G2JobList[#All]").SpecialCells(xlCellTypeLastCell).Row - 2
Set rng1 = ws1.Range("G2JobList[#All]").Resize(LastRow3, 164)
Set My_Range = Range("G2JobList[[#Data],[Job Name]]")
Set My_Range1 = ws4.Range("G2JobList[[#Data],[Job]:[Payment" & Chr(10) & "with" & Chr(10) & "Approval]]")
Set My_Range2 = ws4.Range("G2JobList[[#Data],[SITE Address]:[G1" & Chr(10) & "APPD Date]]")
Set My_Range3 = ws4.Range("G2JobList[[#Data],[G1 RLSD To" & Chr(10) & "ENGRG Date]:[G1 RLSD To" & Chr(10) & "PROD Date]]")
Set My_Range4 = ws4.Range("G2JobList[[#Data],[G1 CUST" & Chr(10) & "RQST Date]]")
Set My_Range5 = ws4.Range("G2JobList[[#Data],[G1" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Jack" & Chr(10) & "Vendor]]")
Set My_Range6 = ws4.Range("G2JobList[[#Data],[Jack" & Chr(10) & "PO]:[Jack" & Chr(10) & "REQD Date]]")
Set My_Range7 = ws4.Range("G2JobList[[#Data],[Jack" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Machine" & Chr(10) & "Vendor]]")
Set My_Range8 = ws4.Range("G2JobList[[#Data],[Machine" & Chr(10) & "PO]:[Machine" & Chr(10) & "REQD Date]]")
Set My_Range9 = ws4.Range("G2JobList[[#Data],[Machine" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Safety" & Chr(10) & "Vendor]]")
Set My_Range10 = ws4.Range("G2JobList[[#Data],[Safety" & Chr(10) & "PO]:[Safety" & Chr(10) & "REQD Date]]")
Set My_Range11 = ws4.Range("G2JobList[[#Data],[Safety" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Governor" & Chr(10) & "Vendor]]")
Set My_Range12 = ws4.Range("G2JobList[[#Data],[Governor" & Chr(10) & "PO]:[Governor" & Chr(10) & "REQD Date]]")
Set My_Range13 = ws4.Range("G2JobList[[#Data],[Governor" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Tail Sheave" & Chr(10) & "Vendor]]")
Set My_Range14 = ws4.Range("G2JobList[[#Data],[Tail Sheave" & Chr(10) & "PO]:[Tail Sheave" & Chr(10) & "REQD Date]]")
Set My_Range15 = ws4.Range("G2JobList[[#Data],[Tail Sheave" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Roller Guides" & Chr(10) & "Vendor]]")
Set My_Range16 = ws4.Range("G2JobList[[#Data],[Roller Guides" & Chr(10) & "PO]:[Roller Guides" & Chr(10) & "REQD Date]]")
Set My_Range17 = ws4.Range("G2JobList[[#Data],[Roller Guides" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[COMP Chain" & Chr(10) & "Vendor]]")
Set My_Range18 = ws4.Range("G2JobList[[#Data],[COMP Chain" & Chr(10) & "PO]:[COMP Chain" & Chr(10) & "REQD Date]]")
Set My_Range19 = ws4.Range("G2JobList[[#Data],[COMP Chain" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Ropes" & Chr(10) & "Vendor]]")
Set My_Range20 = ws4.Range("G2JobList[[#Data],[Ropes" & Chr(10) & "PO]:[Ropes" & Chr(10) & "REQD Date]]")
Set My_Range21 = ws4.Range("G2JobList[[#Data],[Ropes" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Oil Buffers" & Chr(10) & "Vendor]]")
Set My_Range22 = ws4.Range("G2JobList[[#Data],[Oil Buffers" & Chr(10) & "PO]:[Oil Buffers" & Chr(10) & "REQD Date]]")
Set My_Range23 = ws4.Range("G2JobList[[#Data],[Oil Buffers" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Rails" & Chr(10) & "Vendor]]")
Set My_Range24 = ws4.Range("G2JobList[[#Data],[Rails" & Chr(10) & "PO]:[Rails" & Chr(10) & "REQD Date]]")
Set My_Range25 = ws4.Range("G2JobList[[#Data],[Rails" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[CWT" & Chr(10) & "Vendor]]")
Set My_Range26 = ws4.Range("G2JobList[[#Data],[CWT" & Chr(10) & "PO]:[CWT" & Chr(10) & "REQD Date]]")
Set My_Range27 = ws4.Range("G2JobList[[#Data],[CWT" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Sling-PLATF" & Chr(10) & "Parts" & Chr(10) & "Vendor]]")
Set My_Range28 = ws4.Range("G2JobList[[#Data],[Sling-PLATF" & Chr(10) & "Parts" & Chr(10) & "PO]:[Sling-PLATF" & Chr(10) & "Parts" & Chr(10) & "REQD Date]]")
Set My_Range29 = ws4.Range("G2JobList[[#Data],[Sling-PLATF" & Chr(10) & "Parts" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Cab" & Chr(10) & "Vendor]]")
Set My_Range30 = ws4.Range("G2JobList[[#Data],[Cab" & Chr(10) & "PO]:[Cab" & Chr(10) & "REQD Date]]")
Set My_Range31 = ws4.Range("G2JobList[[#Data],[Cab" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[ENT" & Chr(10) & "Vendor]]")
Set My_Range32 = ws4.Range("G2JobList[[#Data],[ENT" & Chr(10) & "PO]:[ENT" & Chr(10) & "REQD Date]]")
Set My_Range33 = ws4.Range("G2JobList[[#Data],[ENT" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[FXTR" & Chr(10) & "Vendor]]")
Set My_Range34 = ws4.Range("G2JobList[[#Data],[FXTR" & Chr(10) & "PO]:[FXTR" & Chr(10) & "REQD Date]]")
Set My_Range35 = ws4.Range("G2JobList[[#Data],[FXTR" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[CONTR" & Chr(10) & "Vendor]]")
Set My_Range36 = ws4.Range("G2JobList[[#Data],[CONTR" & Chr(10) & "PO]:[CONTR" & Chr(10) & "REQD Date]]")
Set My_Range37 = ws4.Range("G2JobList[[#Data],[CONTR" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Door EQPT" & Chr(10) & "Vendor]]")
Set My_Range38 = ws4.Range("G2JobList[[#Data],[Door EQPT" & Chr(10) & "PO]:[Door EQPT" & Chr(10) & "REQD Date]]")
Set My_Range39 = ws4.Range("G2JobList[[#Data],[Door EQPT" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Wiring" & Chr(10) & "Vendor]]")
Set My_Range40 = ws4.Range("G2JobList[[#Data],[Wiring" & Chr(10) & "PO]:[Wiring" & Chr(10) & "REQD Date]]")
Set My_Range41 = ws4.Range("G2JobList[[#Data],[Wiring" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Job Status" & Chr(10) & "Last Update]]")

tbl.Resize rng1

My_Range.Copy
ws1.Range("G2JobList[[#Data],[Job Name]]").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
ws1.Range("G2JobList[[#Data],[Job]:[Payment" & Chr(10) & "with" & Chr(10) & "Approval]]").Value2 = My_Range1.Value2
ws1.Range("G2JobList[[#Data],[SITE Address]:[G1" & Chr(10) & "APPD Date]]").Value2 = My_Range2.Value2
ws1.Range("G2JobList[[#Data],[G1 RLSD To" & Chr(10) & "ENGRG Date]:[G1 RLSD To" & Chr(10) & "PROD Date]]").Value2 = My_Range3.Value2
ws1.Range("G2JobList[[#Data],[G1 CUST" & Chr(10) & "RQST Date]]").Value2 = My_Range4.Value2
ws1.Range("G2JobList[[#Data],[G1" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Jack" & Chr(10) & "Vendor]]").Value2 = My_Range5.Value2
ws1.Range("G2JobList[[#Data],[Jack" & Chr(10) & "PO]:[Jack" & Chr(10) & "REQD Date]]").Value2 = My_Range6.Value2
ws1.Range("G2JobList[[#Data],[Jack" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Machine" & Chr(10) & "Vendor]]").Value2 = My_Range7.Value2
ws1.Range("G2JobList[[#Data],[Machine" & Chr(10) & "PO]:[Machine" & Chr(10) & "REQD Date]]").Value2 = My_Range8.Value2
ws1.Range("G2JobList[[#Data],[Machine" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Safety" & Chr(10) & "Vendor]]").Value2 = My_Range9.Value2
ws1.Range("G2JobList[[#Data],[Safety" & Chr(10) & "PO]:[Safety" & Chr(10) & "REQD Date]]").Value2 = My_Range10.Value2
ws1.Range("G2JobList[[#Data],[Safety" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Governor" & Chr(10) & "Vendor]]").Value2 = My_Range11.Value2
ws1.Range("G2JobList[[#Data],[Governor" & Chr(10) & "PO]:[Governor" & Chr(10) & "REQD Date]]").Value2 = My_Range12.Value2
ws1.Range("G2JobList[[#Data],[Governor" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Tail Sheave" & Chr(10) & "Vendor]]").Value2 = My_Range13.Value2
ws1.Range("G2JobList[[#Data],[Tail Sheave" & Chr(10) & "PO]:[Tail Sheave" & Chr(10) & "REQD Date]]").Value2 = My_Range14.Value2
ws1.Range("G2JobList[[#Data],[Tail Sheave" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Roller Guides" & Chr(10) & "Vendor]]").Value2 = My_Range15.Value2
ws1.Range("G2JobList[[#Data],[Roller Guides" & Chr(10) & "PO]:[Roller Guides" & Chr(10) & "REQD Date]]").Value2 = My_Range16.Value2
ws1.Range("G2JobList[[#Data],[Roller Guides" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[COMP Chain" & Chr(10) & "Vendor]]").Value2 = My_Range17.Value2
ws1.Range("G2JobList[[#Data],[COMP Chain" & Chr(10) & "PO]:[COMP Chain" & Chr(10) & "REQD Date]]").Value2 = My_Range18.Value2
ws1.Range("G2JobList[[#Data],[COMP Chain" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Ropes" & Chr(10) & "Vendor]]").Value2 = My_Range19.Value2
ws1.Range("G2JobList[[#Data],[Ropes" & Chr(10) & "PO]:[Ropes" & Chr(10) & "REQD Date]]").Value2 = My_Range20.Value2
ws1.Range("G2JobList[[#Data],[Ropes" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Oil Buffers" & Chr(10) & "Vendor]]").Value2 = My_Range21.Value2
ws1.Range("G2JobList[[#Data],[Oil Buffers" & Chr(10) & "PO]:[Oil Buffers" & Chr(10) & "REQD Date]]").Value2 = My_Range22.Value2
ws1.Range("G2JobList[[#Data],[Oil Buffers" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Rails" & Chr(10) & "Vendor]]").Value2 = My_Range23.Value2
ws1.Range("G2JobList[[#Data],[Rails" & Chr(10) & "PO]:[Rails" & Chr(10) & "REQD Date]]").Value2 = My_Range24.Value2
ws1.Range("G2JobList[[#Data],[Rails" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[CWT" & Chr(10) & "Vendor]]").Value2 = My_Range25.Value2
ws1.Range("G2JobList[[#Data],[CWT" & Chr(10) & "PO]:[CWT" & Chr(10) & "REQD Date]]").Value2 = My_Range26.Value2
ws1.Range("G2JobList[[#Data],[CWT" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Sling-PLATF" & Chr(10) & "Parts" & Chr(10) & "Vendor]]").Value2 = My_Range27.Value2
ws1.Range("G2JobList[[#Data],[Sling-PLATF" & Chr(10) & "Parts" & Chr(10) & "PO]:[Sling-PLATF" & Chr(10) & "Parts" & Chr(10) & "REQD Date]]").Value2 = My_Range28.Value2
ws1.Range("G2JobList[[#Data],[Sling-PLATF" & Chr(10) & "Parts" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Cab" & Chr(10) & "Vendor]]").Value2 = My_Range29.Value2
ws1.Range("G2JobList[[#Data],[Cab" & Chr(10) & "PO]:[Cab" & Chr(10) & "REQD Date]]").Value2 = My_Range30.Value2
ws1.Range("G2JobList[[#Data],[Cab" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[ENT" & Chr(10) & "Vendor]]").Value2 = My_Range31.Value2
ws1.Range("G2JobList[[#Data],[ENT" & Chr(10) & "PO]:[ENT" & Chr(10) & "REQD Date]]").Value2 = My_Range32.Value2
ws1.Range("G2JobList[[#Data],[ENT" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[FXTR" & Chr(10) & "Vendor]]").Value2 = My_Range33.Value2
ws1.Range("G2JobList[[#Data],[FXTR" & Chr(10) & "PO]:[FXTR" & Chr(10) & "REQD Date]]").Value2 = My_Range34.Value2
ws1.Range("G2JobList[[#Data],[FXTR" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[CONTR" & Chr(10) & "Vendor]]").Value2 = My_Range35.Value2
ws1.Range("G2JobList[[#Data],[CONTR" & Chr(10) & "PO]:[CONTR" & Chr(10) & "REQD Date]]").Value2 = My_Range36.Value2
ws1.Range("G2JobList[[#Data],[CONTR" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Door EQPT" & Chr(10) & "Vendor]]").Value2 = My_Range37.Value2
ws1.Range("G2JobList[[#Data],[Door EQPT" & Chr(10) & "PO]:[Door EQPT" & Chr(10) & "REQD Date]]").Value2 = My_Range38.Value2
ws1.Range("G2JobList[[#Data],[Door EQPT" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Wiring" & Chr(10) & "Vendor]]").Value2 = My_Range39.Value2
ws1.Range("G2JobList[[#Data],[Wiring" & Chr(10) & "PO]:[Wiring" & Chr(10) & "REQD Date]]").Value2 = My_Range40.Value2
ws1.Range("G2JobList[[#Data],[Wiring" & Chr(10) & "SHPG ARR/" & Chr(10) & "Item LCTN]:[Job Status" & Chr(10) & "Last Update]]").Value2 = My_Range41.Value2

My_Range43.Clear
My_Range43.Value = My_Range42.Value
My_Range45.Clear
My_Range45.Formula = My_Range44.Formula

ws3.Activate
ws3.Range("A2").Select
ActiveWindow.ScrollRow = ActiveCell.Row

ws1.Activate
ws1.Range("A3").Select
ActiveWindow.ScrollRow = ActiveCell.Row

wb2.Close SaveChanges:=False 'Un-Remark this line of code after all testing is complete, SPS, 02/07/24

With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .CutCopyMode = True
    .DisplayAlerts = True
    .Calculation = xlCalculationAutomatic
End With

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Tweak Macro that Imports specific table columns from a different workbook to filter a column first
Replies
4
Views
447
sspatriots
S
S
  • Solved
Trying to copy another column from one table to another.
Replies
2
Views
577
Alex Blakenburg
A
S
  • Solved
Copying Filtered Columns (non-adjacent) from one Table to end of another, but need to also add information in adjacent column of Destination Table
Replies
1
Views
576
sspatriots
S
S
  • Solved
Need value to be the Table Column Header in lieu of Cell Address
Replies
1
Views
465
sspatriots
S
S
  • Question Question
Trying to eliminate having to activate the destination worksheet (ws2) in my macro
2
Replies
10
Views
628
sspatriots
S

Forum statistics

Threads
1,223,881
Messages
6,175,161
Members
452,615
Latest member
bogeys2birdies

Share this page

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