Copy Range from Multiple Sheets and Paste Link into a Single Sheet

[vbascientist]

Hello,

I am new to VBA. I have the following code that works great except that I need it to paste the range as a link to the original range. Help!!!

Sub CopyColumns()

Dim Source As Worksheet
Dim Destination As Worksheet
Dim Last As Long

Application.ScreenUpdating = False

'Checking whether "Master" sheet already exists in the workbook
For Each Source In ThisWorkbook.Worksheets
If Source.Name = "Master" Then
MsgBox "Master sheet already exist"

Exit Sub
End If
Next

'Inserting new worksheets in the workbook
Set Destination = Worksheets.Add(after:=Worksheets("Main"))

'Renaming the worksheet
Destination.Name = "Master"

'Looping through the worksheets in the workbook
For Each Source In ThisWorkbook.Worksheets
If Source.Name <> "Master" And Source.Name <> "Main" Then

'Finding the last column from the destination sheet
Last = Destination.Range("A1").SpecialCells(xlCellTypeLastCell).Column

If Last = 1 Then

'Pasting the data in the destination sheet
Source.Range("N:O").Copy Destination.Columns(Last)

Else

Source.Range("N:O").Copy Destination.Columns(Last + 1)

End If

End If

Next

Columns.AutoFit
Application.ScreenUpdating = True

End Sub

 

[Skyybot]

If you want to Hyperlink to a Cell or Range, then this should help you. This would go into the ThisWorkbook code module of your VBA Project. PLease make sure to test on a copy of your Workbook.

Option Explicit

Sub MakeHyperlink()
Dim sht1 As Worksheet, sht2 As Worksheet, rng1 As Range, rng2 As Range, hl As Hyperlink
Set sht1 = Me.Sheets(1)  'Source Worksheet. Can use "SheetName" instead of number
Set sht2 = Me.Sheets(2)  'Paste Worksheet. Can use "SheetName" instead of number
Set rng1 = sht1.Range("A1:B4") 'Set your "Copy" Range accordingly
Set rng2 = sht2.Range("A1:C4") 'Set your "Paste" Range accordinly
Set hl = sht2.Hyperlinks.Add(Anchor:=rng2, Address:=Me.Path & "\" & Me.Name, SubAddress:=sht1.Name & "!" & rng1.Address, TextToDisplay:="Click Here")
End Sub

 

[vbascientist]

Thank you so much for the reply. I have over 150 sheets, is there a way to keep my code the same where I don't have to reference any sheets specifically but still paste-link? I am copying the same columns "N:O" on every sheet.