Copy from the next row with macro

A

adelvoira

New Member
Joined
Sep 9, 2017
Messages
35
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
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
:confused:
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
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.
 
Upvote 0
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
JackDanIce said:
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.
Click to expand...
 
Upvote 0
Re: how to copy from the next row with macro

Look:
Click to expand...
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:
Code: 
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
 
Last edited:
Upvote 0
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?
 
Last edited:
Upvote 0
Re: how to copy from the next row with macro

This is the link to download my book file https://ufile.io/f3qqr
JackDanIce said:
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?)
?
Click to expand...

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 ;)
 
Upvote 0
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.
Click to expand...

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:
Rich (BB 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
 
Last edited:
Upvote 0
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 :mad:
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Adding to an existing MACRO
Replies
2
Views
125
Northern NY Design
N
willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
400
willow1985
willow1985
K
  • Question Question
Increasing row before the macro runs again
Replies
1
Views
230
kbk
K
C
  • Question Question
Please help clean up my convoluted VBA code. It seems very redundant and repetitive.
Replies
1
Views
441
myall_blues
myall_blues
R
  • Question Question
I keep getting the error 91 but i cant figure out what is wrong here.
Replies
2
Views
60
mumps
mumps

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

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