Method of Range Object_Worksheet failed error in copying and pasting data from a worksheet to another

Question

I am trying to copy and paste data from an input sheet into an output sheet, and once the data is in the next spreadsheet, it will be filled down the next rows from a starting date to an ending date.

As is, the code gets me the following errors when debugging: ws1.Range("A" & NextRow) = Method of Range Object_Worsheet failed; same for ws1.Range("B" & NextRow); ws1.Range("C" & NextRow) etc...

ws1.Cells(LastRow, "H") = Application-defined or object-defined error.

Also, I noticed that when I set RawDataEntries it equals 37, that is the last non-empty row, but then when I try to use the For loop and point my mouse on RawDataEntries in For n=1 To RawDataEntries, VBA returns me a value of 105, which seems to come out of nowhere.

I think the logic behind the code is correct. What might have gone wrong?

Sub AddFlight_Click()
Const RNG_END_DT As String = "N2"
Dim NextRow1 As Long, LastRow1 As Long, ws1 As Worksheet
Dim ws2 As Worksheet, RawDataEntries As Long


Set ws1 = Sheets("JetAir Flight Plan")
Set ws2 = Sheets("TUI B Flight Plan")
LastRow1 = ws1.Range("A" & Rows.Count).End(xlUp).Row
NextRow1 = LastRow1 + 1
RawDataEntries = ws2.Range("A" & Rows.Count).End(xlUp).Row

For n = 1 To RawDataEntries

'Data from an input worksheet is copied and pasted into specific cells in an output worksheet.

    ws1.Range("A" & NextRow).Value = ws2.Range("A" & n).Value
    ws1.Range("B" & NextRow).Value = ws2.Range("B" & n).Text
    ws1.Range("D" & NextRow).Value = ws2.Range("D" & n).Text
    ws1.Range("E" & NextRow).Value = ws2.Range("E" & n).Text
    ws1.Range("F" & NextRow).Value = ws2.Range("F" & n).Text
    ws1.Range("G" & NextRow).Value = ws2.Range("G" & n).Text
    ws1.Range(RNG_END_DT).Value = ws2.Range("H" & n).Value

'A series of dates is created from a starting date
'    to an ending date in column A of ws1.

    ws1.Range("A" & NextRow).DataSeries Rowcol:=xlColumns, _
        Type:=xlChronological, Date:=xlDay, Step:=7, _
        Stop:=ws1.Range(RNG_END_DT).Value, Trend:=False

'The data filled in the last row with the userform data through
' the first part of the macro will be copied and pasted in
' the next row until there is a blank cell in column A.

    LastRow1 = ws1.Range("A" & Rows.Count).End(xlUp).Row
    ws1.Range(ws1.Range("B" & NextRow), ws1.Cells(LastRow, "H")).FillDown

'We repeat the process for other rows on the sheet data are pulled from

Next n

Thanks a lot.

TheGuyThatDoesn'tKnowMuch · Accepted Answer · 2016-03-09 17:27:52Z

0

Change NextRow to NextRow1. Right now you are calling a variable that doesn't exist.

Or you can do the opposite (NextRow1 to NextRow)

Same with LastRow and LastRow1

answered Mar 9, 2016 at 17:27

TheGuyThatDoesn'tKnowMuch

1,2502 gold badges17 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Method of Range Object_Worksheet failed error in copying and pasting data from a worksheet to another

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related