Macro copy data from multi sheets to other sheet

[ste33uka]

Hi would any on have a macro code that would copy data from sheets 6,7,8,9,10 cells A70 to F130 to the next available row on a sheet called 'DATA'
My sheet names are just 6,7,8,9,10
Thanks

 

[ste33uka]

i just get compile error when i add elseif

 

[ste33uka]

Ok, if you get stuck, just post what you've tried.

Could you show me how its done please

 

[Fluff]

How did you add it?

 

[ste33uka]

How did you add it?

Thanks fluff, i got it done

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[ste33uka]

Glad you sorted it & thanks for the feedback.

   For i = 6 To 10
      With Sheets(CStr(i))
         If LCase(.Range("Z1").Value) = "yes" Then
            .Range("A70:F130").Copy Sheets("Data").Range("A" & Rows.Count).End(xlUp).Offset(1)
        elseif LCase(.Range("Z1").Value) = "no" Then
            .Range("A70:F130").Copy Sheets("Nodata").Range("A" & Rows.Count).End(xlUp).Offset(1)
        elseif LCase(.Range("Z1").Value) = "mabye" Then
            .Range("A70:F130").Copy Sheets("Maybe").Range("A" & Rows.Count).End(xlUp).Offset(1)
         End If
      End With
   Next i
End Sub

Is there a way to copy data to another workbook called 'moved' ?

 

[Fluff]

Yes, like

If LCase(.Range("Z1").Value) = "yes" Then
            .Range("A70:F130").Copy Workbooks("Moved.xlsx").Sheets("Data").Range("A" & Rows.Count).End(xlUp).Offset(1)

The other workbook will need to be open.

 

[ste33uka]

Yes, like

If LCase(.Range("Z1").Value) = "yes" Then
            .Range("A70:F130").Copy Workbooks("Moved.xlsx").Sheets("Data").Range("A" & Rows.Count).End(xlUp).Offset(1)

The other workbook will need to be open.

Thanks ill give it a go

 

[Fluff]

Ok

 

[ste33uka]

Ok

Nothing copies to other workbook
I have other workbook open
EDIT - Fixed workbook 'Moved' can not be macro enabled for it to work

Sub ste33uka()
   Dim i As Long
 
   For i = 1 To 20
      With Sheets(CStr(i))
         If LCase(.Range("Z1").Value) = "yes" Then
            .Range("A70:F130").Copy Workbooks("Moved.xlsx").Sheets("Data").Range("A" & Rows.Count).End(xlUp).Offset(1)
         ElseIf LCase(.Range("Z1").Value) = "no" Then
            .Range("A70:F130").Copy Workbooks("Moved.xlsx").Sheets("NoData").Range("A" & Rows.Count).End(xlUp).Offset(1)
         End If
      End With
   Next i
End Sub