Forum Discussion
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.
Copy and Paste from a Table
Here is what I want to do using VBA:
- Determine the row for the active cell.
- Copy that row from columns A to H.
- Paste the value from the copied row to A20.
- 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.
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 SubThat 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 WithIt 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.
6 Replies
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 SubThat 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 WithIt 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.
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.
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 SubIn 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 SubBoth 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.
