Excel actively copy data from other sheets

[hmartine]

When I want to actively SUM data between different spreadsheets I simply select =SUM('Sheet-Start:Sheet-End'!B2), then, If I create new sheets I do it between Sheet-Start-and-Sheet-End, so the new sheets are also including in the SUM.
However, what I want to do now is, in a new sheet, COPY into a column the value from cell B2 from Sheets between Sheet-Star and Sheet-End. While I can do this "manually" by copying each sheet/cell in a new cell in the column, I would like to have something that actively updates when new sheets are created, just like the SUM in the above example. That is, if I have the Following Sheets:Sheet-Star, Sheet1, Sheet2, Sheet3, Sheet4, Sheet5, Sheet-End, Sheet-Col. In Sheet-Col I want a column in which each individual value of B2 from Sheets 1-5 is copied (To the column), and then if I add a Sheet6, betweenSheeet-Star and Sheet-End, the new B2 from Sheet6 is automatically added to the end of the column in Sheet-Col. Is this possible? If so, how? It is worth to say that it does not have to "automatically" update the column in Sheet-Col, it could be a Macro and I assign a shortcut that refresh/updates the column.
Thanks very much for the help.

 

[Sgdva]

This can help
You may switch from sub to function if you need the result for later calculations or use it as sub and type the result directly
Consider that if there are hidden sheets it will evaluate them too if they are among the range of the sheets you state:
The sub ToCall is where we evoke the real evaluation, from your sub just call the SumB2ValuesBetweenSheets with the arguments as you need it
Sheet-Star and Sheet-End. Would be SheetOne and SheetTwo argument (Macro will determine which one is the start and which one is the end based on their position.
The third argument would be, from the sheet is being called, where it's going to paste the result.

Sub ToCall()
Call SumB2ValuesBetweenSheets("Sheet1", "Sheet5", Range("D2"))
End Sub


Sub SumB2ValuesBetweenSheets(SheetOne As String, SheetTwo As String, RangeResult As Range, Optional WorkBookEvaluated As Workbook)
'unhide all the sheets (by index) in the mid of the sheets stated
Dim TotalSheets As Long
Dim CounterSheets As Long
Dim TotalSum As Long
If WorkBookEvaluated Is Nothing Then Set WorkBookEvaluated = ThisWorkbook
On Error GoTo Err01SumB2Values
If WorkBookEvaluated.Sheets(SheetOne).Index - WorkBookEvaluated.Sheets(SheetTwo).Index = 0 Then MsgBox ("Err02SumB2Values: Either sheet " & SheetOne & " or sheet " & SheetTwo & " don't exist! Or they are the same! "): End
TotalSheets = IIf(WorkBookEvaluated.Sheets(SheetOne).Index > WorkBookEvaluated.Sheets(SheetTwo).Index, WorkBookEvaluated.Sheets(SheetOne).Index, WorkBookEvaluated.Sheets(SheetTwo).Index)
For CounterSheets = IIf(WorkBookEvaluated.Sheets(SheetOne).Index > WorkBookEvaluated.Sheets(SheetTwo).Index, WorkBookEvaluated.Sheets(SheetTwo).Index, WorkBookEvaluated.Sheets(SheetOne).Index) To TotalSheets
On Error GoTo Err03SumB2Values
TotalSum = WorkBookEvaluated.Sheets(CounterSheets).Range("B2").Value + TotalSum
Next CounterSheets
RangeResult.Value = TotalSum
If 1 = 2 Then
Err01SumB2Values:
MsgBox ("Err01SumB2Values: Either sheet " & SheetOne & " or sheet " & SheetTwo & " don't exist!"): End
End
ElseIf 2 = 3 Then
Err03SumB2Values:
MsgBox ("Err03SumB2Values: The B2 Value in sheet " & WorkBookEvaluated.Sheets(CounterSheets).Name & " is not a number or is formatted as text!"): End
End If
End Sub

 

[hmartine]

Sgdva, thanks for the replay. Sum is not what I am trying to do. All I want to do, within the same workbook, is to copy the value from cell B2 in several SELECTED worksheets and paste into column D in another worksheet called "Summary". In addition, I would like to also copy and paste the corresponding worksheet name in Column C. The code I have so far fails, not sure how to fix it, not sure if there is a better way to do it. I am new in VBA. I am sure you will find silly mistakes, please forgive me. The code fails under "Run-time error '5' : Invalid procedure call or argument". Any help is highly appreciated.

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function

Sub CopyRangeFromMultiWorksheets()
Dim sh As Worksheet
Dim wb As Workbook
Dim DestSh As Worksheet

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set wb = ThisWorkbook
Set DestSh = wb.Sheets("Summary")

' Loop through worksheets that start with the name "20"
' This section I tested and it works

For Each sh In ActiveWorkbook.Worksheets
If LCase(Left(sh.Name, 2)) = "20" Then

' Specify the range to copy the data
' This portion has also been tested and it works

sh.Range("B2").Copy

' Paste copied range into "Summary" worksheet in Column D
' This is the part that does not work I get:
' Run-time error '5' : Invalid procedure call or argument

With DestSh.Cells("D2:D")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

' This statement will copy the sheet names in the C column.
' I have not been able to check this part since I am stock in the previous step
DestSh.Cells("C2:C").Resize(CopyRng.Rows.Count).Value = sh.Name

End If

Next

ExitTheSub:

Application.Goto Worksheets("Summary").Cells(1)


With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub</code>

 

[hmartine]

I apologize I did not put the previous replay in CODE mode. Here it is.

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">[COLOR=#00008B]Function[/COLOR][COLOR=#2E3133] LastRow[/COLOR][COLOR=#2E3133]([/COLOR][COLOR=#2E3133]sh [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#2E3133] Worksheet[/COLOR][COLOR=#2E3133])[/COLOR][COLOR=#2E3133]
    [/COLOR][COLOR=#00008B]On[/COLOR][COLOR=#00008B]Error[/COLOR][COLOR=#00008B]Resume[/COLOR][COLOR=#00008B]Next[/COLOR][COLOR=#2E3133]
    LastRow [/COLOR][COLOR=#2E3133]=[/COLOR][COLOR=#2E3133] sh[/COLOR][COLOR=#2E3133].[/COLOR][COLOR=#2E3133]Cells[/COLOR][COLOR=#2E3133].[/COLOR][COLOR=#2E3133]Find[/COLOR][COLOR=#2E3133]([/COLOR][COLOR=#2E3133]What[/COLOR][COLOR=#2E3133]:=[/COLOR][COLOR=#6B291B]"*"[/COLOR][COLOR=#2E3133],[/COLOR][COLOR=#2E3133] _
                            After[/COLOR][COLOR=#2E3133]:=[/COLOR][COLOR=#2E3133]sh[/COLOR][COLOR=#2E3133].[/COLOR][COLOR=#2E3133]Range[/COLOR][COLOR=#2E3133]([/COLOR][COLOR=#6B291B]"A1"[/COLOR][COLOR=#2E3133]),[/COLOR][COLOR=#2E3133] _
                            Lookat[/COLOR][COLOR=#2E3133]:=[/COLOR][COLOR=#2E3133]xlPart[/COLOR][COLOR=#2E3133],[/COLOR][COLOR=#2E3133] _
                            LookIn[/COLOR][COLOR=#2E3133]:=[/COLOR][COLOR=#2E3133]xlFormulas[/COLOR][COLOR=#2E3133],[/COLOR][COLOR=#2E3133] _
                            SearchOrder[/COLOR][COLOR=#2E3133]:=[/COLOR][COLOR=#2E3133]xlByRows[/COLOR][COLOR=#2E3133],[/COLOR][COLOR=#2E3133] _
                            SearchDirection[/COLOR][COLOR=#2E3133]:=[/COLOR][COLOR=#2E3133]xlPrevious[/COLOR][COLOR=#2E3133],[/COLOR][COLOR=#2E3133] _
                            MatchCase[/COLOR][COLOR=#2E3133]:=[/COLOR][COLOR=#6B291B]False[/COLOR][COLOR=#2E3133]).[/COLOR][COLOR=#2E3133]Row
    [/COLOR][COLOR=#00008B]On[/COLOR][COLOR=#00008B]Error[/COLOR][COLOR=#00008B]GoTo[/COLOR][COLOR=#6B291B]0[/COLOR][COLOR=#2E3133]
[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#00008B]Function[/COLOR][COLOR=#2E3133]

[/COLOR][COLOR=#00008B]Function[/COLOR][COLOR=#2E3133] LastCol[/COLOR][COLOR=#2E3133]([/COLOR][COLOR=#2E3133]sh [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#2E3133] Worksheet[/COLOR][COLOR=#2E3133])[/COLOR][COLOR=#2E3133]
    [/COLOR][COLOR=#00008B]On[/COLOR][COLOR=#00008B]Error[/COLOR][COLOR=#00008B]Resume[/COLOR][COLOR=#00008B]Next[/COLOR][COLOR=#2E3133]
    LastCol [/COLOR][COLOR=#2E3133]=[/COLOR][COLOR=#2E3133] sh[/COLOR][COLOR=#2E3133].[/COLOR][COLOR=#2E3133]Cells[/COLOR][COLOR=#2E3133].[/COLOR][COLOR=#2E3133]Find[/COLOR][COLOR=#2E3133]([/COLOR][COLOR=#2E3133]What[/COLOR][COLOR=#2E3133]:=[/COLOR][COLOR=#6B291B]"*"[/COLOR][COLOR=#2E3133],[/COLOR][COLOR=#2E3133] _
                            After[/COLOR][COLOR=#2E3133]:=[/COLOR][COLOR=#2E3133]sh[/COLOR][COLOR=#2E3133].[/COLOR][COLOR=#2E3133]Range[/COLOR][COLOR=#2E3133]([/COLOR][COLOR=#6B291B]"A1"[/COLOR][COLOR=#2E3133]),[/COLOR][COLOR=#2E3133] _
                            Lookat[/COLOR][COLOR=#2E3133]:=[/COLOR][COLOR=#2E3133]xlPart[/COLOR][COLOR=#2E3133],[/COLOR][COLOR=#2E3133] _
                            LookIn[/COLOR][COLOR=#2E3133]:=[/COLOR][COLOR=#2E3133]xlFormulas[/COLOR][COLOR=#2E3133],[/COLOR][COLOR=#2E3133] _
                            SearchOrder[/COLOR][COLOR=#2E3133]:=[/COLOR][COLOR=#2E3133]xlByColumns[/COLOR][COLOR=#2E3133],[/COLOR][COLOR=#2E3133] _
                            SearchDirection[/COLOR][COLOR=#2E3133]:=[/COLOR][COLOR=#2E3133]xlPrevious[/COLOR][COLOR=#2E3133],[/COLOR][COLOR=#2E3133] _
                            MatchCase[/COLOR][COLOR=#2E3133]:=[/COLOR][COLOR=#6B291B]False[/COLOR][COLOR=#2E3133]).[/COLOR][COLOR=#2E3133]Column
    [/COLOR][COLOR=#00008B]On[/COLOR][COLOR=#00008B]Error[/COLOR][COLOR=#00008B]GoTo[/COLOR][COLOR=#6B291B]0[/COLOR][COLOR=#2E3133]
[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#00008B]Function[/COLOR][COLOR=#2E3133]

[/COLOR][COLOR=#00008B]Sub[/COLOR][COLOR=#2E3133] CopyRangeFromMultiWorksheets[/COLOR][COLOR=#2E3133]()[/COLOR][COLOR=#2E3133]
    [/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=#2E3133] sh [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#2E3133] Worksheet
    [/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=#2E3133] wb [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#2E3133] Workbook
    [/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=#2E3133] DestSh [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#2E3133] Worksheet

    [/COLOR][COLOR=#00008B]With[/COLOR][COLOR=#2E3133] Application
        [/COLOR][COLOR=#2E3133].[/COLOR][COLOR=#2E3133]ScreenUpdating [/COLOR][COLOR=#2E3133]=[/COLOR][COLOR=#6B291B]False[/COLOR][COLOR=#2E3133]
        [/COLOR][COLOR=#2E3133].[/COLOR][COLOR=#2E3133]EnableEvents [/COLOR][COLOR=#2E3133]=[/COLOR][COLOR=#6B291B]False[/COLOR][COLOR=#2E3133]
    [/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#00008B]With[/COLOR][COLOR=#2E3133]

    [/COLOR][COLOR=#00008B]Set[/COLOR][COLOR=#2E3133] wb [/COLOR][COLOR=#2E3133]=[/COLOR][COLOR=#2E3133] ThisWorkbook
    [/COLOR][COLOR=#00008B]Set[/COLOR][COLOR=#2E3133] DestSh [/COLOR][COLOR=#2E3133]=[/COLOR][COLOR=#2E3133] wb[/COLOR][COLOR=#2E3133].[/COLOR][COLOR=#2E3133]Sheets[/COLOR][COLOR=#2E3133]([/COLOR][COLOR=#6B291B]"Summary"[/COLOR][COLOR=#2E3133])[/COLOR][COLOR=#2E3133]

    [/COLOR][COLOR=#848A91]' Loop through worksheets that start with the name "20"[/COLOR][COLOR=#2E3133]
    [/COLOR][COLOR=#848A91]' This section I tested and it works[/COLOR][COLOR=#2E3133]

    [/COLOR][COLOR=#00008B]For[/COLOR][COLOR=#00008B]Each[/COLOR][COLOR=#2E3133] sh [/COLOR][COLOR=#00008B]In[/COLOR][COLOR=#2E3133] ActiveWorkbook[/COLOR][COLOR=#2E3133].[/COLOR][COLOR=#2E3133]Worksheets
        [/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#2E3133] LCase[/COLOR][COLOR=#2E3133]([/COLOR][COLOR=#2E3133]Left[/COLOR][COLOR=#2E3133]([/COLOR][COLOR=#2E3133]sh[/COLOR][COLOR=#2E3133].[/COLOR][COLOR=#2E3133]Name[/COLOR][COLOR=#2E3133],[/COLOR][COLOR=#6B291B]2[/COLOR][COLOR=#2E3133]))[/COLOR][COLOR=#2E3133]=[/COLOR][COLOR=#6B291B]"20"[/COLOR][COLOR=#00008B]Then[/COLOR][COLOR=#2E3133]

            [/COLOR][COLOR=#848A91]' Specify the range to copy the data[/COLOR][COLOR=#2E3133]
            [/COLOR][COLOR=#848A91]' This portion has also been tested and it works[/COLOR][COLOR=#2E3133]

            sh[/COLOR][COLOR=#2E3133].[/COLOR][COLOR=#2E3133]Range[/COLOR][COLOR=#2E3133]([/COLOR][COLOR=#6B291B]"B2"[/COLOR][COLOR=#2E3133]).[/COLOR][COLOR=#2E3133]Copy

            [/COLOR][COLOR=#848A91]' Paste copied range into "Summary" worksheet in Column D[/COLOR][COLOR=#2E3133]
            [/COLOR][COLOR=#848A91]' This is the part that does not work I get:[/COLOR][COLOR=#2E3133]
            [/COLOR][COLOR=#848A91]' Run-time error '5' :  Invalid procedure call or argument[/COLOR][COLOR=#2E3133]

            [/COLOR][COLOR=#00008B]With[/COLOR][COLOR=#2E3133] DestSh[/COLOR][COLOR=#2E3133].[/COLOR][COLOR=#2E3133]Cells[/COLOR][COLOR=#2E3133]([/COLOR][COLOR=#6B291B]"D2:D"[/COLOR][COLOR=#2E3133])[/COLOR][COLOR=#2E3133]
                [/COLOR][COLOR=#2E3133].[/COLOR][COLOR=#2E3133]PasteSpecial xlPasteValues
                [/COLOR][COLOR=#2E3133].[/COLOR][COLOR=#2E3133]PasteSpecial xlPasteFormats
                Application[/COLOR][COLOR=#2E3133].[/COLOR][COLOR=#2E3133]CutCopyMode [/COLOR][COLOR=#2E3133]=[/COLOR][COLOR=#6B291B]False[/COLOR][COLOR=#2E3133]
            [/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#00008B]With[/COLOR][COLOR=#2E3133]

            [/COLOR][COLOR=#848A91]' This statement will copy the sheet names in the C column.[/COLOR][COLOR=#2E3133]
            [/COLOR][COLOR=#848A91]' I have not been able to check this part since I am stock in the previous step[/COLOR][COLOR=#2E3133]
            DestSh[/COLOR][COLOR=#2E3133].[/COLOR][COLOR=#2E3133]Cells[/COLOR][COLOR=#2E3133]([/COLOR][COLOR=#6B291B]"C2:C"[/COLOR][COLOR=#2E3133]).[/COLOR][COLOR=#2E3133]Resize[/COLOR][COLOR=#2E3133]([/COLOR][COLOR=#2E3133]CopyRng[/COLOR][COLOR=#2E3133].[/COLOR][COLOR=#2E3133]Rows[/COLOR][COLOR=#2E3133].[/COLOR][COLOR=#2E3133]Count[/COLOR][COLOR=#2E3133]).[/COLOR][COLOR=#2E3133]Value [/COLOR][COLOR=#2E3133]=[/COLOR][COLOR=#2E3133] sh[/COLOR][COLOR=#2E3133].[/COLOR][COLOR=#2E3133]Name

        [/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#2E3133]

    [/COLOR][COLOR=#00008B]Next[/COLOR][COLOR=#2E3133]

ExitTheSub[/COLOR][COLOR=#2E3133]:[/COLOR][COLOR=#2E3133]

    Application[/COLOR][COLOR=#2E3133].[/COLOR][COLOR=#00008B]Goto[/COLOR][COLOR=#2E3133] Worksheets[/COLOR][COLOR=#2E3133]([/COLOR][COLOR=#6B291B]"Summary"[/COLOR][COLOR=#2E3133]).[/COLOR][COLOR=#2E3133]Cells[/COLOR][COLOR=#2E3133]([/COLOR][COLOR=#6B291B]1[/COLOR][COLOR=#2E3133])[/COLOR][COLOR=#2E3133]


    [/COLOR][COLOR=#00008B]With[/COLOR][COLOR=#2E3133] Application
        [/COLOR][COLOR=#2E3133].[/COLOR][COLOR=#2E3133]ScreenUpdating [/COLOR][COLOR=#2E3133]=[/COLOR][COLOR=#6B291B]True[/COLOR][COLOR=#2E3133]
        [/COLOR][COLOR=#2E3133].[/COLOR][COLOR=#2E3133]EnableEvents [/COLOR][COLOR=#2E3133]=[/COLOR][COLOR=#6B291B]True[/COLOR][COLOR=#2E3133]
    [/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#00008B]With[/COLOR][COLOR=#2E3133]
[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#00008B]Sub[/COLOR]</code>