I’m not mega good on coding parts of excel/macros but I have something working already and want to alter it slightly.

Sub copybrum()
Dim c As Range
Dim j As Integer
Dim Source As Worksheet
Dim Target As Worksheet

‘ Change worksheet designations as needed
Set Source = ActiveWorkbook.Worksheets(“In School Events”)
Set Target = ActiveWorkbook.Worksheets(“Stourbridge”)

j = 2 ‘ Start copying to row 1 in target sheet
For Each c In Source.Range(“F1:F1000”) ‘ Do 1000 rows
If c = “Stourbridge” Then
Source.Rows(c.Row).Copy Target.Rows(j)
j = j + 1
End If
Next c

‘ Change worksheet designations as needed
Set Source = ActiveWorkbook.Worksheets(“In School Events”)
Set Target = ActiveWorkbook.Worksheets(“Birmingham”)

j = 2 ‘ Start copying to row 1 in target sheet
For Each c In Source.Range(“F1:F1000”) ‘ Do 1000 rows
If c = “Birmingham” Then
Source.Rows(c.Row).Copy Target.Rows(j)
j = j + 1
End If
Next c
End Sub

I actually want it to just copy from column A – I and not the whole row. I’ve tried altering the code but can’t get it to work right and most results I’ve found online wanted single columns and not a range hence I cant get it working right.

Any hellllllp?

For Each c In Source.Range(“F1:F1000′ Do 1000 rows

I’ve not given any consideration to the logic of what the code’s doing, but I think you’re missing a closed parenthesis in both of these lines.

To copy a range you want to do a loop, start at cell (a, b) the work along the row before repeating on the next couple. So its a loop in a loop. I think that’s what your after…

Can’t you just use range(A1:I1000)?

Think if it were me I’d define a named range in the source, then you’re not screwed if someone adds / removes lines.

You can just copy a whole block using Range(cells(topleftrow,topleftcol),cells(bottomrightrow,bottomrightcol))

Ah the copy paste hasn’t quite done it right so the parenthesis is actually closed.

I think I know the range but obviously the command of source.rows or copy target.rows is wrong but im unsure how to tell it just the range of cells.

but im unsure how to tell it just the range of cells.

Range(cells(topleftrow,topleftcol),cells(bottomrightrow,bottomrightcol))