Copy only Value not Formula to new Cell location after ActiveX Macro button pushed.

VinnyDoesntKnowExcelCode · Sep 9, 2024

So let's start with I have extremely limited coding knowledge. This is in fact my first attempt at it after watching some you tube videos, downloading a basic starter VBA sheet and tweaking it for my own needs. I am attempting to record daily inventory numbers based upon 2 separate propane site locations. All seemed to go well, even the ActiveX button and I get all results I want EXCEPT the fact that it copies the formulas over instead of the values. This then throws off all the numbers. Everything goes to the correct cell that I have designated for copy. I have tried playing with the paste special command but I could never get it to work. Please see below for code and mini sheet. Thank you.
Private Sub CommandButton1_Click()

'Create and set variables for the Import & Master List worksheets
Dim Import As Worksheet, MList As Worksheet

Set Import = Sheet1
Set MList = Sheet2

'Create and set variables for each cell in the Import worksheet
Dim Dte As Range, Bulk2100 As Range, Disp2100 As Range, DispMeter As Range
Dim Tr660 As Range, Tr736 As Range, Tr751 As Range, Tr753 As Range
Dim Bulk2109 As Range, Disp5002 As Range, Tr661 As Range, Tr739 As Range
Dim Bulk2100P As Range, Disp2100P As Range
Dim Tr660P As Range, Tr736P As Range, Tr751P As Range, Tr753P As Range
Dim Bulk2109P As Range, Disp5002P As Range, Tr661P As Range, Tr739P As Range

Set Dte = Import.Range("F6")
Set Bulk2100P = Import.Range("D9")
Set Disp2100P = Import.Range("D10")
Set DispMeter = Import.Range("D11")
Set Tr660P = Import.Range("D12")
Set Tr736P = Import.Range("D13")
Set Tr751P = Import.Range("D14")
Set Tr753P = Import.Range("D15")
Set Bulk2109P = Import.Range("G9")
Set Disp5002P = Import.Range("G10")
Set Tr661P = Import.Range("G11")
Set Tr739P = Import.Range("G12")
Set Bulk2100 = Import.Range("E9")
Set Disp2100 = Import.Range("E10")
Set Tr660 = Import.Range("E12")
Set Tr736 = Import.Range("E13")
Set Tr751 = Import.Range("E14")
Set Tr753 = Import.Range("E15")
Set Bulk2109 = Import.Range("H9")
Set Disp5002 = Import.Range("H10")
Set Tr661 = Import.Range("H11")
Set Tr739 = Import.Range("H12")

'Create a variable for the paste cell in the Master List worksheet
Dim DestCell As Range

If MList.Range("A2") = "" Then 'If A2 is empty
Set DestCell = MList.Range("A2") '...then destination cell is A2
Else
Set DestCell = MList.Range("A1").End(xlDown).Offset(1, 0) '...otherwise the next empty row
End If

'If no "Date has been entered, exit macro
If Dte = "" Then
MsgBox "PLEASE ENTER A DATE"
Exit Sub
End If

'Copy and paste data from the Import worksheet to the Master List worksheet
Dte.Copy DestCell
Bulk2100.Copy DestCell.Offset(0, 1)
Bulk2100.PasteSpecial xlPasteValues
Bulk2100P.Copy DestCell.Offset(0, 2)
Disp2100.Copy DestCell.Offset(0, 3)
Disp2100P.Copy DestCell.Offset(0, 4)
DispMeter.Copy DestCell.Offset(0, 5)
Tr660.Copy DestCell.Offset(0, 7)
Tr660P.Copy DestCell.Offset(0, 8)
Tr736.Copy DestCell.Offset(0, 9)
Tr736P.Copy DestCell.Offset(0, 10)
Tr751.Copy DestCell.Offset(0, 11)
Tr751P.Copy DestCell.Offset(0, 12)
Tr753.Copy DestCell.Offset(0, 13)
Tr753P.Copy DestCell.Offset(0, 14)
Bulk2109.Copy DestCell.Offset(0, 15)
Bulk2109P.Copy DestCell.Offset(0, 16)
Disp5002.Copy DestCell.Offset(0, 17)
Disp5002P.Copy DestCell.Offset(0, 18)
Tr661.Copy DestCell.Offset(0, 19)
Tr661P.Copy DestCell.Offset(0, 20)
Tr739.Copy DestCell.Offset(0, 21)
Tr739P.Copy DestCell.Offset(0, 22)

