Copy from the next row with macro

[adelvoira]

Hi, i record a new macro but i faced a problem when i click the button to copy a cell value in the first row from sheet "Marge Carburants" to sheet "recapitulatif"
it's copy just the first row and didn't jum to the next cell when i click the button to add an other row in sheet "recapitulatif"
here is my code

Sub Calculer()
'
' Calculer Macro
' Paste to Recapitulatif
'

'
    Range("D4").Select
    Selection.Copy
    Sheets("Recapitulatif").Select
    lastrow = Range("a" & Rows.Count).End(xlUp).Row
    Range("a" & lastrow + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range("A1").Select
    Sheets("Calculette").Select
    Range("D7").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Recapitulatif").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range("A1").Select
    Sheets("Calculette").Select
    Range("I11").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Recapitulatif").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range("A1").Select
    Sheets("Calculette").Select
    Range("M12").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Recapitulatif").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range("A1").Select
    Sheets("Marge Carburants").Select
    ActiveCell.Offset(0, -15).Range("A1").Select
    Sheets("Marge produit autre").Select
    ActiveCell.Offset(0, -8).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Recapitulatif").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range("A1").Select
    Sheets("Calculette").Select
    Range("D9").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Recapitulatif").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range("A1").Select
    Sheets("Calculette").Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Calculette").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Recapitulatif").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range("A1").Select
    Sheets("Marge Carburants").Select
    ActiveWindow.ScrollColumn = 2
    ActiveCell.Offset(0, 15).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Marge Carburants").Select
    Range("Q7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Recapitulatif").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range("A1").Select
    Sheets("Marge produit autre").Select
    Range("J7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Recapitulatif").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range("A1").Select
    Sheets("Calculette").Select
    Range("D11").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Recapitulatif").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    Sheets("Calculette").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("D10").Select
    Selection.ClearContents
    Range("D9").Select
    Selection.ClearContents
    Range("D7").Select
    Selection.ClearContents
    Range("D4").Select
    Selection.ClearContents
    Range("I10").Select
    Selection.ClearContents
    Range("H9").Select
    Selection.ClearContents
    Range("H8").Select
    Selection.ClearContents
    Range("H7").Select
    Selection.ClearContents
    Range("H6").Select
    Selection.ClearContents
    Range("H5").Select
    Selection.ClearContents
    Range("L11").Select
    Selection.ClearContents
    Range("L10").Select
    Selection.ClearContents
    Range("L9").Select
    Selection.ClearContents
    Range("L8").Select
    Selection.ClearContents
    Range("L7").Select
    Selection.ClearContents
    Range("L6").Select
    Selection.ClearContents
    Range("L5").Select
    Selection.ClearContents
End Sub

 

[JackDanIce]

Re: how to copy from the next row with macro

Can you explain in words, what you want your macro to do? There is too much code there to read and decipher.

Use explicit terms like "With Sheet xxx, starting in cell yyy, I want to..." so it's clear, without your PC screen to view.

 

[adelvoira]

Re: how to copy from the next row with macro

Look: i have a sheet named (marge carburants) it had a table with fonctions i need to copy from range("q7") to sheet ("recapitulatif") and it works good, but the problem is how to make my macro jump to the next range(q8,q9,q10.....)when i click the button and with condition if the cell had a value.
if you didn't understand i'll send you the file
Thank you

Can you explain in words, what you want your macro to do? There is too much code there to read and decipher.

Use explicit terms like "With Sheet xxx, starting in cell yyy, I want to..." so it's clear, without your PC screen to view.

 

[JackDanIce]

Re: how to copy from the next row with macro

Look:

I can't see your PC screen.

It's still not clear and as per the board rules, you can only upload a file to a file sharing site and post a link back to this thread, not send privately.

It may be quicker to calculate the formula values in code, rather than use the formulas in the sheet. See if you can adapt the following:

Sub CopyLoop()

    Dim x   As Long
    
    For x = 7 To 50
        Sheets("Marge Carburants").Range("Q" & x).Copy
        Sheets("Recapitulatif").Range("A" & Rows.count).End(xlUp).Offset(1).PasteSpecial xlPasteFormulas
    Next x
    
    Application.CutCopyMode = False

End Sub

 

[adelvoira]

Re: how to copy from the next row with macro

Ok thank you for trying to help me,
the probleme is still percist and i can't upload the file in the thread because it's private
Thank you

I can't see your PC screen.

[/code]

 

[JackDanIce]

Re: how to copy from the next row with macro

You could use data that isn't confidential and create a dummy workbook.

Are you, trying to create a working macro, so it doesn't matter what the data is? If I copy cell A to cell B, does it matter what the cell value or formula is or is it more important to learn how to write the code that does this?

Or are you trying to complete work and don't have time to create a dummy workbook but have time to try to record and edit a macro and then wait for a solution to be suggested?

Unfortunately, your explanation is not clear and there is too much code to review and intuit what you want to achieve.

I understand you want to loop and copy formulas but is it column D or column Q? What sheet is it on you want to loop? What sheet and cell should the first result be copied to?

Are you aware if you can't show your PC monitor or share the file then you need to find a way to explain the problem better for any suggestion to work?

 

[adelvoira]

Re: how to copy from the next row with macro

This is the link to download my book file https://ufile.io/f3qqr

Yes this is what i want
(If I copy cell A to cell B, does it matter what the cell value or formula is or is it more important to learn how to write the code that does this?
Or are you trying to complete work and don't have time to create a dummy workbook but have time to try to record and edit a macro and then wait for a solution to be suggested?)
?

The first problem is:
in sheet (Marge Carburants) there is a formula in each table but i don't need it to calculate if i didn't inter any value in sheet (Index) as shown in (range 8) Marge Carburants
The second thing and this is the important:
As i told you before
i need to copy the value of the last row in sheet(marge carburants) column "q" (total vente) to sheet (racapitulatif) in last row.

i know that you can't understand me without my pc screen

 

[JackDanIce]

Re: how to copy from the next row with macro

i need to copy the value of the last row in sheet(marge carburants) column "q" (total vente) to sheet (racapitulatif) in last row.

So you're aware I can't see your PC monitor, what column in racapitulatif? Column A? Column B? Column C? Column AB? Column CC?

This will copy the last row in column Q of sheet Marge Carburants to a column you specificy by adjusting the code:

Sub Macro1

Sheets("Racapitulatif").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("Marge Carburants").Range("Q" & Rows.Count).End(xlUp).Row

End Sub

 

[adelvoira]

Re: how to copy from the next row with macro

I adjust the code to copy in column "H" of sheet recapitulatif but the calculation it's not going good