dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,352
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have a spreadsheet that is populated from another sheet. Here is a screen shot of it https://www.screencast.com/t/6YANfjeXWzov. As each line is entered and copied across, the code places the new line under the previous line. What is the vba code to enter a formula in columns H and I for each row that I copy across. The formula for the cell H4, must be G4*0.1 and the formula for I4 must be G4+H4. Obviously the references need to change with the row that the new entry is being pasted into. So the second entry into the worksheet must be on row 5 instead of 4.

Here is my code to copy to the sheet.

Code:
Sub cmdCopy2()
Worksheets("home").Unprotect Password:="costings"
Application.ScreenUpdating = False

Dim Lastrow As Long



    Lastrow = Worksheets("All Costings").Cells(Rows.Count, "B").End(xlUp).Row + 1
    
    Worksheets("Home").Range("A5:F5").copy
    'work with cell at intersection of LastRow and column A of All Costings worksheet
    With Worksheets("All Costings").Cells(Lastrow, 1)
        'paste values
        .PasteSpecial Paste:=xlPasteValues
        'format date
        .Columns("A").NumberFormat = "dd/mm/yyyy"
        'left align the date cell in column A
        .HorizontalAlignment = xlLeft
    End With

    
    'assign number of first empty row in column B of Combo to Lastrow
    'Lastrow = Sheets("All costings").Cells(Rows.Count, "A").End(xlUp).Row
    
    'copy value in cell K5 of home worksheet
    Worksheets("Home").Range("K5").copy
    
    'paste value in cell at intersection of Lastrow and column E of Combo
    Worksheets("All costings").Cells(Lastrow, 7).PasteSpecial Paste:=xlPasteValues
    


    Worksheets("Home").Range("L5").copy
    Worksheets("all costings").Cells(Lastrow, 8).PasteSpecial Paste:=xlPasteValues
    Worksheets("Home").Range("M5").copy
    Worksheets("all costings").Cells(Lastrow, 9).PasteSpecial Paste:=xlPasteValues
    
    If Worksheets("home").Range("E5") = "Activities" Then
0
    Else
        'I tried to enter something here but I had no idea
        Worksheets("All Costings").Range("Lastrow, 8").Formula = "G",Lastrow * .1
    End If
    
        
  
    'copy value in cell J5 of Home worksheet
   ' Worksheets("Home").Range("j5").copy
    
    'paste value in cell at intersection of Lastrow and column E of Combo
   ' Worksheets(Combo).Cells(Lastrow, 5).PasteSpecial Paste:=xlPasteValues
    
    'format values in columns G to I of Combo
    Worksheets("all costings").Columns("G:I").NumberFormat = "$#,##0.00"
    
    'format cells to be in ascending date order
    Call SortDates

    'cancel Cut or Copy mode
    Application.CutCopyMode = False

    'turn screen updating on
    Application.ScreenUpdating = True

'Worksheets("home").Protect Password:="costings"

End Sub
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Hi

Try this test:

Code:
Range("H4:H10").Formula = "=G4*0.1"
It needs to be a dynamic formula, not hard coded into vba as it will need to change with every new line that is added. For instance, the first row will be in row 4 but every additional line added will be in extra rows.
 
Last edited:
Upvote 0
Did you try the formula provided by @pgc01
It is dynamic !
If it really needs to be for each row individually, try something lie this

Code:
Worksheets("All Costings").Range("H" & Lastrow).Formula = "=G" & Lastrow & " * .1"
 
Upvote 0
If it really needs to be for each row individually, try something lie this

Code:
Worksheets("All Costings").Range("H" & Lastrow).Formula = "=G" & Lastrow & " * .1"

Thanks Michael, this works perfectly!!!
 
Upvote 0
Hang on, I forgot to comment out the code that I had that was entering the formula in one of the cells. I will get back to you after some more testing.
 
Upvote 0
I got that bit working by
Code:
Worksheets("All Costings").Range("I" & Lastrow).Formula = "=G" & Lastrow
Where the formula is =G7 or whatever the last row is

Still having problems with this code

Code:
        If Worksheets("home").Range("E5") = "Activities" Then


            'paste total formula
            Worksheets("All Costings").Range("I" & Lastrow).Formula = "=G" & Lastrow
            Worksheets("All Costings").Range("H" & Lastrow).Text = "0"
        Else

            
        End If

I don't know how to get the line of code to remove the formula and make the cell 0 is column H, with the line of code: Worksheets("All Costings").Range("H" & Lastrow).Text = "0"
 
Upvote 0
Try

Code:
Worksheets("All Costings").Range("H" & Lastrow).value=0
 
Upvote 0

Forum statistics

Threads
1,203,171
Messages
6,053,885
Members
444,692
Latest member
Queendom

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top