VBA Excel copy cell range to external workbook

Question

Previously I was dealing with the query below:

VBA Excel taking control of the cells on the another workbook

regarding copying the data from my active workbook to some external workbook.

Now I would like to do an analog copy, but not as a single cell, but as a range.

My code looks like this:

  Sub Splicing()

  Dim PoP As String, SN As String
  Dim Fibre As Range

  Dim newbook As Workbook
  Dim fs As Worksheet

  Set fw = Sheets("Frontsheet")
  'name = fw.Range("AA9")
  name = fw.Range("D18")
  name2 = fw.Range("D38")
  custom_name = name & " - Splicing As-build_v." & name2 & ".0"

  PoP = ActiveWorkbook.Sheets("Frontsheet").Range("D10").Value
  SN = ActiveWorkbook.Sheets("Frontsheet").Range("D12").Value

  Fibre = ActiveWorkbook.Sheets("Fibre Drop Release Sheet").Range("A2:H20").Value

  Path = ActiveWorkbook.Path & "\Splicing Template_V1.0.xlsm"
  Set newbook = Workbooks.Open(Path)

  newbook.Sheets("Frontsheet").Cells(10, 4).Value = PoP
  newbook.Sheets("Frontsheet").Cells(12, 4).Value = SN


  newbook.Sheets("Fibre drop release sheet").Cells(3, 2).Value = Fibre


  Path = ActiveWorkbook.Path & "\" & custom_name & ".xlsm"

  'Application.DisplayAlerts = False
  ActiveWorkbook.SaveAs filename:=Path, FileFormat:=52


  End Sub

The debugger shows line Fibre = and says: Object variable or With block variable not set.

I tried also the other solution presented here:

https://www.extendoffice.com/documents/excel/3084-excel-copy-range-to-new-workbook.html

and here

VBA Copying Excel Range to Different Workbook

and here

http://debugvba.com/copy-data-from-one-sheet-to-another-workbook-vba-excel-debugvba/

but these codes don't match to my example

score 2 · Accepted Answer · answered Apr 28 '20 at 15:52

Two issues:

Dim Fibre As Range
...
Fibre = ActiveWorkbook.Sheets("Fibre Drop Release Sheet").Range("A2:H20").Value

Change to

Dim Fibre As Variant

Then if you're using value transfer, the destination and source should be the same size:

newbook.Sheets("Fibre drop release sheet").Range("B3:I21").Value = Fibre

If you don't want to hard-code the address B3:I21, then Resize is useful to get the destination to the same size as the source.

newbook.Sheets("Fibre drop release sheet").Cells(3, 2).Resize(Ubound(Fibre, 1), Ubound(Fibre, 2)).Value = Fibre

VBA Excel copy cell range to external workbook

1 Answers1