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

 

[nemmi69]

what do you mean by linking? You can set up a reference to both

 

[lzunke]

what do you mean by linking? You can set up a reference to both

I am trying to use Activecell.formula function to link the cells , but it is giving an error. I will upload worksheet and the code I have written

 

[nemmi69]

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"
End Sub

 

[lzunke]

Thank You,
I have attached a sample sheet
There is a main sheet "consolidate" and other data sheet (data 1 to data 4).

I want to consolidate the data from all the four sheets one below the other .i.e data from data 1 sheet is linked to consolidate sheet, then data from data 2 sheet linked to consolidate sheet below the linked data 1

sample.xlsx
	H
13
Data 4

 

[lzunke]

So basically, not a paste special value but the linked value from each of the data sheet to consolidate sheet one below the other where data from one sheet ends and data from other sheet starts

 

[nemmi69]

So is it a case of all "A1"s linked etc?

 

[lzunke]

So is it a case of all "A1"s linked etc?

Yes, if you see the output sheet in the attached workbook, it shows how I need the final data ram through via code

 

[nemmi69]

Do not seem to be able to get the book

 

[HaHoBe]

Hi lzunke,

how about

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

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

Set wsDest = ThisWorkbook.Sheets(1)
wsDest.Cells.ClearContents

lngStart = 2
For Each ws In Worksheets
  If ws.Name <> wsDest.Name Then
    ws.UsedRange.Copy
    wsDest.Range("A" & lngStart).Select
    wsDest.Paste Link:=True
    lngStart = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row + 1
  End If
Next ws

Set wsDest = Nothing
End Sub

Ciao,
Holger