SOLVED

VBA: Simple Copy and Paste using Cells

Brass Contributor

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.

 

Copy and Paste from a TableCopy and Paste from a Table

 

Here is what I want to do using VBA:

 

  1. Determine the row for the active cell.
  2. Copy that row from columns A to H.
  3. Paste the value from the copied row to A20.
  4. Cancel copy.

I also want to use "cells" in my VBA code. I have one small routine that works, and one that doesn't. I am curious as to what the problem is with the one that fails.

 

 

Sub HardCodeRowA()

'\ This rountine works.

Dim lRowValue As Long
Dim rnKeyRange As Range

    lRowValue = ActiveCell.Row

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

    Cells(20, 1).PasteSpecial Paste:=xlPasteValues

    Application.CutCopyMode = False

End Sub

 

 

 

Sub HardCodeRowB()

'\ This rountine fails.

Dim lRowValue As Long
Dim rnKeyRange As Range

    Set rnKeyRange = Sheets("Sheet1").Range("Table1")

    lRowValue = ActiveCell.Row

    rnKeyRange(Cells(lRowValue, 1), Cells(lRowValue, 8)).Copy
    '\ Although I get no errors when I single step through, the routine_
    '\ does not seem to copy.

    Cells(20, 1).PasteSpecial Paste:=xlPasteValues

    Application.CutCopyMode = False

End Sub

 

 

Can someone please comment?

 

Also, if there is a more elegant solution using Cells, please share that solution with me too.

 

Thank you for your help.

6 Replies

@KStecyk 

In rnKeyRange(Cells(lRowValue, 1), Cells(lRowValue, 8)), Cells(lRowValue), 1) acts as the row number and Cells(lRowValue, 8) 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
best response confirmed by KStecyk (Brass Contributor)
Solution

@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.

@Hans Vogelaar 

 

In rnKeyRange(Cells(lRowValue, 1), Cells(lRowValue, 8)), Cells(lRowValue), 1) acts as the row number and Cells(lRowValue, 8)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.

@JMB17 

 

Thank you for taking the time and effort to provide a comprehensive answer.

I understand that I provided a range reference. It's interesting that I did not get an error message.

I modified your following line:

 

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

 

to the following:

 

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

 

I believe you referenced this in your final comments by saying that it is generally good practice to qualify your Cells reference. Cells could be referencing the activesheet, which might be different than Sheet1. As I was typing this response to you, I just appreciated the meaning of your comment. Okay, so I should go back to your original for reasons just discussed.

 

I like your macro2. I had to study it to understand it. I like your error checking and your use of Intersect, Resize, and Columns.Count. And I like the nested With statements.

 

I will adapt your macro2 to my situation.

 

I appreciate your help.

 

You're welcome.

In this particular case, you don't have to qualify the Cells reference because the macro first tests to ensure the activesheet is Sheet1 - so it is certain to be referencing the correct sheet and will work either way.

I pointed it out just so that you are aware of it. When I first started learning vba, it was something I overlooked at first on one of my first larger project and spent a fair amount of time trying to debug.

@JMB17 

 

Thank you for your additional comments. It's always helpful learning how to do think ahead.

 

Kevin

1 best response

Accepted Solutions
best response confirmed by KStecyk (Brass Contributor)
Solution

@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.

View solution in original post