0

Trying to paste underneath all the data on a different workbook but am getting application defined error

Edit:

Sub Matt_Liam()
    Dim Rng As Range
    Set Rng = Workbooks("orders (3).xlsx").Worksheets("Sheet3").Range("A1:AY300")
    Rng.Copy

    Dim i As Long
    Dim s11 As Workbook
    Set s11 = Workbooks("11 Production")
    Set s11 = ActiveWorkbook


    With Sheets("Sheet1")
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lastRow = .Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row            
        Else
            lastRow = 1
        End If
    End With

    Rng.PasteSpecial
End Sub

The errors are here

    Range(Selection, Selection.End(xlDown)).Select

And Here

    Workbooks("11 Production.xlsx").Worksheets("Sheet1").Range("A" & i).Select
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
matt.auerbach
  • 9
  • 4
  • 2
    1) `Dim i as Long`. 2) See [this question](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for the best way to find the last row, instead of using `End(xlDown)`. 3) See [this question](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) for how to avoid using `Select`. – BigBen Aug 06 '19 at 15:53
  • Ok awesome thanks. I updated above with my new code, i am just not sure how to implement the dim functions instead of the .select – matt.auerbach Aug 06 '19 at 16:16
  • You set s11 as a particular workbook, but then immediately set it to the activeworkbook, and then never use it afterwards. You code assumes that Sheet1 is in the activeworkbook by default since you don't say which workbook. – Harassed Dad Aug 07 '19 at 10:01

0 Answers0