Problem with my macro and it deletes formulas.

[ASadStudent]

Hello everyone, I am noticing a problem with my macro, but I can't find where I broke it.
The reason it broke is because I was trying to fix a problem where my macro deletes the =SOM formulas that were in the same column.

Here is an explanation of what my macro does:
My macro needs to copy paste data from 1 excel document sheet to another. The document where the data is coming from is called “omzet” and the document where the data needs to go to is called “maandafsluiting”. The sheets in both documents are just called Sheet1.

The data needs to be copied based on the product name that is in front of the document on the B Column. If the product names on both files match then it needs to copy the amount that is in the N column in the “omzet” document to the F Column in the “Maandafsluiting” document.

This is what my macro looks like right now:

Sub CopyPaste()
    Dim omzet As Worksheet: Set omzet = Workbooks.Item("Omzet").Sheets("Sheet1")
    Dim Maandafsluiting As Worksheet: Set Maandafsluiting = Workbooks.Item("Maandafsluiting").Sheets(1)
   
    Dim data As Variant, lr As Long, d As Object, key As String, rw As Long
  
    lr = omzet.Cells(Rows.Count, 1).End(3).Row
    data = omzet.Cells(1, 1).Resize(lr, 14).Value
   
    Set d = CreateObject("Scripting.Dictionary")
   
    For rw = LBound(data) To UBound(data)
        If data(rw, 14) <> 0 Then
            key = data(rw, 2)
            If Not d.exists(key) Then
                d(key) = data(rw, 14)
            End If
        End If
    Next rw
   
    lr = Maandafsluiting.Cells(Rows.Count, 1).End(3).Row
    data = Maandafsluiting.Cells(1, 1).Resize(lr, 6).Value
      
    For rw = LBound(data) To UBound(data)
        key = data(rw, 2)
        If d.exists(key) Then
            data(rw, 6) = d(key)
        End If
    Next rw
  
    Maandafsluiting.Cells(1, 7).Resize(UBound(data)).Value = Application.Index(data, 0, 7)
End Sub

Here is what the excel document looks like:

Omzet:

Maandafsluiting:

Thanks a lot for helping me solve my problem.

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details. I have added the tags for you this time.

 

[HaHoBe]

Hi ASadStudent,

it should be codeline

    data = Maandafsluiting.Cells(1, 1).Resize(lr, 7).Value

when writing that part back into the sheet. You should have mentioned the formulas in your other thread.

BTW: does 365 really work with

Set omzet = Workbooks.Item("Omzet").Sheets("Sheet1")

without giving the extension of the workbook like

Set omzet = Workbooks("Omzet.xlsx").Worksheets("Sheet1")

Ciao,
Holger

 

[Peter_SSs]

BTW: does 365 really work with

Set omzet = Workbooks.Item("Omzet").Sheets("Sheet1")

without giving the extension of the workbook like

Set omzet = Workbooks("Omzet.xlsx").Worksheets("Sheet1")

Not related to 365. The code will work as originally written if the user has the Windows setting to not show file extensions.

 

[HaHoBe]

Thanks for the information, Peter

 

[Peter_SSs]

Thanks for the information, Peter

Cheers.

 

[Alex Blakenburg]

Try changing the last 2 lines that use .Value to .Formula, highlighted in the code below:

    lr = Maandafsluiting.Cells(Rows.Count, 2).End(3).Row
    data = Maandafsluiting.Cells(1, 1).Resize(lr, 6).Formula
      
    For rw = LBound(data) To UBound(data)
        key = data(rw, 2)
        If d.exists(key) Then
            data(rw, 6) = d(key)
        End If
    Next rw
  
    Maandafsluiting.Cells(1, 6).Resize(UBound(data)).Formula= Application.Index(data, 0, 6)

 

[ASadStudent]

I solved my problem in my other thread

 

[Fluff]

Alex solved your problem & it's directly above your post.
I have therefore changed the marked solution.