Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
PaulPoll
 
Posts: n/a
Default Macro - Skipping a line

I want to make a macro that will automatically skip a line. Each day I fill
in a cell with a number which changes seven or so various formula cells
residing on one line. I then copy and paste those values to their place below
the last day's entry. Each day it's the same procedure but the values change.
I cannot get a macro to find the place it should "paste" the value to
properly. It always copies over and replaces the cells from the previous day
because it is not moving down a line. Eg. Every day I make an entry to A1
which in turn changes the values in A5 thru H5. I then copy cells A5 thru H5
and paste those "values" to A6 thru H6. The next day, after entering data in
A1 that changes the values in A5 thru H5, I again want to record those new
values but I now want them pasted in the line below the previous day's entry;
I now want them pasted in A7 thru H7, each day moving to the next line below.
Can anyone tell me what I need to add to a macro to make it automatically
move to the next line? Thank you.
  #3   Report Post  
Gord Dibben
 
Posts: n/a
Default

Paul

Sub Copyit()
Range("A5:H5").Copy Destination:= _
ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
End Sub


Gord Dibben Excel MVP

On Mon, 20 Dec 2004 10:01:02 -0800, "PaulPoll"
wrote:

I want to make a macro that will automatically skip a line. Each day I fill
in a cell with a number which changes seven or so various formula cells
residing on one line. I then copy and paste those values to their place below
the last day's entry. Each day it's the same procedure but the values change.
I cannot get a macro to find the place it should "paste" the value to
properly. It always copies over and replaces the cells from the previous day
because it is not moving down a line. Eg. Every day I make an entry to A1
which in turn changes the values in A5 thru H5. I then copy cells A5 thru H5
and paste those "values" to A6 thru H6. The next day, after entering data in
A1 that changes the values in A5 thru H5, I again want to record those new
values but I now want them pasted in the line below the previous day's entry;
I now want them pasted in A7 thru H7, each day moving to the next line below.
Can anyone tell me what I need to add to a macro to make it automatically
move to the next line? Thank you.


  #4   Report Post  
PaulPoll
 
Posts: n/a
Default

Dear Mr. Dibben,
Thank you for responding. I've tried working with what you suggested but
have not been successful. I am not very experienced with this stuff. I've
enclosed below what my Macro is now and unfortunately it does exactly what I
was complaining about originally; not skipping a line. As I said I tried your
suggestion but I just keep getting various error messages and I really don't
know what I am doing. If you would be kind enough, and have the time to do
so, maybe you could add your suggestion to my recorded macro below so it will
work as I was hoping it would. Thank you.
Sincerely, Paul Pollack

LineSkip Macro
' Macro recorded 12/20/2004 by Paul Pollack
'

'
Range("F12:J12").Select
Selection.Copy
Range("F52:J52").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("L51:R52").Select
Application.CutCopyMode = False
Selection.FillDown
Range("K12").Select
Selection.Copy
Range("K52").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

End Sub

"Gord Dibben" wrote:

Paul

Sub Copyit()
Range("A5:H5").Copy Destination:= _
ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
End Sub


Gord Dibben Excel MVP

On Mon, 20 Dec 2004 10:01:02 -0800, "PaulPoll"
wrote:

I want to make a macro that will automatically skip a line. Each day I fill
in a cell with a number which changes seven or so various formula cells
residing on one line. I then copy and paste those values to their place below
the last day's entry. Each day it's the same procedure but the values change.
I cannot get a macro to find the place it should "paste" the value to
properly. It always copies over and replaces the cells from the previous day
because it is not moving down a line. Eg. Every day I make an entry to A1
which in turn changes the values in A5 thru H5. I then copy cells A5 thru H5
and paste those "values" to A6 thru H6. The next day, after entering data in
A1 that changes the values in A5 thru H5, I again want to record those new
values but I now want them pasted in the line below the previous day's entry;
I now want them pasted in A7 thru H7, each day moving to the next line below.
Can anyone tell me what I need to add to a macro to make it automatically
move to the next line? Thank you.



  #5   Report Post  
Dave Peterson
 
Posts: n/a
Default

I'm kind of confused over your ranges. You wrote A5:H5, but I don't see that in
your code at all.

I'm guessing that the text of your first message was ok.

Option Explicit
Sub testme01()

Dim tRng As Range
Dim fRng As Range

With Worksheets("sheet1")
Set fRng = .Range("A5:H5")
Set tRng = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
fRng.Copy
tRng.PasteSpecial Paste:=xlPasteValues
End With

End Sub

This relies on column A having something in it when you paste.

It goes to the bottom of column A (A65536) and then essentially hits the End key
followed by the uparrow. This brings it to the last used cell in column A.
Then .offset(1,0) moves it down one row.



PaulPoll wrote:

Dear Mr. Dibben,
Thank you for responding. I've tried working with what you suggested but
have not been successful. I am not very experienced with this stuff. I've
enclosed below what my Macro is now and unfortunately it does exactly what I
was complaining about originally; not skipping a line. As I said I tried your
suggestion but I just keep getting various error messages and I really don't
know what I am doing. If you would be kind enough, and have the time to do
so, maybe you could add your suggestion to my recorded macro below so it will
work as I was hoping it would. Thank you.
Sincerely, Paul Pollack

LineSkip Macro
' Macro recorded 12/20/2004 by Paul Pollack
'

'
Range("F12:J12").Select
Selection.Copy
Range("F52:J52").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("L51:R52").Select
Application.CutCopyMode = False
Selection.FillDown
Range("K12").Select
Selection.Copy
Range("K52").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

End Sub

"Gord Dibben" wrote:

Paul

Sub Copyit()
Range("A5:H5").Copy Destination:= _
ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
End Sub


Gord Dibben Excel MVP

On Mon, 20 Dec 2004 10:01:02 -0800, "PaulPoll"
wrote:

I want to make a macro that will automatically skip a line. Each day I fill
in a cell with a number which changes seven or so various formula cells
residing on one line. I then copy and paste those values to their place below
the last day's entry. Each day it's the same procedure but the values change.
I cannot get a macro to find the place it should "paste" the value to
properly. It always copies over and replaces the cells from the previous day
because it is not moving down a line. Eg. Every day I make an entry to A1
which in turn changes the values in A5 thru H5. I then copy cells A5 thru H5
and paste those "values" to A6 thru H6. The next day, after entering data in
A1 that changes the values in A5 thru H5, I again want to record those new
values but I now want them pasted in the line below the previous day's entry;
I now want them pasted in A7 thru H7, each day moving to the next line below.
Can anyone tell me what I need to add to a macro to make it automatically
move to the next line? Thank you.




--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Line chart - date line association gone mad! Johannes Czernin Charts and Charting in Excel 5 January 17th 05 09:48 PM
Zip Code Macro Ken Wright Excel Worksheet Functions 0 December 9th 04 08:55 AM
Macro and If Statement SATB Excel Discussion (Misc queries) 2 December 3rd 04 05:46 PM
Macro Formula revision? Mark Excel Worksheet Functions 1 November 28th 04 02:43 AM
Macro for multiple charts JS Excel Worksheet Functions 1 November 19th 04 04:44 AM


All times are GMT +1. The time now is 07:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"