While writing files from another workbook, keep empty cells empty!

**nivoe** · 11-09-2012, 11:35 AM

Hello everyone

First off the code:

Sub Get_Data()
    Workbooks.Open Filename:="C:\work\A.xlsx"
    Windows("Main.xlsm").Activate
    Sheets("Sheet1").Select
    Range("A1:ET2000").Select
    Selection.FormulaR1C1 = "=[A.xlsx]Sheet1!R1C1:R2000C150"
End Sub

With this code, I write all Data from Sheet1 in A.xlsx into Sheet1 in Main.xlsx.
It is working perfectly with one exception.

If, e.g. in A.xlsx there are empty cells, the cells in Main.xlsx show zeros (=0).

How could I make it that empty cells in A show as empty cells in Main?

Help is much appreciated
Cheers

**Norie** · 11-09-2012, 11:48 AM

Do you want to have formulas linked to A.xlsx?

Or would the values be OK?

Sub Get_Data()
Dim wbA As Workbook

    Set wbA = Workbooks.Open Filename:="C:\work\A.xlsx"

    wbA.Worksheets("Sheet1").Range("A1:ET2000).Copy

    ThisWorkbook.Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValues

    wbA.Close SaveChanges:=False
End Sub

**nivoe** · 11-09-2012, 12:06 PM

All I want is to get the values (but to keep the conditional formatting in Main.xlsx)

If your above code is like a manual copy and paste (which overwrites cond. form.) I cannot use it.

Cheers

btw. you above line (wbA.Worksheets("Sheet1").Range("A1:ET2000").Copy
) throws an exception:

Run Time Error 91, Object Variable or With Block Variable not set

**Norie** · 11-09-2012, 12:17 PM

Why can't you use what I posted?

All it does is copy and paste values, it doesn't overwrite any formatting.

there were a couple of typos.

Sub Get_Data()
Dim wbA As Workbook

    Set wbA = Workbooks.Open(Filename:="C:\A.xlsx")

    wbA.Worksheets("Sheet1").Range("A1:ET2000").Copy

    ThisWorkbook.Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValues
    
    Application.CutCopyMode = False
    
    wbA.Close SaveChanges:=False
End Sub