Copy two separate ranges from another workbook

kevinbradley57 · #1 06-02-2018, 08:23 PM

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Copy data as well as formatting from one workbook to another.	LearnerExcel	Excel	1	03-10-2018 01:35 PM
Copy from one workbook to another	MartinExcel11	Excel Programming	1	03-28-2017 09:49 AM
Take String of numbers, expand ranges, sort, then compress back into ranges	AustinBrister	Word VBA	19	08-22-2016 05:18 PM
How to make a shared workbook and allow user to edit ranges	prdecina	Excel	1	07-25-2016 11:42 PM
Storing and retreiving text from a separate workbook	Benjamin92	Excel Programming	4	03-08-2015 04:20 PM