title: Combining Multiple sheets from one work book into a single sheet on another work book with a macro
body: <p>I am just attempting this to help speed up cost comparisons, except my programming skills are laughable at this point. This is what I have so far...</p>
  
  <pre><code>Sub CommandButton1_Click()
  
  'Last cell in column
  Dim WS As Worksheet
  Dim LastCell As Range
  Dim LastCellRowNumber As Long
  
  Set WS = Worksheets("Asphalt")
  With WS
      Set LastCell = .Cells(.Rows.Count, "C").End(xlUp)
      LastCellRowNumber = LastCell.Row + 1
  End With
  
  Dim wb As Workbook, wb2 As Workbook
  Dim vFile As Variant
  
  'Set source workbook
  Set wb = ActiveWorkbook
  
  'Open the target workbook
  vFile = Application.GetOpenFilename("Excel-files,*.xlsx", _
      1, "Select One File To Open", , False)
  
  'if the user didn't select a file, exit sub
  If TypeName(vFile) = "Boolean" Then Exit Sub
  Workbooks.Open vFile
  
  'Set selectedworkbook
  Set wb2 = ActiveWorkbook
  
  'Select cells to copy
  wb2.Worksheets("Asphalt").Range("J3:R3").Copy
  
  'Go back to original workbook you want to paste into
  wb.Activate
  
  'Paste starting at the last empty row
  wb.Worksheets("Asphalt").Range("C" &amp; LastCellRowNumber).Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
  
  'Close and save the workbook you copied from
  wb2.Save
  wb2.Close
  
  End Sub
  </code></pre>
  
  <p>Now first thing I can't seem to open another .xls workbook for whatever reason. Secondly I get a message saying my subscript is out of range.</p>
  
  <p>Ultimately what I want to happen is to hit a button, open the work book I want to have as my initial costs and it will populate not only pricing but also line items and quantities. Then hit another button to open another workbook and import the costs from that workbook but only if the line items match. </p>
  
  <p>Now I know this is a tall tall order and I will continue on my trek to get my goal of having this all powerful spreadsheet, but I would greatly appreciate any and all help that I can get. </p>
  
  <p>Here is a picture of roughly what I would like the final project to look like <a href="http://imgur.com/3EnYEcP" rel="nofollow">http://imgur.com/3EnYEcP</a> just minus manually inputting all the numbers in like a caveman ;) </p>