Paste values not working in a table

[davidepstein22]

Hi all,

I don't know if I found a MSFT bug, but let me explain my dilemma and I would appreciate your comments/suggestion

1. My spreadsheet reads about 25-sales files using Power Query and places the data in a new sheet. This works correctly
2. I summarize #1 using the unique function to calculate the total units sold by model number. This works correctly.
3. My VBA reads the new month's data and enters the data into my Sales table (a formal Excel table). This works correctly.
4. The VBA code attempts to convert the formulas into values in the Units Sold column for the new rows (cells), but it always returns "0" (over writing the actual values). I expected a simple copy | paste values would work, but it does not. I even tried copying the values to a new workbook and then pasting them back to the desired sheet, but it did not work, nor many other approaches.

I am convinced there is a bug or table behavior is causing to occur, but I can't identify the cause. I would appreciate your help.


Thanks,
Dave

 

[igold]

Without seeing your data (fictitious or real) and without seeing your code, it is really hard for anyone to know what is going on. It would also be helpful if you would complete your profile so we know what your OS and Excel versions are...

 

[davidepstein22]

Hi all,

I don't know if I found a MSFT bug, but let me explain my dilemma and I would appreciate your comments/suggestion

1. My spreadsheet reads about 25-sales files using Power Query and places the data in a new sheet. This works correctly
2. I summarize #1 using the unique function to calculate the total units sold by model number. This works correctly.
3. My VBA reads the new month's data and enters the data into my Sales table (a formal Excel table). This works correctly.
4. The VBA code attempts to convert the formulas into values in the Units Sold column for the new rows (cells), but it always returns "0" (over writing the actual values). I expected a simple copy | paste values would work, but it does not. I even tried copying the values to a new workbook and then pasting them back to the desired sheet, but it did not work, nor many other approaches.

I am convinced there is a bug or table behavior is causing to occur, but I can't identify the cause. I would appreciate your help.


Thanks,
Dave

Hi all,

I don't know if I found a MSFT bug, but let me explain my dilemma and I would appreciate your comments/suggestion

1. My spreadsheet reads about 25-sales files using Power Query and places the data in a new sheet. This works correctly
2. I summarize #1 using the unique function to calculate the total units sold by model number. This works correctly.
3. My VBA reads the new month's data and enters the data into my Sales table (a formal Excel table). This works correctly.
4. The VBA code attempts to convert the formulas into values in the Units Sold column for the new rows (cells), but it always returns "0" (over writing the actual values). I expected a simple copy | paste values would work, but it does not. I even tried copying the values to a new workbook and then pasting them back to the desired sheet, but it did not work, nor many other approaches.

I am convinced there is a bug or table behavior is causing to occur, but I can't identify the cause. I would appreciate your help.


Thanks,
Dave

Hi all,

Thanks for the suggestions, but it is still not working as expected. I am sharing the following read-only link to a public version of my spreadsheet. Please note that in this version, the paste values simply does not paste values, but does retrain the formula in the Sales' sheet Units Sold column. To execute the code, go to the Sales sheet and click on the button in J1 ("Update Sales").

Public version.xlsm

Microsoft Excel Workbook

1drv.ms

In my version, the copy | paste values returns a zero in each new row. When I created the public version of the s/s the outcome changed.

I appreciate the community's help!!

Dave

 

[MARK858]

What range appears in your Immediate window when you run the code below?

Sub UpdateSalesSheet()

'This routine copies products from the Products sheet to the Sales sheet and then read the new month's sales files and enters the Unit Sold for each model #.

Application.ScreenUpdating = False
Dim salesmonthenddate As String
Dim cel, rng As Range
Dim found, result As Integer
Dim tblsalesUnitsSoldcolumn, tblSalesLastRow As Integer
Dim fso As Object
Dim filedate As Date


'ReplaceUnitSoldFormulas

    'get last row of Sales Table before new month's data is copied to it
    tblSalesLastRow = ActiveSheet.ListObjects("Sales").DataBodyRange.Rows.Count + 1
    tblsalesUnitsSoldcolumn = 6
                     
    'add sumif to the new records in the Sales sheet to retrieve the Units Sold
    Sheets("Sales").Select
    Set rng = Range(Cells(tblSalesLastRow + 1, tblsalesUnitsSoldcolumn), Cells(ActiveSheet.ListObjects("Sales").DataBodyRange.Rows.Count + 1, tblsalesUnitsSoldcolumn))
    Debug.Print rng.Address
End Sub

Just testing if what I see is the same as you.

 

[MARK858]