'Clear the contents in the Import worksheet
Dte.ClearContents
Bulk2100P.ClearContents
Disp2100P.ClearContents
DispMeter.ClearContents
Tr660P.ClearContents
Tr736P.ClearContents
Tr751P.ClearContents
Tr753P.ClearContents
Bulk2109P.ClearContents
Disp5002P.ClearContents
Tr661P.ClearContents
Tr739P.ClearContents

End Sub

InventoryMacroAttempt.xlsm

B

C

D

E

F

G

H

5

GRANTS PASS/SUTHERLIN DAILY INVENTORY

6

Date

9/5/2024

7

G.P.

Sutherlin

8

%

Gallons

%

Gallons

9

Bulk 2100-1

40

12000

Bulk 2109-1

0

10

Disp 2100-2

0

Disp 5002-17

0

11

Disp Meter Count

Truck 661

0

12

Truck 660

0

Truck 739

0

13

Truck 736

0

14

Truck 751

0

15

Truck 753

0

Import

Cell Formulas
Range	Formula
E9,H9	E9	=30000*(D9/100)
E10,H10	E10	=1150*(D10/100)
H11,E12:E13	H11	=3200*(G11/100)
H12,E14	H12	=3000*(G12/100)
E15	E15	=2800*(D15/100)

MikeVol · Sep 10, 2024

Hi @VinnyDoesntKnowExcelCode! Instead of your block in the code:

Code:

    'Copy and paste data from the Import worksheet to the Master List worksheet
    Dte.Copy DestCell
    Bulk2100.Copy DestCell.Offset(0, 1)
    Bulk2100.PasteSpecial xlPasteValues
    Bulk2100P.Copy DestCell.Offset(0, 2)
    Disp2100.Copy DestCell.Offset(0, 3)
    Disp2100P.Copy DestCell.Offset(0, 4)
    DispMeter.Copy DestCell.Offset(0, 5)
    Tr660.Copy DestCell.Offset(0, 7)
    Tr660P.Copy DestCell.Offset(0, 8)
    Tr736.Copy DestCell.Offset(0, 9)
    Tr736P.Copy DestCell.Offset(0, 10)
    Tr751.Copy DestCell.Offset(0, 11)
    Tr751P.Copy DestCell.Offset(0, 12)
    Tr753.Copy DestCell.Offset(0, 13)
    Tr753P.Copy DestCell.Offset(0, 14)
    Bulk2109.Copy DestCell.Offset(0, 15)
    Bulk2109P.Copy DestCell.Offset(0, 16)
    Disp5002.Copy DestCell.Offset(0, 17)
    Disp5002P.Copy DestCell.Offset(0, 18)
    Tr661.Copy DestCell.Offset(0, 19)
    Tr661P.Copy DestCell.Offset(0, 20)
    Tr739.Copy DestCell.Offset(0, 21)
    Tr739P.Copy DestCell.Offset(0, 22)

insert the following corrected code:

VBA Code:

    ' Copy and paste data from the Import worksheet to the Master List worksheet
    DestCell.Value = Dte.Value
    DestCell.Offset(0, 1).Value = Bulk2100.Value
    DestCell.Offset(0, 2).Value = Bulk2100P.Value
    DestCell.Offset(0, 3).Value = Disp2100.Value
    DestCell.Offset(0, 4).Value = Disp2100P.Value
    DestCell.Offset(0, 5).Value = DispMeter.Value
    DestCell.Offset(0, 7).Value = Tr660.Value
    DestCell.Offset(0, 8).Value = Tr660P.Value
    DestCell.Offset(0, 9).Value = Tr736.Value
    DestCell.Offset(0, 10).Value = Tr736P.Value
    DestCell.Offset(0, 11).Value = Tr751.Value
    DestCell.Offset(0, 12).Value = Tr751P.Value
    DestCell.Offset(0, 13).Value = Tr753.Value
    DestCell.Offset(0, 14).Value = Tr753P.Value
    DestCell.Offset(0, 15).Value = Bulk2109.Value
    DestCell.Offset(0, 16).Value = Bulk2109P.Value
    DestCell.Offset(0, 17).Value = Disp5002.Value
    DestCell.Offset(0, 18).Value = Disp5002P.Value
    DestCell.Offset(0, 19).Value = Tr661.Value
    DestCell.Offset(0, 20).Value = Tr661P.Value
    DestCell.Offset(0, 21).Value = Tr739.Value
    DestCell.Offset(0, 22).Value = Tr739P.Value

