locked
What is the correct syntax to dynamically pass the values to the .Range property using VBA RRS feed

  • Question

  • In my workbook I have to write vba code to copy a range of cells from one worksheet to another worksheet.

    I've already figured out how to program a macro that gets the current row and column when the user selects a checkbox on the worksheet.

    From this point I have to select a certain range that just spans 1 row, but multiple columns. This range that is selected will then be copied to another worksheet in the same workbook.

    For example:

    The macro will get the current row/column when the user selects a checkbox - I already figured out this part.

    The row and column is stored in variables. At this point I know the row. I just need to know how to set the values for the .Range property so I can copy the range. Below is the VBA code without the variables passed to the Range property:

    Sheets("ItemsToList").Range("c19:o19").Copy

    This range, c19:o19, has to be dynamically set in the code. As you can see, the columns 'C' and 'O' will always be the same. It's the row that will change depending on what row the user clicks.

    Questions:

    I've tried variations of .Range(" & LCol & LRow & ":" ...... - but this doesn't work. What is the correct syntax for .Range so I can pass it variables?

    Also, I need to copy the same formatting, formulas, etc to the other worksheet. How would I do this?

    Thank You

    Keith


    Keith Aul

    Friday, May 22, 2015 5:59 PM

Answers

  • Re:  specify a range

    Sub Example()
     Dim lngRow As Long
     
     lngRow = 19
     'Sheets("ItemsToList").Range("c19:o19").Copy

     With Sheets("ItemsToList")
       .Range(.Cells(lngRow, 3), .Cells(lngRow, 15)).Copy
     End With
    End Sub
    '---
    Note the use of dots to tie it all together

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Marked as answer by KeithAul Sunday, May 24, 2015 2:14 AM
    • Edited by James Cone Monday, October 31, 2016 10:58 PM
    Friday, May 22, 2015 9:26 PM
  • Keith,

    Is this what you are asking for:

    Sub CopyPaste()
        Dim lRow As Long
        Dim sh As Worksheet
        lRow = 19
        Sheets("ItemsToList").Range("c" & lRow & ":o" & lRow).Copy
        Set sh = Sheets("DestinationSheet")
        sh.Paste sh.Range("B9")
        Application.CutCopyMode = False
    End Sub
    

    The code copies the range C19:O19 on sheet "ItemsToList"

    and pastes that range in B9 on the sheet "DestinationSheet"

    Jan

    • Marked as answer by KeithAul Sunday, May 24, 2015 2:14 AM
    Friday, May 22, 2015 9:29 PM

All replies

  • Re:  specify a range

    Sub Example()
     Dim lngRow As Long
     
     lngRow = 19
     'Sheets("ItemsToList").Range("c19:o19").Copy

     With Sheets("ItemsToList")
       .Range(.Cells(lngRow, 3), .Cells(lngRow, 15)).Copy
     End With
    End Sub
    '---
    Note the use of dots to tie it all together

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Marked as answer by KeithAul Sunday, May 24, 2015 2:14 AM
    • Edited by James Cone Monday, October 31, 2016 10:58 PM
    Friday, May 22, 2015 9:26 PM
  • Keith,

    Is this what you are asking for:

    Sub CopyPaste()
        Dim lRow As Long
        Dim sh As Worksheet
        lRow = 19
        Sheets("ItemsToList").Range("c" & lRow & ":o" & lRow).Copy
        Set sh = Sheets("DestinationSheet")
        sh.Paste sh.Range("B9")
        Application.CutCopyMode = False
    End Sub
    

    The code copies the range C19:O19 on sheet "ItemsToList"

    and pastes that range in B9 on the sheet "DestinationSheet"

    Jan

    • Marked as answer by KeithAul Sunday, May 24, 2015 2:14 AM
    Friday, May 22, 2015 9:29 PM
  • Jan/Jim

    Thanks you for your solutions. I tried both of them and they both work.

    I would like to know, because I was given multiple solutions, what are the advantages or disadvantages to each solution?

    Keith


    Keith Aul

    Sunday, May 24, 2015 2:17 AM
  • Re:  "advantages or disadvantages to solution"

    Keith,
    A code line that joins one or more items using "&" can run slower then a code line without it.
    When running thru several thousand rows in a loop, milli-seconds can add up.
    Otherwise, it doesn't really matter.

    Complicating the issue is that fewer dots equals faster code.
    It is a common practice to time code to determine if a certain variation is faster.
    That can be done by running a loop say 25,000+ times with the code in it and measuring how long it takes.

    Note: 
    Jan actually answered your question about... " I've tried variations of .Range(" & LCol & LRow & ":" "
    I provided the way I would do it.
    '---
    Jim Cone


    • Edited by James Cone Monday, October 31, 2016 10:58 PM
    Sunday, May 24, 2015 2:56 AM