CODEWITHSUNDEEP
excelvba
Crystal
Crystal

Reputation: 1

Copy Pasting Range of Cells with Dynamic Start Point

I'm having trouble copy pasting a range of cells based on a dynamic start point (the start point is 1 cell beneath a cell with a specified value).

This specific range of cells will always be in columns A-Z, but the row is dynamic across worksheets. However, this range of cells is always preceded by a row above with a specific cell value, let's say "Dataset Here".

So in an example sheet, I need to copy A650:Z700, and the cell "Dataset Here" is in A649.

How can I copy a range based on the requirement that the range falls underneath the cell with the value "Dataset Here"?

I was able to identify the starting row, but am lost how to turn it into a dynamic version of Range("A650:Z700").Copy: 

Dim StartRow As Long
    StartRow = Range("A:BA").Find("Dataset Here").Row + 1

Upvotes: 0

Views: 61

Answers (1)

SNicolaou
SNicolaou

Reputation: 550

Hello Crystal and Welcome.

You could do something like the below. This works with the assumption that all your cells and columns below the 'Dataset Here' are populated (otherwise the copy range might not be correctly defined). The following example copies cells A6:B9 to cell F1

enter image description here

Option Explicit

Public Sub sCopyCells()

  Call sCopyCellsForWorksheet(Worksheets("Sheet1"))

End Sub

Private Sub sCopyCellsForWorksheet(ByRef ws As Worksheet)

  Dim target As Range, source As Range, destination As Range

  Set target = ws.Range("A:A").Find("Dataset Here")
  Set source = Range(target.Offset(1, 0), target.End(xlDown).End(xlToRight))
  Set destination = ws.Range("F1")

  source.Copy
  destination.PasteSpecial xlPasteAll

End Sub

Upvotes: 0

Related Questions