help creating formula or macro to compile three cells on 8 lines from each tab in workbook

[pile-it Mark]

i have a workbook that i track flights and maintenance for an aircraft. every year i decide to make changes and have to try and remember how i got to this place. the harder things i do manually till i figure out how to make a VBA work to save me the hassle. this is my issue today.

there are currently 145 tabs in this workbook. a previous one that i would like to use this on has 550. i usually make major changes at 250 tabs and start a new book


total hours total Landings

=SUM(E10:E5338) =SUM(F10:F5338)
for each tab in the workbook retrieve the following cells. Add lines as required. correct sums to seek all cells used. tabs are added for each trip. 80 - 200 a year.

Date trip number tab name Hours Landings
=E12 =R$5 =S12 =T12
=E13 =R$5 =S13 =T13
=E14 =R$5 =S14 =T14
=E15 =R$5 =S15 =T15
=E16 =R$5 =S16 =T16
=E17 =R$5 =S17 =T17
=E18 =R$5 =S18 =T18
=E19 =R$5 =S19 =T19

trying to remember HTML maker now.

Excel 2010
	B	C	D	E	F
4				total hours	total Landings
5
6				=SUM(E10:E5338)	=SUM(F10:F5338)
7	for each tab in the workbook retrieve the following cells. Add lines as required. correct sums to seek all cells used. tabs are added for each trip. 80 - 200 a year.
8
9	Date	trip number	tab name	Hours	Landings
10	=E12	=R$5		=S12	=T12
11	=E13	=R$5		=S13	=T13
12	=E14	=R$5		=S14	=T14
13	=E15	=R$5		=S15	=T15
14	=E16	=R$5		=S16	=T16
15	=E17	=R$5		=S17	=T17
16	=E18	=R$5		=S18	=T18
17	=E19	=R$5		=S19	=T19
18
19
20
time

i have no idea how to pull the tab name. i have a VBA to rename the tab to a cell value, but have naver been able to make it work.

the micro i would like to add this to, unless it can be done with formulas:

Sub CreateSheetCopy()
'
' CreateSheetCopy Macro
'

'
    Sheets("Master 407").Select
    Sheets("Master 407").Copy Before:=Sheets(5)
' Sub Copy1stRow()
    Application.Run "'flightlog 2017 r17.xlsm'!Copy1stRow"
    Application.Run "'flightlog 2017 r17.xlsm'!CopyformatChange"
End Sub

 

[Michael M]

I might not be real bright........but I don't see what you actually want !!
you say "retrieve the following cells and add lines as required".....but what do you want to do with them and where to put them ???
Why not concentrate on what you require for a single tab...then we'll extend it to all tabs.

 

[pile-it Mark]

this is ugly but it pulls data from tab 12 30 2018 and puts it on tab time

Sub timecompiler()
'
' timecompiler Macro Pile-it 01/01/2019 v.0
'

'
    
    Range("B10").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[2]C[3]"
    Range("C10").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[-5]C[15]"
    Range("E10").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[2]C[14]"
    Range("F10").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[2]C[14]"
    Range("B11").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[2]C[3]"
    Range("C11").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[-6]C[15]"
    Range("E11").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[2]C[14]"
    Range("F11").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[2]C[14]"
    Range("B12").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[2]C[3]"
    Range("C12").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[-7]C[15]"
    Range("E12").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[2]C[13]"
    Range("F12").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[2]C[14]"
    Range("B13").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[2]C[3]"
    Range("C13").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[-8]C[15]"
    Range("E13").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[2]C[13]"
    Range("F13").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[2]C[13]"
    Range("B14").Select
    ActiveCell.FormulaR1C1 = ""
    Sheets("12 30 2018").Select
    Range("E17").Select
    Sheets("time").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[2]C[3]"
    Range("B15").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[2]C[3]"
    Range("B16").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[2]C[3]"
    Range("B17").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[2]C[3]"
    Range("E14").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[2]C[13]"
    Range("E15").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[2]C[13]"
    Range("E16").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[2]C[13]"
    Range("E17").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[2]C[13]"
    Range("F14").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[2]C[13]"
    Range("F15").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[2]C[13]"
    Range("F16").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[2]C[13]"
    Range("F17").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[2]C[13]"
    Range("C14").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[-9]C[15]"
    Range("C15").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[-10]C[15]"
    Range("C16").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[-11]C[15]"
    Range("C17").Select
    ActiveCell.FormulaR1C1 = "='12 30 2018'!R[-12]C[15]"
    Range("C18").Select
End Sub

i just realized i could just pull cell E12, S21 & T21 from all tabs and accomplish the same total. this is supposed to keep a running time and landing count for my trip sheets

 

[pile-it Mark]

i would like tab TIME to have columns that pull cell E12, S21, T21 from all sheets that nave number names and then have e6 & E7 auto sum the columns.

post 1 has sheet Time this is the destination sheet


this code copies one row on each sheet and moves it to a named sheet i could possibly modify this a bit

Sub Copy1stRow()
    Dim arr As Variant, m As Variant
    Dim ws As Worksheet, wsTotals As Worksheet
    Dim nr As Long
    
'summary sheet
    Set wsTotals = ThisWorkbook.Worksheets("TOTALS")

'sheets to be excluded
    arr = Array("TOTALS", "distance table", "Master 407", "Master 347", "Summary", "Master", "Sheet 9", "Master 347r9", "vba test")
    
    Application.ScreenUpdating = False
    
'clear contents below row 3
    wsTotals.Range("A4").CurrentRegion.ClearContents
    
    For Each ws In ThisWorkbook.Worksheets
'check ws against exlcuded list
        m = Application.Match(ws.Name, arr, False)
        If IsError(m) Then
            nr = wsTotals.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
'ensure start at row 4
            If nr < 4 Then nr = 4
'copy header row
            ws.Rows(1).Copy
'paste to summary sheet
            wsTotals.Rows(nr).PasteSpecial Paste:=xlPasteValues
'clear clipboard
            Application.CutCopyMode = False

        End If
       
        Next ws
 sortRow4colB wsTotals, xlAscending
    Application.ScreenUpdating = True
   
End Sub