0

I have a source workbook and a destination workbook. The source book has a range (D13:F293) that contains data values, as well as blank cells. The destination book contains the same range and various cells contain formulas.

I want to import data from the source book to the destination book, but ONLY cells that contain a value. Furthermore, if the destination book/cell contains a formula, I want to KEEP the formula in the cell.

My options are:

  1. Scan the source for blank cells and only import cells with data.
  2. Scan the destination for formulas, and if a formulas exists, don't import data to that cell.

I don't know how to do this. I am very new to VBA and don't fully understand the syntax. I have tried 3 times with code that either replaces formulas with blank cells, or gives an error.

This code copies blank cells to destination book:

Sub TransferData()
   If Workbooks.Count > 1 Then
      Workbooks(2).Sheets("HELOC").Range("D13:F293").Copy
      Workbooks(1).Sheets("HELOC").Range("D13:F293").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      Workbooks(2).Close savechanges:=False
   Else
      MsgBox "The data hasn't been transferred.", vbExclamation, "Error"
   End If
End Sub

This code also copies blank cells to destination book:

Sub TransferData()
   If Workbooks.Count > 1 Then
      For Each cl In ActiveSheet.UsedRange
         If cl.HasFormula() = True Then
            Workbooks(1).Sheets("HELOC").Range("D13:F293") = Workbooks(1).Sheets("HELOC").Range("D13:F293")
         Else
            Workbooks(1).Sheets("HELOC").Range("D13:F293").Value = Workbooks(2).Sheets("HELOC").Range("D13:F293").Value
         End If
      Next cl
      Workbooks(2).Close savechanges:=False
   Else
      MsgBox "The data hasn’t been transferred.", vbExclamation, "Error"
   End If
End Sub

This code (modified from here) results in:

Runtime error 91 Object variable or With block variable not set on line:

"If mySourceBook.Cells(i, 1).Value <> "" Then"

Sub TransferData()
   Dim mySourceBook As Worksheet, myDestinationBook As Worksheet, myBook As Workbook
   Set myBook = Excel.ActiveWorkbook
   Set mySource = myBook.Sheets("HELOC")
   Set myImportData = Excel.ActiveWorkbook.Sheets("HELOC")

   Dim i As Integer, j As Integer 'Define a couple integer variables for counting

   j = 13
   For i = 13 To 293
      If mySourceBook.Cells(i, 1).Value <> "" Then
         myDestinationBook.Cells(j, 2).Value = mySourceBook.Cells(i, 1).Value
         j = j + 1
      End If
   Next i 'This triggers the end of the loop and moves on to the next value of "i".

   Workbooks(2).Close savechanges:=False

   MsgBox "The data hasn’t been transferred.", vbExclamation, "Error"

End Sub

I appreciate any advice given. But please, explain it to me like I'm a child as I don't fully comprehend VBA.

Louis
  • 3,592
  • 2
  • 10
  • 18
mattman1968
  • 1
  • 2
  • 1
    in regards to the error you are receiving: Avoid using ActiveWorkbook and specifically name the workbook. Also, i dont see that you defined `myDestinationBook` anywhere. – Zack E Apr 03 '19 at 18:39
  • 1
    What is `mySourceBook` there? You don't it to anything anywhere, hence the `Object variable not set` error. What are you trying to accomplish, only copying cells with data? If you have 200 cells and only 5 of them contain data, how should it be pasted on your destination sheet? – dwirony Apr 03 '19 at 18:44

2 Answers2

0

You can do it like this:

Sub MoveIt()

    Dim rngSrc As Range, rngDest As Range, i As Long, cS As Range, cD As Range

    Set rngSrc = Workbooks("Source.xlsx").Worksheets("Sheet1").Range("D13:F293")
    Set rngDest = Workbooks("Target.xlsx").Worksheets("Sheet4").Range("D13:F293")

    For i = 1 To rngSrc.Cells.Count
        Set cS = rngSrc.Cells(i)
        Set cD = rngDest.Cells(i)
        If Len(cS.Value) > 0 And Not cD.HasFormula Then
            cS.Copy cD
            'or
            cD.Value = cS.Value
        End If
    Next i

End Sub
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Tim, your code worked with a minor tweak! It leaves formulas in the destination cells. Now that it does exactly what I thought I wanted, I find it is not what I really need. There are instances when the destination formula may need to be over-written with a data value from the source. Is it possible to scan the source for blank cells instead and only paste cells with a value to the destination range? – mattman1968 Apr 03 '19 at 19:37
  • It already only pastes non-blank cells. If you want it to overwrite formulas then remove the `And Not cD.HasFormula` check – Tim Williams Apr 03 '19 at 20:36
0

I got it to work how I want! Thank you very much for your help, Tim!

Now to figure out how to import and keep the format. 

Sub TransferData()

Dim rngSrc As Range, rngDest As Range, i As Long, cS As Range, cD As Range

Set rngSrc = Workbooks("Exported AMP Data.xlsx").Worksheets("HELOC").Range("D13:F293")
Set rngDest = Workbooks(1).Worksheets("HELOC").Range("D13:F293")

For i = 1 To rngSrc.Cells.Count
    Set cS = rngSrc.Cells(i)
    Set cD = rngDest.Cells(i)
    If Len(cS.Value) > 0 Then
        cS.Copy cD
        'or
        cD.Value = cD.Value
    End If
Next i
End If
End Sub
mattman1968
  • 1
  • 2