copy link from multiple sheets

[koi]

Hi All,

sorry for this newbie question but i hope someone can help me to solve it,

i have link in A1 like ='Sheet1'!$G$5, what is the fastest way to copy that link in A2 for ='Sheet2'!$G$5, and in A3 = ='Sheet3'!$G$5 ?

i dont mind using vba as well if normal formula wont work,

Thanks

 

[Chihiro]

You could use... INDIRECT, but that would be volatile and may hit performance depending on how many you have, and how much downstream calculation you have.

Ex:
=INDIRECT("'Sheet" & Rows($A$1:A1) & "'!G5")

To give you VBA solution, I'd need more info. Such as which sheet holds the formula and should be excluded from loop etc.

 

[koi]

Hi Chihiro,

I also think about Indirect but then i have 100 sheets so I'm thinking the file could be heavy,

basically I have "Summary" Sheet, where I need to make the link from A1:C1 refer to data1 sheet, and A2:C2 refer to data2 sheet till data100

example below :
Summary!A1 = data1!C1
Summary!B1 = data1!D1
Summary!C1 = data1!E1

Summary!A2 = data2!C1
Summary!B2 = data2!D1
Summary!C2 = data2!E1

Summary!A3 = data3!C1
Summary!B3 = data3!D1
Summary!C3 = data3!E1

i'm also thinking now that if it not possible to make vba link to sheet, then i will try to find vba copy paste as value...i think vba copy is easier?

Thanks Chihiro

 

[Chihiro]

Hmm, I'd seriously consider restructuring your workbook... but likely that's not an option at this point, I take it?

There are several approaches to this. And will depend heavily on your Excel version. What version are you using?

1. Use PowerQuery/Get & Transform to return data to Summary sheet.

2. Use data relationship and structured table. Then use pivot table to return data

3. VBA.

Ex: Assuming Worksheets are ordered left to right ascending.

Sub Demo()
Dim ws As Worksheet
i = 1
For Each ws In ThisWorkbook.Worksheets
    If Not ws.Name = "Summary" Then
        With Sheets("Summary")
            .Range("A" & i & ":C" & i).Value = ws.Range("C1:E1").Value
        End With
        i = i + 1
    End If
Next
End Sub

 

[koi]

Thanks Chihiro,

it works well for copy and paste as value, thumbs up !

another question, what if i have another sheet name "something" but i dont want to include ...i only need from data1, data2, ...data100

 

[Chihiro]

Assuming only data1... data100 start with 'data' in sheet name.
Something like below.

Sub Demo()
Dim ws As Worksheet
i = 1
For Each ws In ThisWorkbook.Worksheets
    If ws.Name Like "data*" Then
        With Sheets("Summary")
            .Range("A" & i & ":C" & i).Value = ws.Range("C1:E1").Value
        End With
        i = i + 1
    End If
Next
End Sub

 

[koi]

perfect, Thanks Chihiro

 

[koi]

Hi Chihiro,

I've done some modification by adding helper range on data sheet, so the code only 1 lines like your original one, it works perfectly no matter how many times i run the macro...the problem is the macro start on A4 line instead of A3 lines, can you give advice on how to make it always start on A3?

Thanks Chihiro

>>> use code - tags <<<

Sub Demo()
Dim ws As Worksheet
i = 3
For Each ws In ThisWorkbook.Worksheets
If ws.Name Like "data*" Then
With Sheets("Summary")

.Range("A" & i & ":I" & i).Value = ws.Range("S1:AA1").Value

End With
i = i + 1
End If
Next
End Sub

 

[vletm]

koi
A4 -> A3
Modify Your i = 3 to i = 2

 

[koi]

Hi All,

nevermind the code works perfectly, only i have another sheet name "data raw" so the code also copied the data from that sheet

Thanks All