Forum Discussion

KStecyk's avatar
KStecyk
Brass Contributor
Jan 20, 2021
Solved

VBA: Simple Copy and Paste using Cells

I have a simple question for someone well versed in VBA.   I have a sample workbook titled "CopyPaste.xlsm" with "Table1" on "Sheet1." The workbook is enclosed if you are curious.     Here...
excel
Macros and VBA
  • JMB17's avatar
    JMB17
    Jan 20, 2021

    KStecyk 

     

    Try this:

    Sub macro2()
     
     If ActiveSheet.Name <> Sheet1.Name Then
          Exit Sub
     End If
     
     With Sheet1.ListObjects("Table1")
          If Not Intersect(ActiveCell, .DataBodyRange) Is Nothing Then
               With Intersect(ActiveCell.EntireRow, .DataBodyRange)
                    Sheet1.Range("A20").Resize(1, .Columns.Count).Value = .Value
               End With
          End If
     End With
     
End Sub

     

    That line of HardCodeB does not work because it is expecting a reference in the format rnKeyRange(Row, Col) and you are giving it range references. For example, rnKeyRange(1,1) is a reference to cell "A2" (the top left cell of the rnKeyRange "A2:H16").

     

    Range references are relative to your starting range. Since your lRowValue is not relative to the Range rnKeyRange, but is relative to the worksheet, you should be able to use

    Sheet1.Range(Sheet1.Cells(lRowValue, 1), Sheet1.Cells(lRowValue, 8)).Copy

     

    or

    With rnKeyRange.Worksheet
    .Range(.Cells(lRowValue, 1), .Cells(lRowValue, 8)).Copy
    End With

     

    It is a generally good practice to qualify your Cells reference for situations in which your macro may not be running on the active sheet. For example, Sheet1.Range(Cells(1,1), Cells(1,10)) - the Range object refers to Sheet1, but the Cells objects still refer to the active sheet. Not really necessary in this case since I'm testing to ensure the activesheet is Sheet1 before proceeding, but I do it anyway.

HansVogelaar's avatar
HansVogelaar
MVP
Jan 20, 2021

KStecyk 

In rnKeyRange(Cells(lRowValue, 1), Cells(lRowValue, 8)), Cells(lRowValue), 1) acts as the row number and Cells(lRowValue, 😎 as the column number. Since the numbers in your table are quite high, rnKeyRange(Cells(lRowValue, 1), Cells(lRowValue, 8)) refers to a cell far outside the table.

That is obviously not what you want.

You could use

 

rnKeyRange.Range(Cells(lRowValue - 1, 1), Cells(lRowValue - 1, 8)).Copy

 

I used lRowValue - 1 since rnKeyRange is the data range; it starts in row 2.

 

Here is an alternative version of the macro:

 

Sub HardCodeRowC()
    Dim lRowValue As Long
    lRowValue = ActiveCell.Row
    Cells(20, 1).Resize(1, 8).Value = Cells(lRowValue, 1).Resize(1, 8).Value
End Sub
  • KStecyk's avatar
    KStecyk
    Brass Contributor
    Jan 21, 2021

    HansVogelaar 

     

    In rnKeyRange(Cells(lRowValue, 1), Cells(lRowValue, 8)), Cells(lRowValue), 1) acts as the row number and Cells(lRowValue, 😎as the column number. Since the numbers in your table are quite high, rnKeyRange(Cells(lRowValue, 1), Cells(lRowValue, 8)) refers to a cell far outside the table.

    That is obviously not what you want.

     

    I believe that I provided a range reference and it was expecting a row and column. Please see JMB17's response. If I choose F7, then lRowValue is 7.

     

    You could use

     

    rnKeyRange.Range(Cells(lRowValue - 1, 1), Cells(lRowValue - 1, 8)).Copy

     

    I used lRowValue - 1 since rnKeyRange is the data range; it starts in row 2.

     

    Here is an alternative version of the macro:

     

     

    Sub HardCodeRowC()
    Dim lRowValue As Long
    lRowValue = ActiveCell.Row
    Cells(20, 1).Resize(1, 8).Value = Cells(lRowValue, 1).Resize(1, 8).Value
End Sub

     

     

    Both of those two solutions work well. And you answered my concern of how to modify my code to make it work. I understand why you subtracted 1 from lRowValue.

     

    Thank you for your help.

Resources