How to Fix a macro that copies data from one workbook to another

Question

I am writing a macro to copy data from one workbook to another, from specific columns while check if any of the cells are NA

ive tried changing references

Sub Update_Account()
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim LastRow As Integer
Dim SourceRows As Integer
Dim I As Integer
Workbooks.Open "C:\Users\willi\Documents\Excel\Text.xlsx"
Set ws = ThisWorkbook.Worksheets("LookupLists")
Set ws2 = Workbooks("Text.xlsx").Worksheets("Test")
SourceRows = ws2.Application.WorksheetFunction.CountA(Columns(1)) - 1
For I = 2 To SourceRows
  If ws2.Application.WorksheetFunction.IsNA(ws2.Cells(I, 2)) Then

  Else
    LastRow = ws.Application.WorksheetFunction.CountA(Columns(26))
    ws2.Range(Cells(I, 1), Cells(I, 3)).Copy
    ThisWorkbook.Worksheets("LookupLists").Cells(LastRow, 26).Paste
  End If
  Next I
End Sub

it should copy but instead it gives me error 1004

Possible duplicate of [Why does Range work, but not Cells?](https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells) — BigBen, Jul 28 '19 at 18:51

score 0 · Accepted Answer · answered Jul 29 '19 at 07:28

Try giving your .cells a full reference with ws2 as their workbook. Like this:

Sub Update_Account()
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim LastRow As Integer
Dim SourceRows As Integer
Dim I As Integer
Workbooks.Open "C:\Users\willi\Documents\Excel\Text.xlsx"
Set ws = ThisWorkbook.Worksheets("LookupLists")
Set ws2 = Workbooks("Text.xlsx").Worksheets("Test")
SourceRows = ws2.Application.WorksheetFunction.CountA(Columns(1)) - 1
For I = 2 To SourceRows
  If ws2.Application.WorksheetFunction.IsNA(ws2.Cells(I, 2)) Then

  Else
    LastRow = ws.Application.WorksheetFunction.CountA(Columns(26))
    ws2.Range(ws2.Cells(I, 1), ws2.Cells(I, 3)).Copy
    ThisWorkbook.Worksheets("LookupLists").Cells(LastRow, 26).Paste
  End If
  Next I
End Sub

Thanks, I figured out the error was because the Lastrow Columns should have ws. so it was equal to zero so i was trying to fix all the other things that I thought were wrong — william vinemorris, Jul 29 '19 at 08:22

How to Fix a macro that copies data from one workbook to another

1 Answers1