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

Pecoflyer · #2 06-02-2018, 11:27 PM

Please wrap your code with code tags. Thank you

NoSparks · #3 06-03-2018, 08:43 AM

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

Code:

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

more difficult to understand is to use an array

Code:

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

Code:

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

kevinbradley57 · #4 06-04-2018, 12:09 PM

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 · #5 06-05-2018, 05:55 AM

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 · #6 07-24-2018, 08:38 PM

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 )"

Code:

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

NoSparks · #7 07-25-2018, 07:10 AM

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

kevinbradley57 · #8 07-25-2018, 09:16 AM

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:

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 · #9 07-25-2018, 09:29 AM

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

kevinbradley57 · #10 07-25-2018, 10:11 AM

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 · #11 07-25-2018, 12:31 PM

Code:

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 · #12 07-25-2018, 01:51 PM

Perfect -- THANK YOU!

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