inserting rows from different sheets into master sheet

Hello guys! I'm new at this forum and new also at excel macros
If you can help me I'll be very grateful

The thing is:
I have several sheets on a workbook, one of them (the last one) is the master sheet. I need to copy a certain amount of data from these others sheets (as a value, with special paste) to the master sheet. A defined number of rows will be copied to the master sheet, but in each sheet this number will be different (but that's not an issue.. I think). The user will add a new sheet everyday (manually), and when he presses a button (on the master sheet), the new data will be inserted and copied to the master sheet.

I'm sorry for my english, and I hope this makes sense.

How I plan to do it:

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 11-01-2011 by amoya

Dim direccion1 As Integer
Dim direccion2 As Integer
Dim dir As Integer
Dim hoja As Worksheet
Dim libro As Workbook
Dim contador As Integer
Dim i As Integer


Set libro = ActiveWorkbook
contador = ActiveWorkbook.Worksheets.Count


For i = 2 To (contador - 2)
    Cells.Find(What:="Nave", LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Cells.FindNext(After:=ActiveCell).Activate
    Cells.FindNext(After:=ActiveCell).Activate
    Cells.FindNext(After:=ActiveCell).Activate
    Cells.FindNext(After:=ActiveCell).Activate
    Cells.FindNext(After:=ActiveCell).Activate
    Cells.FindNext(After:=ActiveCell).Activate
    Cells.FindNext(After:=ActiveCell).Activate
    direccion1 = ActiveCell.Row
    
    Cells.Find(What:="Total Consumo", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        
    direccion2 = ActiveCell.Row
    dir = direccion2 - direccion1

    Range("B" & direccion1, "M" & direccion2).Select
    Selection.Copy
    Sheets("EA").Select

        Rows("8", dir).Select
        Selection.Insert Shift:=xlDown
        Range("E8", "P" & dir).Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        
Next i
End Sub

As you can see, I search in every sheet (with three exceptions) two words (the first one I searched several times using the macro recorder, but that's what I wanted). When I find "Naves" I save the row, and search for the next word, then, save the location too.
Then, I select a Range in the active sheet and copy it (from the row of the first word, until the row of the second one)
Then, I select the master sheet, and here is where the issue starts: the syntax of Rows is wrong. I want to select the rows from 8 to "dir" (which is a number) and then insert there that very same number of columns (dir-8). After that, pastespecial the data from the sheets. What is the correct syntax for this?

The other problem I have is that when I run the macro from the master sheet, I think it tries to find the words "Nave" and "Total Consumo" even if I excluded it from the search, why does that happen?

My code is really rudimentary, I can see that, but I'm learning

Thank you in advance!

I've managed some issues and my code now is:

Sub pruebas2_funciona()
'
' Macro4 Macro
' Macro recorded 11-01-2011 by amoya

Dim direccion1 As Integer
Dim direccion2 As Integer
Dim dir As Integer
Dim hoja As Worksheet
Dim libro As Workbook
Dim contador As Integer
Dim i As Integer
Dim dirn As Integer
Dim rango As Range



Set libro = ActiveWorkbook
contador = ActiveWorkbook.Worksheets.Count

For i = 2 To contador - 2

    Cells.Find(What:="Nave", LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        
    Cells.FindNext(After:=ActiveCell).Activate
    Cells.FindNext(After:=ActiveCell).Activate
    Cells.FindNext(After:=ActiveCell).Activate
    Cells.FindNext(After:=ActiveCell).Activate
    Cells.FindNext(After:=ActiveCell).Activate
    Cells.FindNext(After:=ActiveCell).Activate
    Cells.FindNext(After:=ActiveCell).Activate
    direccion1 = ActiveCell.Row
    
    Cells.Find(What:="Total Consumo", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        
    direccion2 = ActiveCell.Row
    dir = direccion2 - direccion1
    dirn = dir + 9

    Range("B" & direccion1, "M" & direccion2).Select
    Selection.Copy
    
    Sheets("EA").Select
    
    Range("E8", "P" & dirn).Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
        
    
    Rows("8:" & dirn).Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown
    
Next i

End Sub

But I can't call the macro from the master sheet How do I fix that?
When I call the macro from the other sheets, It "kinda" works.. I have some issues with the ranges, so the data appears weird.

Is there a way to store "selection.copy"?

Thank you

I'm still having problems and this should be really simple!

Sub pruebas2_funciona()
'
' Macro4 Macro
' Macro recorded 11-01-2011 by amoya

Dim direccion1 As Integer
Dim direccion2 As Integer
Dim dir As Integer
Dim hoja As Worksheet
Dim libro As Workbook
Dim contador As Integer
Dim i As Integer
Dim dirn As Integer
Dim rn As Range


Set libro = ActiveWorkbook
contador = ActiveWorkbook.Worksheets.Count

For Each hoja In libro.Worksheets
    If hoja.Name <> "EA" Or hoja.Name <> "NG" Or hoja.Name <> "Listas" Then

        
        Cells.Find(What:="Nave", After:=hoja.Range("A1"), LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        True, SearchFormat:=False).Activate
        direccion1 = ActiveCell.Row
        
        Cells.Find(What:="Total Consumo", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Activate
            
        direccion2 = ActiveCell.Row
        dir = direccion2 - direccion1
        dirn = dir + 9
    
        Range("B" & direccion1, "M" & direccion2).Select
        Selection.Copy
        
        Sheets("EA").Select
        
      
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
            
        Rows(8 & ":" & dirn).Select
        Application.CutCopyMode = False
        Selection.Insert Shift:=xlDown
        


    End If
Next hoja

End Sub

It seems that the macro doesn't run over all the sheets, how can I fix this?

The code finally works Even if I nobody answered, I read the other topics and they were VERY helpful. Thank you

Sub actualizar()
'
' Macro4 Macro
' Macro recorded 11-01-2011 by amoya

Dim direccion1 As Integer
Dim direccion2 As Integer
Dim dir As Integer
Dim hoja As Worksheet
Dim libro As Workbook
Dim contador As Integer
Dim i As Integer
Dim j As Integer


Set libro = ActiveWorkbook
contador = ActiveWorkbook.Worksheets.Count


For i = 2 To contador - 2

    Sheets(i).Select
    Cells.Find(What:="Nave", LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        True, SearchFormat:=False).Activate

    direccion1 = ActiveCell.Row
    
    Cells.Find(What:="Total Consumo", LookIn:=xlFormulas, After:=ActiveCell, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        
    direccion2 = ActiveCell.Row
    dir = direccion2 - direccion1

    Range("B" & direccion1, "M" & direccion2).Select
    Selection.Copy
    Sheets("EA").Select
    dirn = dir + 10
    


    
    Range("E8", "P" & dirn).Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False

    If i <> contador - 2 Then
        Rows(8 & ":" & dirn).Select
        Selection.Insert Shift:=xlDown
    End If