Link to home
Start Free TrialLog in
Avatar of wlwebb
wlwebbFlag for United States of America

asked on

Excel VB for copying formulas from last row to the next row

Hi all!  Another stupid newbie question.

I am attempting to copy the formulas from the last line to the next open line after that line in Excel 2007.  Below is the code but it keeps copying the formula a the starting cell B11 instead of the next open line.  I know the problem is the line of code

 Range("B11").Select

How do I change that code to copy from the last line to the next open line ("B11") was the line that was open when I recorded my macro.

Sub CopyNewPayDatesAddtlInfo()
'
' Macro14 Macro
'

'
    Range("B4").Select
    Selection.End(xlDown).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Range("B11").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("B4").Select
End Sub

Open in new window

Avatar of Ardhendu Sarangi
Ardhendu Sarangi
Flag of United States of America image

Hi wlwebb,
I think there is a lot of redundant lines in this code. can you please explain what you are trying to do here?

- Ardhendu
SOLUTION
Avatar of Ardhendu Sarangi
Ardhendu Sarangi
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of wlwebb

ASKER

Pari
I know that there are easier ways to do what I am attempting.  Just not very good at it yet so you can only go with what you know.  

Anyway, in looking at your code I have a question.  In the line  (n = Cells(65536, "B").End(xlUp).Row) why put that line of code in?  What is the purpose of 65536?  What happens when the user reaches line 65537 of data?

Keep in mind, I am only wanting to copy the formula to one row, that row being the very next open line and to that line only.  Will you code accomplish that?  Or will it copy that formula all the way down to line 65536?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of wlwebb

ASKER

Thanks for the input
Hi wlwebb,

is there a reason for grading this as B? Please see the following help section on the grades to give.

https://www.experts-exchange.com/help.jsp#hs=29&hi=403 

thanks,
Ardhendu

Instead of 65536, you should get in the habit of using Rows.Count so it will be compatible across all versions.
Also, a B grade is supposed to be used if the experts didn't really get you the solution.
Since you didn't ask any further questions pari certainly assumed that you didn't need any more help. It does not appear to me that anything was lacking.