Copy the same cell from multiple sheets
Solved
dyueo
Posted messages
5
Registration date
Status
Membre
-
Groquick Posted messages 17 Status Membre -
Groquick Posted messages 17 Status Membre -
Hello,
Here is a response that I cannot find on the various forums I visited.
I have 98 tabs (97 + 1 summary tab)
I want the summary sheet to display the 97 R6 cells (located on the 97 tabs)
It doesn't matter how these 97 R6 cells are displayed (in the same row or in the same column)
I cannot do a copy/paste 97 times manually (especially since I still have 6 more files to process with the same issue)
Creating a simple formula like A1=Sheet2!R6, A2=Sheet3!R6 is impossible since the tab names are very different.
Thank you
Here is a response that I cannot find on the various forums I visited.
I have 98 tabs (97 + 1 summary tab)
I want the summary sheet to display the 97 R6 cells (located on the 97 tabs)
It doesn't matter how these 97 R6 cells are displayed (in the same row or in the same column)
I cannot do a copy/paste 97 times manually (especially since I still have 6 more files to process with the same issue)
Creating a simple formula like A1=Sheet2!R6, A2=Sheet3!R6 is impossible since the tab names are very different.
Thank you
Configuration: Windows XP Internet Explorer 7.0
6 réponses
Hello,
A little sub that could do that for you...
See you later
--
Experience teaches more certainly than advice. (André Gide)
If you bump into a pot and it sounds hollow, it’s not necessarily the pot that’s empty. ;-)(Confucius)
A little sub that could do that for you...
Sub RappelR6() Dim Lig As Long, Col As Integer Dim Wk As Worksheet Lig = 2 'First row to copy Col = 5 'Column to copy For Each Wk In Worksheets If Wk.Name <> "recap" Then Sheets("Recap").Cells(Lig, Col) = Wk.Range("R6") Lig = Lig + 1 End If Next Wk End Sub See you later
--
Experience teaches more certainly than advice. (André Gide)
If you bump into a pot and it sounds hollow, it’s not necessarily the pot that’s empty. ;-)(Confucius)
Hello.
The formula is indeed very simple: A1=SUM(Sheet1:Sheet97!R6)
I still have some doubts, because I didn't understand what you meant by " No matter how these 97 cells R6 are displayed (in the same row or in the same column) "!
The formula only works if in each of the 98 tabs the value of cell R26 is the one being added...
--
Retirement is nice! Especially in the West Indies... :-)
☻ Raymond ♂
The formula is indeed very simple: A1=SUM(Sheet1:Sheet97!R6)
I still have some doubts, because I didn't understand what you meant by " No matter how these 97 cells R6 are displayed (in the same row or in the same column) "!
The formula only works if in each of the 98 tabs the value of cell R26 is the one being added...
--
Retirement is nice! Especially in the West Indies... :-)
☻ Raymond ♂
Thank you for the response, but it doesn't match what I'm looking for. Let me explain (better) ;-)
In fact, this cell contains text.
It is filled with various names (across 197 tabs).
I want to display the 197 names on a summary sheet (hence the "on the same row or in the same column").
Not wanting to do 197 copy/pastes, I'm asking if there is a formula, a macro... that can copy the R6 cell from the 197 tabs 197 times and display it all on a single page.
In fact, this cell contains text.
It is filled with various names (across 197 tabs).
I want to display the 197 names on a summary sheet (hence the "on the same row or in the same column").
Not wanting to do 197 copy/pastes, I'm asking if there is a formula, a macro... that can copy the R6 cell from the 197 tabs 197 times and display it all on a single page.
Ah! I didn't understand anything...
There might be a solution using the OFFSET function; but what you really need is a macro, and unfortunately, that’s beyond my skills...
Other forum members will be happy to help you, don’t worry!
--
Retirement is great! Especially in the Caribbean... :-)
☻ Raymond ♂
There might be a solution using the OFFSET function; but what you really need is a macro, and unfortunately, that’s beyond my skills...
Other forum members will be happy to help you, don’t worry!
--
Retirement is great! Especially in the Caribbean... :-)
☻ Raymond ♂
Hello,
Introduction
First, let me introduce myself, as a Technical Salesperson, I'm more comfortable with the phone and mechanical topics than with macros, unless they are at a barbecue accompanied by Vino Verde (for those of us who are more Portuguese).
So: to make a macro, where I start recording what I'm doing to repeat it later: Check.
But then making a macro where there is pure programming... I don't master it yet... let's be honest: not at all!
I've tried to find a forum where someone had exactly my problem... but I couldn't find it. I tried fiddling with the different answers, but between loops, All Worksheets and the syntax... it’s a bit (a lot, (passionately), madly) hard and not very intuitive. I tried looking at what the online macro courses offered, but I would have to go through an entire curriculum, certainly very interesting, but I will take 3 weeks to gather all the skills to create my macro! And this morning, I came across this message. It's almost exactly what I want... but... but not quite...
So let me present you my:
Problematic:
I have a workbook with X tabs:
the first Y (=10) are not of interest to me here,
The different Z tabs that follow are each dedicated to one of my clients. Of course, each tab is named after the corresponding client. They are not called Z1, then Z2, then Z3, etc.
On tab A -named: "To Do Clients" and directly following the client tabs (Z)- I would like to compile the content of certain cells from the different Z tabs (see "objective" section below)
Tab B -named: "To Do Prospects" and directly following tab A- is where I compile the information of my prospects. The macro was created using the "recorder" function of Excel macros.
Note that neither A nor B returns results for the cells when we seek the values from all the tabs.
Objective:
In tab A, starting from cell A2, I would like the macro to copy the results of 3 cells from each of the Z tabs.
A2 for the first value of the 1st client, B2 for the second value of the 1st client, and C2 for the third value of the 1st client.
Then A3 A2 for the first value of the 2nd client, B3 etc.
To simplify the macro and start from Lermite222's, I copied for all the tabs.
Then, I delete rows 2 to 11 to remove the information returned by the first Y tabs.
The cells to return are respectively: I3, B1, J3.
Limits of my skills:
First, I don't know anything about it and that's my biggest problem.
Then, the code from lermite222 is what I've found closest.
So I've added around it:
"""""""""""""""
Sub RappelR6()
Dim Lig As Long, Col As Integer
Dim Wk As Worksheet
Lig = 2 'First row to copy
Col = 1 'Column to copy
For Each Wk In Worksheets
If Wk.Name <> "To Do Clients" Then
Sheets("To Do Clients").Cells(Lig, Col) = Wk.Range("I3")
Lig = Lig + 1
End If
Next Wk
Rows("2:11").Select
Range("A11").Activate
Selection.ClearContents
Selection.Delete Shift:=xlUp
Range("A2:C2001").Select
ActiveWorkbook.Worksheets("To Do Clients").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("To Do Clients").Sort.SortFields.Add Key:=Range( _
"A2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("To Do Clients").Sort
.SetRange Range("A3:A61")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
"""""""""""""""
and my goodness we are not bad... but I would need to make 3 macros for I3, B1 and J3. But then, I would lose my sorting by date...
So, like a reality TV candidate who thinks they have reached the stardom because they were featured on the cover of a magazine, I tried:
""""""""""""""
Sub RappelR6()
Dim Lig As Long, Col As Integer
Dim Wk As Worksheet
Lig = 2 'First row to copy
Col = 1 'Column to copy
For Each Wk In Worksheets
If Wk.Name <> "To Do Clients" Then
Sheets("To Do Clients").Cells(Lig, Col) = Wk.Range("I3")
Lig = Lig + 1
End If
Next Wk
Dim Lig As Long, Col As Integer
(...)
Lig = 2 'First row to copy
Col = 2 'Column to copy
(...)
Sheets("To Do Clients").Cells(Lig, Col) = Wk.Range("B1")
Lig = Lig + 1
End If
Next Wk
Dim Lig As Long, Col As Integer
(...)
Lig = 2 'First row to copy
Col = 3 'Column to copy (...)
Sheets("To Do Clients").Cells(Lig, Col) = Wk.Range("J3")
Lig = Lig + 1
End If
Next Wk
Rows("2:11").Select
(...) xlSortNormal
With ActiveWorkbook.Worksheets("To Do Clients").Sort
(...)
End With
End Sub
""""""""""
And of course, that didn't work: "compilation error: Existing declaration in current scope".
So I deleted: "Dim Lig As Long, Col As Integer"
same error message. Then I tried deleting:
"Dim Wk As Worksheet"
same error message.
And I said to myself:
You don't know where Sub Rappel starts and ends and how it works!
that changing the number of "Col =" might not be enough to change the column...
...
...
...
Moment of solitude...
Finally, here it is, I think I know how to tinker, but now, I need a mechanic.
Is my request clear? Could you help me finalize this macro? Thank you for your help.
Willywill
Introduction
First, let me introduce myself, as a Technical Salesperson, I'm more comfortable with the phone and mechanical topics than with macros, unless they are at a barbecue accompanied by Vino Verde (for those of us who are more Portuguese).
So: to make a macro, where I start recording what I'm doing to repeat it later: Check.
But then making a macro where there is pure programming... I don't master it yet... let's be honest: not at all!
I've tried to find a forum where someone had exactly my problem... but I couldn't find it. I tried fiddling with the different answers, but between loops, All Worksheets and the syntax... it’s a bit (a lot, (passionately), madly) hard and not very intuitive. I tried looking at what the online macro courses offered, but I would have to go through an entire curriculum, certainly very interesting, but I will take 3 weeks to gather all the skills to create my macro! And this morning, I came across this message. It's almost exactly what I want... but... but not quite...
So let me present you my:
Problematic:
I have a workbook with X tabs:
the first Y (=10) are not of interest to me here,
The different Z tabs that follow are each dedicated to one of my clients. Of course, each tab is named after the corresponding client. They are not called Z1, then Z2, then Z3, etc.
On tab A -named: "To Do Clients" and directly following the client tabs (Z)- I would like to compile the content of certain cells from the different Z tabs (see "objective" section below)
Tab B -named: "To Do Prospects" and directly following tab A- is where I compile the information of my prospects. The macro was created using the "recorder" function of Excel macros.
Note that neither A nor B returns results for the cells when we seek the values from all the tabs.
Objective:
In tab A, starting from cell A2, I would like the macro to copy the results of 3 cells from each of the Z tabs.
A2 for the first value of the 1st client, B2 for the second value of the 1st client, and C2 for the third value of the 1st client.
Then A3 A2 for the first value of the 2nd client, B3 etc.
To simplify the macro and start from Lermite222's, I copied for all the tabs.
Then, I delete rows 2 to 11 to remove the information returned by the first Y tabs.
The cells to return are respectively: I3, B1, J3.
Limits of my skills:
First, I don't know anything about it and that's my biggest problem.
Then, the code from lermite222 is what I've found closest.
So I've added around it:
"""""""""""""""
Sub RappelR6()
Dim Lig As Long, Col As Integer
Dim Wk As Worksheet
Lig = 2 'First row to copy
Col = 1 'Column to copy
For Each Wk In Worksheets
If Wk.Name <> "To Do Clients" Then
Sheets("To Do Clients").Cells(Lig, Col) = Wk.Range("I3")
Lig = Lig + 1
End If
Next Wk
Rows("2:11").Select
Range("A11").Activate
Selection.ClearContents
Selection.Delete Shift:=xlUp
Range("A2:C2001").Select
ActiveWorkbook.Worksheets("To Do Clients").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("To Do Clients").Sort.SortFields.Add Key:=Range( _
"A2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("To Do Clients").Sort
.SetRange Range("A3:A61")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
"""""""""""""""
and my goodness we are not bad... but I would need to make 3 macros for I3, B1 and J3. But then, I would lose my sorting by date...
So, like a reality TV candidate who thinks they have reached the stardom because they were featured on the cover of a magazine, I tried:
""""""""""""""
Sub RappelR6()
Dim Lig As Long, Col As Integer
Dim Wk As Worksheet
Lig = 2 'First row to copy
Col = 1 'Column to copy
For Each Wk In Worksheets
If Wk.Name <> "To Do Clients" Then
Sheets("To Do Clients").Cells(Lig, Col) = Wk.Range("I3")
Lig = Lig + 1
End If
Next Wk
Dim Lig As Long, Col As Integer
(...)
Lig = 2 'First row to copy
Col = 2 'Column to copy
(...)
Sheets("To Do Clients").Cells(Lig, Col) = Wk.Range("B1")
Lig = Lig + 1
End If
Next Wk
Dim Lig As Long, Col As Integer
(...)
Lig = 2 'First row to copy
Col = 3 'Column to copy (...)
Sheets("To Do Clients").Cells(Lig, Col) = Wk.Range("J3")
Lig = Lig + 1
End If
Next Wk
Rows("2:11").Select
(...) xlSortNormal
With ActiveWorkbook.Worksheets("To Do Clients").Sort
(...)
End With
End Sub
""""""""""
And of course, that didn't work: "compilation error: Existing declaration in current scope".
So I deleted: "Dim Lig As Long, Col As Integer"
same error message. Then I tried deleting:
"Dim Wk As Worksheet"
same error message.
And I said to myself:
You don't know where Sub Rappel starts and ends and how it works!
that changing the number of "Col =" might not be enough to change the column...
...
...
...
Moment of solitude...
Finally, here it is, I think I know how to tinker, but now, I need a mechanic.
Is my request clear? Could you help me finalize this macro? Thank you for your help.
Willywill
I found it!!!
Once the first extract was done, I went to the adjacent cell (Range("B2").Select, then Range("B3").Select)
Looking back, I wonder if those lines are necessary,
above I pasted all the text from what seemed to be the macro that interested me, (from "Dim Lig As Long," to "Next Wk"
I removed the dimensioning at the beginning of my macro. Indeed, I received some compilation error messages from the debugger that said: "Existing declaration in the current scope". Not knowing what I was doing, I first deleted:
Dim Lig As Long, Col As Integer
and since it still bugged, I deleted:
Dim Wk As Worksheet
I modified "Col = 1 'Column to copy to" by changing it to 2 for B2 and 3 for C2 (that's why I think going to B2 and C2 before the macro is unnecessary)
And here it is, my first macro done in programming... shall I say: "the first digital macro". Because given how I struggled, I think my brain is not made up of just 0s and 1s... I even believe that there are several of us in my head using it! And it's not always me who takes the lead!
So my final macro:
"""""""""""""""""""""""
Sub RappelR6()
Dim Lig As Long, Col As Integer
Dim Wk As Worksheet
Lig = 2 'First line to copy to
Col = 1 'Column to copy to
For Each Wk In Worksheets
If Wk.Name <> "To Do Clients" Then
Sheets("To Do Clients").Cells(Lig, Col) = Wk.Range("I3")
Lig = Lig + 1
End If
Next Wk
Range("B2").Select
Lig = 2 'First line to copy to
Col = 2 'Column to copy to
For Each Wk In Worksheets
If Wk.Name <> "To Do Clients" Then
Sheets("To Do Clients").Cells(Lig, Col) = Wk.Range("B1")
Lig = Lig + 1
End If
Next Wk
Range("B3").Select
Lig = 2 'First line to copy to
Col = 3 'Column to copy to
For Each Wk In Worksheets
If Wk.Name <> "To Do Clients" Then
Sheets("To Do Clients").Cells(Lig, Col) = Wk.Range("J3")
Lig = Lig + 1
End If
Next Wk
Rows("2:11").Select
Range("A11").Activate
Selection.ClearContents
Selection.Delete Shift:=xlUp
Range("A2:C2001").Select
ActiveWorkbook.Worksheets("To Do Clients").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("To Do Clients").Sort.SortFields.Add Key:=Range( _
"A2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("To Do Clients").Sort
.SetRange Range("A3:A61")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Once the first extract was done, I went to the adjacent cell (Range("B2").Select, then Range("B3").Select)
Looking back, I wonder if those lines are necessary,
above I pasted all the text from what seemed to be the macro that interested me, (from "Dim Lig As Long," to "Next Wk"
I removed the dimensioning at the beginning of my macro. Indeed, I received some compilation error messages from the debugger that said: "Existing declaration in the current scope". Not knowing what I was doing, I first deleted:
Dim Lig As Long, Col As Integer
and since it still bugged, I deleted:
Dim Wk As Worksheet
I modified "Col = 1 'Column to copy to" by changing it to 2 for B2 and 3 for C2 (that's why I think going to B2 and C2 before the macro is unnecessary)
And here it is, my first macro done in programming... shall I say: "the first digital macro". Because given how I struggled, I think my brain is not made up of just 0s and 1s... I even believe that there are several of us in my head using it! And it's not always me who takes the lead!
So my final macro:
"""""""""""""""""""""""
Sub RappelR6()
Dim Lig As Long, Col As Integer
Dim Wk As Worksheet
Lig = 2 'First line to copy to
Col = 1 'Column to copy to
For Each Wk In Worksheets
If Wk.Name <> "To Do Clients" Then
Sheets("To Do Clients").Cells(Lig, Col) = Wk.Range("I3")
Lig = Lig + 1
End If
Next Wk
Range("B2").Select
Lig = 2 'First line to copy to
Col = 2 'Column to copy to
For Each Wk In Worksheets
If Wk.Name <> "To Do Clients" Then
Sheets("To Do Clients").Cells(Lig, Col) = Wk.Range("B1")
Lig = Lig + 1
End If
Next Wk
Range("B3").Select
Lig = 2 'First line to copy to
Col = 3 'Column to copy to
For Each Wk In Worksheets
If Wk.Name <> "To Do Clients" Then
Sheets("To Do Clients").Cells(Lig, Col) = Wk.Range("J3")
Lig = Lig + 1
End If
Next Wk
Rows("2:11").Select
Range("A11").Activate
Selection.ClearContents
Selection.Delete Shift:=xlUp
Range("A2:C2001").Select
ActiveWorkbook.Worksheets("To Do Clients").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("To Do Clients").Sort.SortFields.Add Key:=Range( _
"A2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("To Do Clients").Sort
.SetRange Range("A3:A61")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub