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.
Code:
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