Solved: VBA code to copy range to another specific range

[Aussiebear]

I currently have a growing database listing on sheet ("Database") starting at A2:H2 and down to last row,(last row can be determined by Col A. I would like to copy that over to the worksheet("Query") starting at range A11:H11. When pasting it will be overwriting existing data.

How is this done?

[Bob Phillips]

[vba]

With Worksheets("Database")

.Range(.Range("A2:H2"), .Range("A2:H2").End(xlDown)).Copy Worksheets("Query").Range("A11")
End With
[/vba]

[Aussiebear]

Thanks Bob, so the messages you would normally recieve when doing it manually don't need responding to when doing it by VBA?

[Aussiebear]

I've created a button on the Query sheet which calls the Sub RefreshData
[VBA]
Sub cmdRefresh_Click()
Call RefreshData
End Sub
[/VBA]

then used your code to copy and paste


[VBA]Sub RefreshData()

With Worksheets("Database")
.Range(.Range("A2:H2"), .Range("A2:H2").End(xlDown)).Copy Worksheets("Query").Range("A11")
End With

End Sub[/VBA]

so that if I change the any of the data on the Database worksheet either by adding or changing the sort order it updates the Query data accordingly

[Bob Phillips]

Why don't you tie it to the Change event?