Transferring data from multiple sheets

Hi folks

I have to following code:

Worksheets("GW").Range(Range("E7"), Range("E500").End(xlUp)).Copy
    Worksheets("FINAL").Range("A2").Insert shift:=xlDown

Worksheets("GW").Range(Range("D7"), Range("D500").End(xlUp)).Copy
    Worksheets("FINAL").Range("B2").Insert shift:=xlDown
    
Worksheets("GW").Range("O5").Copy
    Worksheets("FINAL").Range("B500").End(xlUp).Offset(2, 0).PasteSpecial Paste:=xlPasteValues

the idea being that it transfers a varied range of data from one sheet into another - easy!

however, I have 9 different sheets that I want this to apply to and all data to get transferred into the FINAL sheet.

Any ideas how to simplify the code in order to do this?

I tried a FOR EACH code but got twisted round.

any help would be great.

TVM

Something like this:

Sub Superdonk(): Dim ws As Worksheet, wf As Worksheet, c As Long, SNames As String, n As Integer
        SNames = Array(" ", "GW", "GW", "GW", "GW", "GW", "GW", "GW", "GW", "GW")
                        Set wf = Sheets("Final")
                For n = 1 To UBound(SNames): Set ws = Sheets(SNames(n))
                        ws.Range(Range("E7"), Range("E500").End(xlUp)).copy
                            c = c + 1: wf.Cells(2, c).Insert Shift:=xlDown

                        ws.Range(Range("D7"), Range("D500").End(xlUp)).copy
                            c = c + 1: wf.Cells(2, c).Insert Shift:=xlDown
    
                                ws.Range("O5").copy
    wf.Range("B500").End(xlUp).Offset(2, 0).PasteSpecial Paste:=xlPasteValues
                Next n
End Sub

Rewrite the reds

Thanks for the quick reply...however...

I get an 'Expected Array' error on the following line:

For n = 1 to UBound

it specifically highlights the 'UBound'

ideas?

Hi Superdonk,

I should have declared SNames as Variant:

Sub Superdonk(): Dim ws As Worksheet, wf As Worksheet, c As Long, SNames, n As Integer
        SNames = Array(" ", "GW", "GW", "GW", "GW", "GW", "GW", "GW", "GW", "GW")
                        Set wf = Sheets("Final")
                For n = 1 To UBound(SNames): Set ws = Sheets(SNames(n))
                        ws.Range(Range("E7"), Range("E500").End(xlUp)).copy
                            c = c + 1: wf.Cells(2, c).Insert Shift:=xlDown

                        ws.Range(Range("D7"), Range("D500").End(xlUp)).copy
                            c = c + 1: wf.Cells(2, c).Insert Shift:=xlDown
    
                                ws.Range("O5").copy
    wf.Range("B500").End(xlUp).Offset(2, 0).PasteSpecial Paste:=xlPasteValues
                Next n
End Sub