Copy two separate ranges from another workbook

[kevinbradley57]

I have a source file called "SearchResultsCompleted m.dd.yy" where m.dd.yy is the current date. It is a system export with one worksheet, named "Archer Search Report". It has data in columns A-L and a header row. The number of rows varies. I have a destination file "Weekly Plan Status m.dd.yy" with worksheet "Completed". I need to copy from the source file the data (not the header row) from columns A-G and H-L to the destination file in columns A-G and I-M. I want Column H of the destination file to have the formula =F2-G2, =F3-G3, etc.

I know just enough VBA to get frustrated. Here's the code I cobbled together from what I could find on various VBA websites. Any help would be appreciated.

Sub GetCompleted()
 
Application.ScreenUpdating = False
Dim Lastrow As Long
Dim range1 As Range, range2 As Range
Set range1 = Range("A2:G" & Range("G" & Rows.Count).End(xlUp).Row)
Set range2 = Range("H2:K" & Range("H" & Rows.Count).End(xlUp).Row)
 
Workbooks("Weekly Plan Status " & Format(Date, "m.d.yy") & ".xlsm").Worksheets("Completed").range1.Value = _
    Workbooks("SearchResultsCompleted " & _
    Format(Date, "m.d.yy") & ".xls").Worksheets("Archer Search Report").range1.Value

Range("$H$2").Formula = xxx

Lastrow = Range("H" & Rows.Count).End(xlUp).Row
Range("H2").FormulaR1C1 = xxx
Range("H2").AutoFill Destination:=Range(“H3:H" & Lastrow)
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True
 
End Sub

[Pecoflyer]

Please wrap your code with code tags. Thank you

[NoSparks]

Couple of options...
easiest to understand is copy and paste as values

range1.Copy
  yourdestinationsheet.Range("A2").PasteSpecial (xlPasteValues)

more difficult to understand is to use an array

arr = range1.value
  yourdestinationsheet.Range("A2").Resize(UBound(arr, 1), UBound(arr, 2)) = arr

If you put the formula that's wanted into H2 and make it the active cell,
then go to the Immediate window, Ctrl + g, from the VBE, type in
? activecell.formulaR1C1
hit enter and you will see what Excel sees as being the formula in that cell
include H2 in the autofill range

  Range("H2").FormulaR1C1 = "=xxx"
  Range("H2").AutoFill Destination:=Range("H2:H" & lastrow)

[kevinbradley57]

Thanks, NoSparks. Since the code lives in my destination file, I'm assuming I must define the range to be copied, including the full path, workbook, and worksheet, yes?

[NoSparks]

If the source workbook is already open the full path part wouldn't be necessary, otherwise it would be in order to open the source via the macro.

[kevinbradley57]

NoSparks -- When I enter your suggested line of code (below), the colon in "H2:H" is highlighted and I get the following compile error: "Expected: list separator or )"

Range("H2").AutoFill Destination:=Range("H2:H" & lastrow)

[NoSparks]

Are you sure that's what you've entered ?
Check your double quotes.

[kevinbradley57]

Thanks, NoSparks. The first double quotes were slanted -- no idea how I did that. However, now I get this run-time error: Method 'Range' of object' _Global' failed.

Here's the code:

Sub Difference()
Lastrow = Range("H" & Rows.Count).End(xlUp).Row
Range("H2").FormulaR1C1 = "RC[-2]-RC[-3]"
Range("H2").AutoFill Destination:=Range("H2:H") & Lastrow
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True
End Sub

[NoSparks]

Use what you show in post #6 for the destination.

[kevinbradley57]

That did it -- thank you. I'm still not able to copy selected ranges (part of my original need as shown in post #1), but I'm learning a lot about what does NOT work.

[NoSparks]

Sub GetCompleted()
 
    Dim lastrow As Long
    Dim range1 As Range, range2 As Range
    Dim src As Workbook, dest As Workbook

Set src = Workbooks("SearchResultsCompleted " & Format(Date, "m.d.yy") & ".xls")
Set dest = Workbooks("Weekly Plan Status " & Format(Date, "m.d.yy") & ".xlsm")

With src.Sheets("Archer Search Report")
    lastrow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Set range1 = .Range("A2:G" & lastrow)
    Set range2 = .Range("H2:L" & lastrow)
End With

With dest.Sheets("Completed")
    .Range("A2:G" & lastrow).Value = range1.Value
    .Range("I2:M" & lastrow).Value = range2.Value
    .Range("H2").FormulaR1C1 = "=RC[-2]-RC[-3]"
    .Range("H2").AutoFill Destination:=Range("H2:H" & lastrow)
End With
 
End Sub

[kevinbradley57]

Perfect -- THANK YOU!