PDA

View Full Version : Solved: VBA code to copy range to another specific range



Aussiebear
02-19-2010, 06:21 AM
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
02-19-2010, 06:38 AM
With Worksheets("Database")

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

Aussiebear
02-19-2010, 07:00 AM
Thanks Bob, so the messages you would normally recieve when doing it manually don't need responding to when doing it by VBA?

Aussiebear
02-19-2010, 07:21 AM
I've created a button on the Query sheet which calls the Sub RefreshData

Sub cmdRefresh_Click()
Call RefreshData
End Sub


then used your code to copy and paste


Sub RefreshData()

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

End Sub

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
02-19-2010, 08:00 AM
Why don't you tie it to the Change event?