Код четко открывает рабочую книгу и рабочий лист (с четырьмя строками данных).... но затем ОСТАНАВЛИВАЕТСЯ на "Lastrow = Cells..." с ошибкой 91 "Переменная объекта или переменная блока не установлена".
Dim Lastrow As Long, NumPickups As Integer
Workbooks.Open Filename:="C:\Users\dads\Downloads\Donation Data.xlsm"
Worksheets("DonationDataQuery").Activate
Lastrow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
NumPickups = Lastrow - 1
Range(Cells(1, 1), Cells(Lastrow, 22)).Select
Selection.Copy
Windows("test.xlsm").Activate
Range("J1").Select
ActiveSheet.Paste
Windows("Donation Data.xlsm").Activate
Пересмотренный подход к коду на основе комментария ниже:
Private Sub GeneratePickupData_Click()
'Open the Donation Data.xlsm and copy the data to this worksheet
Workbooks.Open Filename:="C:\Users\dads\Downloads\Donation Data.xlsm"
Worksheets("DonationDataQuery").Activate
'covert numbers brought over by Acess as text to numbers
Range("A1:O50").Select
With Selection
Selection.NumberFormat = "General"
.Value = .Value
End With
Dim Lastrow As Long, NumPickups As Integer
Lastrow = 0
NumPickups = 0
''Lastrow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
Lastrow = Range("A1").End(xlDown).Row
NumPickups = Lastrow - 1
'Range(Cells(1, 1), Cells(Lastrow, 22)).Select
Range("A1", Range("A1").SpecialCells(xlLastCell)).Select
Range("A1:V50").Select
Selection.Copy
Windows("Pickup Form Gen 2.xlsm").Activate
Range("J1").Select
ActiveSheet.Paste
Windows("Donation Data.xlsm").Activate
ActiveSheet.UsedRange.ClearContents
ActiveWorkbook.Save
ActiveWindow.Close
lastrow=Cells(Rows.Count, "A").End(xlUp).Row
илиlastrow=cells(rows.count,22).end(xlup).row
- person Davesexcel   schedule 19.02.2015