VinnyDoesntKnowExcelCode · Sep 10, 2024

MikeVol said:

Hi @VinnyDoesntKnowExcelCode! Instead of your block in the code:

Code:

    'Copy and paste data from the Import worksheet to the Master List worksheet
    Dte.Copy DestCell
    Bulk2100.Copy DestCell.Offset(0, 1)
    Bulk2100.PasteSpecial xlPasteValues
    Bulk2100P.Copy DestCell.Offset(0, 2)
    Disp2100.Copy DestCell.Offset(0, 3)
    Disp2100P.Copy DestCell.Offset(0, 4)
    DispMeter.Copy DestCell.Offset(0, 5)
    Tr660.Copy DestCell.Offset(0, 7)
    Tr660P.Copy DestCell.Offset(0, 8)
    Tr736.Copy DestCell.Offset(0, 9)
    Tr736P.Copy DestCell.Offset(0, 10)
    Tr751.Copy DestCell.Offset(0, 11)
    Tr751P.Copy DestCell.Offset(0, 12)
    Tr753.Copy DestCell.Offset(0, 13)
    Tr753P.Copy DestCell.Offset(0, 14)
    Bulk2109.Copy DestCell.Offset(0, 15)
    Bulk2109P.Copy DestCell.Offset(0, 16)
    Disp5002.Copy DestCell.Offset(0, 17)
    Disp5002P.Copy DestCell.Offset(0, 18)
    Tr661.Copy DestCell.Offset(0, 19)
    Tr661P.Copy DestCell.Offset(0, 20)
    Tr739.Copy DestCell.Offset(0, 21)
    Tr739P.Copy DestCell.Offset(0, 22)

insert the following corrected code:

VBA Code:

    ' Copy and paste data from the Import worksheet to the Master List worksheet
    DestCell.Value = Dte.Value
    DestCell.Offset(0, 1).Value = Bulk2100.Value
    DestCell.Offset(0, 2).Value = Bulk2100P.Value
    DestCell.Offset(0, 3).Value = Disp2100.Value
    DestCell.Offset(0, 4).Value = Disp2100P.Value
    DestCell.Offset(0, 5).Value = DispMeter.Value
    DestCell.Offset(0, 7).Value = Tr660.Value
    DestCell.Offset(0, 8).Value = Tr660P.Value
    DestCell.Offset(0, 9).Value = Tr736.Value
    DestCell.Offset(0, 10).Value = Tr736P.Value
    DestCell.Offset(0, 11).Value = Tr751.Value
    DestCell.Offset(0, 12).Value = Tr751P.Value
    DestCell.Offset(0, 13).Value = Tr753.Value
    DestCell.Offset(0, 14).Value = Tr753P.Value
    DestCell.Offset(0, 15).Value = Bulk2109.Value
    DestCell.Offset(0, 16).Value = Bulk2109P.Value
    DestCell.Offset(0, 17).Value = Disp5002.Value
    DestCell.Offset(0, 18).Value = Disp5002P.Value
    DestCell.Offset(0, 19).Value = Tr661.Value
    DestCell.Offset(0, 20).Value = Tr661P.Value
    DestCell.Offset(0, 21).Value = Tr739.Value
    DestCell.Offset(0, 22).Value = Tr739P.Value

Thank you very so much! This code did exactly what I was looking for and matching them all to Value keeps the formatting in Sheet 2 uniformed which is also what I like. Worked first click and could not be more stoked. This website freaking rocks!

Copy only Value not Formula to new Cell location after ActiveX Macro button pushed.

VinnyDoesntKnowExcelCode

New Member

Excel Facts

MikeVol

Board Regular

VinnyDoesntKnowExcelCode

New Member

Similar threads

Forum statistics

Share this page

Copy only Value not Formula to new Cell location after ActiveX Macro button pushed.

VinnyDoesntKnowExcelCode

New Member

Excel Facts

MikeVol

Board Regular

VinnyDoesntKnowExcelCode

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock