Copy by setting variable ranges equal between workbooks. Range of Object worksheet failed

Question

I'm attempting to copy and paste values from one workbook into a master workbook by setting the ranges (which have varying numbers of rows but static columns) equal to one another. whWb.whexpoSh is the master sheet, so values need to be added to the first blank row.

I'm having trouble with the last line of code (***). I keep getting

method range of object _worksheet failed

I got this method to work but only when I used a fixed range of cell names with expoSh in the last line.

In the example I'm testing, F=2, A=6, B=7 and lrowExpo = 7. So the range areas match up.

Sub Export_to_Master_Sheet()

Dim lRowWhExpo As Integer, lRowWhLoss As Integer, lrowExpo As Integer, lrowLoss As Integer
Dim expWb As Workbook, whWb As Workbook
Dim covWs As Worksheet, expoSh As Worksheet, lossSh As Worksheet, whexpoSh As Worksheet, whlossSh As Worksheet
Dim A As Integer, B As Integer, C As Integer, D As Integer, F As Integer
 Application.ScreenUpdating = False

Set expWb = ThisWorkbook
Set covWs = expWb.Worksheets("Cover")
Set expoSh = expWb.Worksheets("Exposure Data Capture")
Set lossSh = expWb.Worksheets("Loss Data Capture")

F = covWs.Range("H2").Value

Application.Workbooks.Open FileName:="G:\GST\Documents\Exposure and Loss Data.xlsx"

Set whWb = ActiveWorkbook
Set whexpoSh = whWb.Worksheets("Exposure Data Capture")
Set whlossSh = whWb.Worksheets("Loss Data Capture")

lrowExpo = expoSh.Columns("B").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
lRowWhExpo = whexpoSh.Cells(Rows.Count, 2).End(xlUp).Row

A = lRowWhExpo + 1
B = lRowWhExpo + F

***whexpoSh.Range(Cells(A, 1), Cells(B, 111)).Value = expoSh.Range(Cells(6, 1), Cells(lrowExpo, 111)).Value

End Sub

score 1 · Answer 1 · answered Mar 06 '18 at 01:01

This seems to work with my limited testing:

Replace this line:

***whexpoSh.Range(Cells(A, 1), Cells(B, 111)).Value = expoSh.Range(Cells(6, 1), Cells(lrowExpo, 111)).Value

With this:

Dim toRng As Range, fromRng As Range
With whexpoSh
    Set toRng = .Range(.Cells(A, 1), .Cells(B, 111))
End With
With expoSh
    Set fromRng = .Range(.Cells(6, 1), .Cells(lrowExpo, 111))
End With

toRng.Value = fromRng.Value

I recommend that you declare your Range objects. It can make debugging a little easier.

Copy by setting variable ranges equal between workbooks. Range of Object worksheet failed

1 Answers1