VBA code to link from one sheet

[lzunke]

Hello,

I have two excel sheets in same workbook. I want to link data from one worksheet to another , but it is giving an error. I want to link the data which is spread into multiple rows and colums. Pasting was easy , but linking , I am not able to understand which code to use

 

[lzunke]

Do not seem to be able to get the book

Do not seem to be able to get the book

sample.xlsx
	H
13
Data 4

 

[nemmi69]

Discovered that this machine is not allowed the mini sheet add-in so I will give an example

Sub LinkSheets()
Dim WbName1 As Workbook
Dim WsName1 As Worksheet, WsName2 As Worksheet

Set WbName1 = ThisWorkbook
Windows(ThisWorkbook.Name).Activate
Set WsName1 = WbName.Sheets(1) ' Or replace with "SheetName1"
Set WsName2 = WbName.Sheets(2) ' Or replace with "SheetName2"


WsName1.Range("A2").Value = WsName2.Range("A2").Value
WsName1.Range("A5:A22").Value = WsName2.Range("A5:A22").Value
WsName1.Range("E7:Z7").Value = WsName2.Range("E7:Z7").Value

End Sub

 

[lzunke]

Discovered that this machine is not allowed the mini sheet add-in so I will give an example

Sub LinkSheets()
Dim WbName1 As Workbook
Dim WsName1 As Worksheet, WsName2 As Worksheet

Set WbName1 = ThisWorkbook
Windows(ThisWorkbook.Name).Activate
Set WsName1 = WbName.Sheets(1) ' Or replace with "SheetName1"
Set WsName2 = WbName.Sheets(2) ' Or replace with "SheetName2"


WsName1.Range("A2").Value = WsName2.Range("A2").Value
WsName1.Range("A5:A22").Value = WsName2.Range("A5:A22").Value
WsName1.Range("E7:Z7").Value = WsName2.Range("E7:Z7").Value

End Sub

Thank you, but this will paste special value. I needed a equal to link, so that in the consolidate sheet I could see from which sheet the value is coming from

 

[nemmi69]

No, it makes the 1st cell equal the value in the 2nd. There is no copy and paste

 

[HaHoBe]

Hi lzunke,

both posts with attachments just deliver an empty cell for me in my Excel.

The result in Sheets Collect looks like this:

Mappe1
	A	B	C	D	E	F	G	H
1	Hyperlink to sheet	Start of Data
2	Data1	1	2	3	4	5	6	7
3		2	4	6	8	10	12	14
4		3	Start	here	0	15	0	21
5		4	8	12	16	20	24	28
6		5	10	15	20	25	30	35
7	Data2	1	2	3	4	5
8		2	4	6	8	10
9		3	6	9	12	15
10		4	8	Test for Text	0	20
11		5	10	15	0	25
Collect

Cell Formulas
Range	Formula
B2:H6	B2	=Data1!A1
B7:F11	B7	=Data2!A1

Code used:

Sub lzunke2_Hyperlinks()
'https://www.mrexcel.com/board/threads/vba-code-to-link-from-one-sheet.1213006/
'2022-08-10

Dim wsDest        As Worksheet
Dim ws            As Worksheet
Dim lngStart      As Long

Set wsDest = ThisWorkbook.Sheets(1)
wsDest.Cells.Delete               'Clear all data in Collection sheet

lngStart = 2
wsDest.Range("A1").Value = "Hyperlink to sheet"
wsDest.Range("B1").Value = "Start of Data"

For Each ws In Worksheets
  If ws.Name <> wsDest.Name Then
    ws.UsedRange.Copy
    Application.Goto wsDest.Range("B" & lngStart)
    wsDest.Paste Link:=True
    wsDest.Hyperlinks.Add Anchor:=wsDest.Range("A" & lngStart), _
        Address:="", SubAddress:=ws.Name & "!A1", TextToDisplay:=ws.Name
    lngStart = wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Row + 1
  End If
Next ws

Set wsDest = Nothing

End Sub

Ciao,
Holger