When I run your code with a break on the paste values line your formula evaluates to 0 (I have only taken it out of copy/paste mode), is there something else other than clicking the button to make the formula reference any other data we are supposed to do?

When I take the break off it pastes as values for F189:F190 i.e. it is a constant of 0 obviously.
Is the formula supposed to reference a cell in column C, one row below that is blank?

 

[davidepstein22]

What range appears in your Immediate window when you run the code below?

Sub UpdateSalesSheet()

'This routine copies products from the Products sheet to the Sales sheet and then read the new month's sales files and enters the Unit Sold for each model #.

Application.ScreenUpdating = False
Dim salesmonthenddate As String
Dim cel, rng As Range
Dim found, result As Integer
Dim tblsalesUnitsSoldcolumn, tblSalesLastRow As Integer
Dim fso As Object
Dim filedate As Date


'ReplaceUnitSoldFormulas

    'get last row of Sales Table before new month's data is copied to it
    tblSalesLastRow = ActiveSheet.ListObjects("Sales").DataBodyRange.Rows.Count + 1
    tblsalesUnitsSoldcolumn = 6
                    
    'add sumif to the new records in the Sales sheet to retrieve the Units Sold
    Sheets("Sales").Select
    Set rng = Range(Cells(tblSalesLastRow + 1, tblsalesUnitsSoldcolumn), Cells(ActiveSheet.ListObjects("Sales").DataBodyRange.Rows.Count + 1, tblsalesUnitsSoldcolumn))
    Debug.Print rng.Address
End Sub

Just testing if what I see is the same as you.

 

[davidepstein22]

Hi Mark,

I did not specify try your code, but I will... Here is the code that I use to write new rows to my table. The code works perfectly - the formula retrieves the correct value from the Sumif.
If dd <> "" And dd > 0 Then 'write row on Sales sheet
Sheets("Sales").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = productname
ActiveCell.Offset(0, 1) = desc
ActiveCell.Offset(0, 2) = modelnum
ActiveCell.Offset(0, 3) = clr
ActiveCell.Offset(0, 4) = salesmonthenddate
ActiveCell.Offset(0, 5).Formula = "=SUMIF('Last Months Sales'!$H$2:$H$200,[@[Model '#]] ,'Last Months Sales'!$I$2:$I$200)"
End If

Here is the code that works in debug mode, but not when I let the macro run normally. Excel is losing the value returned in the formula above and return a zero for each new row. As you can see, I removed the rng (range) code and simply use select all of the rows in the Units Sold column.

Sheets("Sales").Select
ActiveSheet.ListObjects("Sales").ListColumns("Units Sold").DataBodyRange.Select
Selection.Copy
Sheets("Sales").ListObjects("Sales").DataBodyRange.Cells(1, tblsalesUnitsSoldcolumn).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

I can try your suggestion, but the code does work in debug mode, so it should work when I run the code normally.

Thanks,
Dave

 

[MARK858]

Here is the code that works in debug mode, but not when I let the macro run normally.

That sounds like it is being taken out of copypastemode when you do the selection.

Have you tried the below to see what happens?

    With Sheets("Sales")
        .ListObjects("Sales").ListColumns("Units Sold").DataBodyRange.Copy
        .ListObjects("Sales").DataBodyRange.Cells(1, tblsalesUnitsSoldcolumn).PasteSpecial Paste:=xlPasteValues
    End With

If that doesn't work can you also try

    With Sheets("Sales").ListObjects("Sales").ListColumns("Units Sold").DataBodyRange
        Sheets("Sales").ListObjects("Sales").DataBodyRange.Cells(1, tblsalesUnitsSoldcolumn).Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With

 

[davidepstein22]

That sounds like it is being taken out of copypastemode when you do the selection.

Have you tried the below to see what happens?

    With Sheets("Sales")
        .ListObjects("Sales").ListColumns("Units Sold").DataBodyRange.Copy
        .ListObjects("Sales").DataBodyRange.Cells(1, tblsalesUnitsSoldcolumn).PasteSpecial Paste:=xlPasteValues
    End With

If that doesn't work can you also try

    With Sheets("Sales").ListObjects("Sales").ListColumns("Units Sold").DataBodyRange
        Sheets("Sales").ListObjects("Sales").DataBodyRange.Cells(1, tblsalesUnitsSoldcolumn).Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With

 

[davidepstein22]

Hi Mark,

I tried both approaches, but still receiving a zero for all of the new rows. I have no idea what is happening. If you are able, I can share my screen with you to demo it. Uploading a sample file is very difficult. If we cannot solve it in 10 minutes then we can end the call. I realize that your time is really valuable and I will respect it.

Thanks,
Dave