0

This main issue is getting the correct data selected. I can't just select the entire column from my openbook because I get this "paste area isn't the same" type of error when I try to paste into the bottom of my active worksheet. If I could just select whatever cell is below the first row in the filtered list, it should work, but the row will change daily.

 `            Sub GetData2()

Dim FileToOpen As Variant
Dim openbook As Workbook

ChDrive "C"
ChDir "C:\Users\alovell\Desktop\q400"
Application.ScreenUpdating = False
FileToOpen = Application.GetOpenFilename(Title:="Browse for your file", FileFilter:="Excel 
Files(*.xls*),*xls*")
If FileToOpen <> False Then
    Set openbook = Application.Workbooks.Open(FileToOpen)

openbook.Sheets(1).Range("A:A").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
ActiveCell.Offset(-1, 1).Range("A1").Select
Selection.AutoFilter
ActiveSheet.Range("$A:$D").AutoFilter Field:=2, Criteria1:= _
    "=fabricate*", Operator:=xlAnd, Criteria2:="<>*machine*"
    
    openbook.Sheets(1).Range("$A:$A").Offset(0, 1).Copy
    ThisWorkbook.Sheets(3).Range("A1").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues
    

    
      End If
    Application.ScreenUpdating = False

    End Sub
    `
shot040
  • 1
  • 2
  • [Find the last row perhaps](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). – BigBen Oct 22 '21 at 13:49
  • **Do not use `Select`, or `Copy/PasteValues`** but instead reference the source and destination cells using the `Range()` function directly. – JAlex Oct 22 '21 at 14:12

1 Answers1

0

If you select an area of cells to paste inside, you are saying to Excel that you are aware of the paste area size, which will be used as a control measure.

So, the solution is:
Instead of selecting the whole area where you want to paste, just select the first cell, and Excel will automatically take the rest of the cells (the ones beneath and the ones to the right). When you only select one cell of the paste area, Excel won't perform the mentioned control measure.

Dominique
  • 16,450
  • 15
  • 56
  • 112
  • The problem is that OP is copying the entire column B, and an entire column can only be pasted in row 1. – BigBen Oct 22 '21 at 13:54
  • I can select the single cell I need to paste into, but I can't figure out how to not select the first cell on my openbook so that I don't get the paste error. – shot040 Oct 22 '21 at 13:57