copy one row from one sheet to another row in another sheet

Question

I am getting errors and I can not see why.

I have an array looks for matches from the array down a column. When it matches it copys the used range of that row and paste it below the last row on another page.

Dim iCL As Integer, USCurRow As Integer, USlRow As Integer, USlCol As Integer, USlRow2 As Integer
Dim USCountryList(1 To 1) As String
Dim USCtry As String

USCountryList(1) = "Country1"

For iCL = 1 To UBound(USCountryList)

  USCtry = USCountryList(iCL)
  USlRow = Sheets("US Confirmed").Cells(Rows.Count, 1).End(xlUp).Row
  USlCol = Sheets("US Confirmed").Cells(1, Columns.Count).End(xlToLeft).Column
  USlRow2 = Sheets("World Confirmed").Cells(Rows.Count, 2).End(xlUp).Row

    For USCurRow = 2 To USlRow
       If Sheets("US Confirmed").Cells(USCurRow, 1) = USCtry Then
         Sheets("World Confirmed").Range(Cells(USlRow2 + 1, 1), Cells(USlRow2 + 1, USlCol)) = _
         Sheets("US Confirmed").Range(Cells(USCurRow, 1), Cells(USCurRow, USlCol))
       End If
    Next USCurRow
Next iCL

I keep getting an application-defined or object-defined error during the Copy part. Stepping through the values of the variables appear to be valid: hard coding them in as such:

  Sheets("World Confirmed").Range(Cells(268,1),cells(268,121))= _
  Sheets("US Confirmed").Range(Cells(3, 1), Cells(3, 121))

Generates same application-defined or object-defined error error.

I have also tried range select. selection copy range select selection paste and still running into an error

`Sheets("World Confirmed").Range(Cells(USlRow2 + 1, 1), Cells(USlRow2 + 1, USlCol))` itself is an object. You want to assign values to this object. I don't know how it is for `Range` but for `Cells` you can use assign a value to a kind of `Formula` or `Value` property/attribute. — Tuckbros, May 21 '20 at 22:20
@Tuckbros not too sure what is meant by this. the verbiage is a bit over my head. — Bill Flippen, May 22 '20 at 16:05
I just wanted to say, you have to choose the property of the range you want to update. You want to assign the ̀Value` of the source Range to the `Value` of the taget Range. — Tuckbros, May 22 '20 at 18:38

score 1 · Accepted Answer · answered May 22 '20 at 03:11

I believe the error is caused by your unqualified Worksheet.Cells() property.

In case you aren't familiar with object qualification, let's take a snippit from the Microsoft documentation for the Worksheet.Cells property:

Syntax

expression.Cells

expression A variable that represents a Worksheet object.

...

Using this property without an object qualifier returns a Range object that represents all the cells on the active worksheet

So in your line
Sheets("World Confirmed").Range(Cells(USlRow2 + 1, 1), Cells(USlRow2 + 1, USlCol)) = _ Sheets("US Confirmed").Range(Cells(USCurRow, 1), Cells(USCurRow, USlCol))
the Range() property is qualified to Sheets("World Confirmed") and Sheets("US Confirmed") but your Cells() properties that are defining your start and end cells for your range are not.

If you qualify your Cells() properties to the relevant sheet your runtime error will cease but you will likely find the values won't be assigned to Sheet1 as expected.

Using the .Value property on each range should resolve this, like so:

For USCurRow = 2 To USlRow
       If Sheets("US Confirmed").Cells(USCurRow, 1) = USCtry Then
         Sheets("World Confirmed").Range(Sheets("World Confirmed").Cells(USlRow2 + 1, 1), Sheets("World Confirmed").Cells(USlRow2 + 1, USlCol)).Value = _
         Sheets("US Confirmed").Range(Sheets("US Confirmed").Cells(USCurRow, 1), Sheets("US Confirmed").Cells(USCurRow, USlCol)).Value
       End If
    Next USCurRow

To improve readability and maintainability I'd suggest assigning your worksheets to a variable like so:

Dim SourceSheet as Worksheet
Dim DestinationSheet as Worksheet

Set SourceSheet = Thisworkbook.Sheets("US Confirmed")
Set DestinationSheet = ThisWorkbook.Sheets("World Confirmed")

    For USCurRow = 2 To USlRow
       If Sheets("US Confirmed").Cells(USCurRow, 1) = USCtry Then
         DestinationSheet.Range(DestinationSheet.Cells(USlRow2 + 1, 1), DestinationSheet.Cells(USlRow2 + 1, USlCol)).Value = _
         SourceSheet.Range(SourceSheet.Cells(USCurRow, 1), SourceSheet.Cells(USCurRow, USlCol)).Value
       End If
    Next USCurRow

@Samual Everson Thank you so much, not only did you fix the issue, you enlightened me. I used to always slap `.value` onto pretty much everything, but I took a hiatus for a year or two from doing any vba, when I resumed I got lazy. For the record, I did have variables set for the workbooks, but took them away in case they were partly to blame. I have other subs that are called and they can call this one and I wanted to make sure it wasn;t being passed along and being changed. (Was trying everything before posting) — Bill Flippen, May 22 '20 at 16:10
@BillFlippen I think you've taken some good debugging steps there! I thought it worth mentioning, there is no real benefit in using the `integer` data type any more - I couldn't find the MSDN page that points this out but I did find [this stackoverflow answer](https://stackoverflow.com/a/26409520/9663006) that explains why very well AND also has a link and quote from the elusive MSDN page. :) — Samuel Everson, May 23 '20 at 08:13

copy one row from one sheet to another row in another sheet

1 Answers1

Syntax