Type Mismatch when copying and pasting from second workbook with VBA

Question

I have two excel files, and I need to copy only necessary rows from a raw data sheet into my new workbook.

The code I have so far is:

Sub Pop_Sheet()

  Dim WB As Workbook                                  ' Declare variables and types
  Dim WS As Worksheet
  Dim NumRows As Integer
  Dim BadCols As Range
  Dim BadRow As Range


  Set WB = Get_File()                                 ' Get file from user
  Set WS = WB.Sheets(1)                               ' Select first sheet in workbook

                                                      ' Delete unneeded rows/columns
  Set BadCols = WS.Range("A:A,B:B,D:D,G:G,H:H,I:I,J:J,K:K,L:L,M:M,P:P,Q:Q,R:R,S:S,V:V,W:W,X:X,AA:AA,AC:AC,AE:AE,AF:AF,AG:AG,AH:AH,AI:AI,AL:AL,AO:AO,AP:AP,AV:AV,AW:AW,AX:AX,AY:AY,AZ:AZ,BA:BA,BB:BB,BC:BC,BD:BD,BE:BE,BF:BF,BG:BG")
  Set BadRow = WS.Rows("1:1")

  BadCols.Delete
  BadRow.Delete

  WS.Columns("N:N").Select                            ' Insert empty column at N
  Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

  NumRows = WS.Range("A1048576").End(xlUp).Row        ' Count number of rows used in raw data

  Rows("5:" + (5 + NumRows)).Select                   ' Insert X rows above...It should at least... Errors out before here
  ActiveCell.EntireRow.Insert

  WS.Range("A1:U" + NumRows).Select.Copy              ' Copy and paste ???
  Rows("4:" + (4 + NumRows)).Select.Paste

  Close_File (WB)                                     ' Clean up, free memory
  Set WB = Nothing
  Set WS = Nothing
  Set BadCols = Nothing
  Set BadRow = Nothing

End Sub

Private Function Get_File() As Workbook

  Dim FPath As Variant                                ' Declare variable for filepath
                                                      ' Get path from file picker
  FPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx", Title:="Select File To Import")

  If Not FPath = False Then                           ' If a file was chosen
    Set Get_File = Workbooks.Open(FPath)              ' Open file
  Else
    Set Get_File = Nothing                            ' Otherwise do nothing
  End If

  Set FPath = Nothing                                 ' Free Memory

End Function

Private Sub Close_File(ByRef WB As Workbook, Optional Save As Boolean = False)

  WB.Close (Save)                                     ' Close raw data workbook

End Sub

I get a "Type Mismatch" error when I run this however. I believe it has to do with copying and pasting into cells of a different size, which is why I have attempted to insert the number needed, select those, and paste into them.

Any help or guidance in general is really appreciated. Thank you!

Answer 1

The short answer is that this line is incorrect:

Rows("5:" + (5 + NumRows)).Select

Instead, you need to use the "concatenate" operator which is "&":

Rows("5:" & (5 + NumRows)).Select

You need to fix the other lines where you do the same thing. "&" combines two strings.

The long answer is that your code can be cleaned up quite a bit to make it faster and easier to read. My major advice is not to "select" things and then "do" actions -- just attach the action to the range itself (edits marked with **):

Sub Pop_Sheet()

  Dim WB As Workbook                                  ' Declare variables and types
  Dim WS As Worksheet
  Dim NumRows As Integer
  Dim BadCols As Range
  Dim BadRow As Range


  Set WB = Get_File()                                 ' Get file from user
  Set WS = WB.Sheets(1)                               ' Select first sheet in workbook

                                                      ' Delete unneeded rows/columns
  Set BadCols = WS.Range("A:B,D:D,G:M,P:S,V:X,AA:AA,AC:AC,AE:AI,AL:AL,AO:AP,AV:BG")   'Can select multiple adjacent columns by using start and end columns
  Set BadRow = WS.Rows(1)      ' ** This function can just take a number rather than the range string

  BadCols.Delete
  BadRow.Delete

  WS.Columns("N:N").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove                           ' ** Combine two steps

  NumRows = WS.UsedRange.Rows.Count        ' ** Count number of rows used in raw data

  WS.Range(WS.Rows(5),WS.Rows(5 + NumRows)).Insert                   ' ** Should work better

  WS.Range("A1",WS.Cells(NumRows,21)).Copy destination:=Range(Rows(4),Rows(4+NumRows))              ' Copy and paste ???

  Close_File (WB)                                     ' Clean up, free memory
  Set WB = Nothing
  Set WS = Nothing
  Set BadCols = Nothing
  Set BadRow = Nothing

End Sub

Answer 2

Replace this:

Rows("5:" + (5 + NumRows)).Select 
ActiveCell.EntireRow.Insert
WS.Range("A1:U" + NumRows).Select.Copy
Rows("4:" + (4 + NumRows)).Select.Paste

With this:

Rows("5:" & (5 + NumRows)).EntireRow.Insert
WS.Range("A1:U" + NumRows).Copy
WS.Paste Destination:=Range("D1")

Let me know how that works.