Jan 20 2021 01:48 PM
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 is what I want to do using VBA:
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.
Jan 20 2021 02:36 PM
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
Jan 20 2021 02:46 PM
Solution
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.
Jan 21 2021 02:12 PM
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.
Jan 21 2021 02:28 PM
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.
Jan 21 2021 02:59 PM
Jan 22 2021 04:09 PM
Thank you for your additional comments. It's always helpful learning how to do think ahead.
Kevin
Jan 20 2021 02:46 PM
Solution
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.