Dynamic Range

[michaeljryan78]

I have a data range "states" that is dynamic. I want to copy from a separate worksheet and paste another dynamic range "types" 1 row underneath the last row of datarange "states". Since the ranges are connected to Access query info, the data is fluctuating constantly. My guess is that VBA is the only way to accomplish this. My plan is to have this executed on the "onResfresh" option or some variant thereof. Any help in giudeing me in the right direction is much appreciated.

Thanks!

 

[Trevor G]

I would look at using either UseRange or CurrentRegion.

Try something like this

Sub currentRegionCopy()
Sheets("Sheet2").Activate
Range("H4").Activate
ActiveCell.CurrentRegion.Copy
Sheets.Add
ActiveSheet.Paste
Sheets("Sheet2").Activate
Range("B5").Activate
ActiveCell.CurrentRegion.Copy
Sheets("Sheet4").Activate
Range("A2").End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.PasteSpecial xlPasteAll
End Sub