Copying a column from one sheet to another dynamically

Hi, so i have working code which will copy column A from one sheet to column B on another sheet.

The VBA is the following

Private Sub CopyDomainName()
    Dim sourceColumn As Range, targetColumn As Range

    Set sourceColumn = Worksheets("Dataset Level Metadata").Range("A3:A100")
    Set targetColumn = ActiveSheet.Range("B2:B100")

    sourceColumn.Copy Destination:=targetColumn
End Sub

However the range "A3:A100" needs to be updated to pick up the correct column from another sheet.

I have EXCEL code for this,

=INDIRECT("DomainDetails!D"&MATCH(Sheet1!C$1,INDIRECT("DomainDetails!$B$3:$B$18"),0)+1)

This will return the letter A. Is there any way to incorporate this into the VBA script, on this line?

Set sourceColumn = Worksheets("Dataset Level Metadata").Range("A3:A100")

In order the make sure the range A3:A100 uses the correct column.

Thanks in advance


Book1.xlsm

Hi,

With stuff like this I prefer to use a variable that identiofies the column offset from A and then use

Formula:

     Set sourceColumn = Worksheets("Dataset Level Metadata").Range("A3:A100").Offset(0,col_offset)

and I usually use a helper cell in the worksheet to work out the column offset required and read that into the VBA

Originally Posted by craig159753

Im not sure how i can incorporate this in my VBA script. Thanks for the reply though

Upload the workbook and give us some examples of what you would do and what result you would expect and no doubt we can help further.