Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > vba, step to next line
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6
Default vba, step to next line
Can anyone help? i have written a macro to copy what i want from one sheet to
another,(in the same book) but then i want it to copy on the next line when i
run it again. so as i keep the first lot of info, thus ending up with a page
of info as you go.
I have included the code so far.
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6
Default vba, step to next line


"Learning the hard way" wrote:

Can anyone help? i have written a macro to copy what i want from one sheet to
another,(in the same book) but then i want it to copy on the next line when i
run it again. so as i keep the first lot of info, thus ending up with a page
of info as you go.
I have included the code so far.
Sub copy_keiths_invs()
'
' copy_keiths_invs Macro
' Macro recorded 23/05/2006 by Nick
'

'
Range("C10").Select
Selection.Copy
Sheets("Keith's Payments").Select
Range("A11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Payment Advice").Select
Range("D19").Select
Selection.Copy
Sheets("Keith's Payments").Select
Range("B11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Payment Advice").Select
Range("D20").Select
Selection.Copy
Sheets("Keith's Payments").Select
Range("C11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Payment Advice").Select
Range("D22").Select
Selection.Copy
Sheets("Keith's Payments").Select
Range("D11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Payment Advice").Select
Range("D23").Select
Selection.Copy
Sheets("Keith's Payments").Select
Range("E11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Payment Advice").Select
Range("D24").Select
Selection.Copy
Sheets("Keith's Payments").Select
Range("F11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A12").Select
Sheets("Payment Advice").Select
Application.CutCopyMode = False
End Sub

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 216
Default vba, step to next line
tRY THIS

Sub copy_keiths_invs()
'
' copy_keiths_invs Macro
' Macro recorded 23/05/2006 by Nick

Dim shSource As Worksheet
Dim shTarget As Worksheet
Dim iLastRow As Long

Set shSource = Worksheets("Payment Advice")
Set shTarget = Worksheets("Keith's Payments")

shTarget.Activate
With shSource
.Range("C10").Copy
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
CopyData "A" & iLastRow + 1
.Range("D19").Copy
CopyData "B" & iLastRow + 1
.Range("D20").Copy
CopyData "C" & iLastRow + 1
.Range("D22").Copy
CopyData "D" & iLastRow + 1
.Range("D23").Copy
CopyData "E" & iLastRow + 1
.Range("D24").Select
CopyData "F" & iLastRow + 1
End With
Application.CutCopyMode = False
End Sub

Private Sub CopyData(Target As String)
Range(Target).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End Sub


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Learning the hard way" wrote
in message ...


"Learning the hard way" wrote:

Can anyone help? i have written a macro to copy what i want from one
sheet to
another,(in the same book) but then i want it to copy on the next line
when i
run it again. so as i keep the first lot of info, thus ending up with a
page
of info as you go.
I have included the code so far.
Sub copy_keiths_invs()
'
' copy_keiths_invs Macro
' Macro recorded 23/05/2006 by Nick
'

'
Range("C10").Select
Selection.Copy
Sheets("Keith's Payments").Select
Range("A11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Payment Advice").Select
Range("D19").Select
Selection.Copy
Sheets("Keith's Payments").Select
Range("B11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Payment Advice").Select
Range("D20").Select
Selection.Copy
Sheets("Keith's Payments").Select
Range("C11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Payment Advice").Select
Range("D22").Select
Selection.Copy
Sheets("Keith's Payments").Select
Range("D11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Payment Advice").Select
Range("D23").Select
Selection.Copy
Sheets("Keith's Payments").Select
Range("E11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Payment Advice").Select
Range("D24").Select
Selection.Copy
Sheets("Keith's Payments").Select
Range("F11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A12").Select
Sheets("Payment Advice").Select
Application.CutCopyMode = False
End Sub



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6
Default vba, step to next line
Thanks Bob, worked great up until the last copy then got " Runtime error 1004"
"Select method of range class failed."
As i am still learning cant seem to fix myself would greatly apprec. your
input once again.
Thankx.
"Bob Phillips" wrote:

tRY THIS

Sub copy_keiths_invs()
'
' copy_keiths_invs Macro
' Macro recorded 23/05/2006 by Nick

Dim shSource As Worksheet
Dim shTarget As Worksheet
Dim iLastRow As Long

Set shSource = Worksheets("Payment Advice")
Set shTarget = Worksheets("Keith's Payments")

shTarget.Activate
With shSource
.Range("C10").Copy
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
CopyData "A" & iLastRow + 1
.Range("D19").Copy
CopyData "B" & iLastRow + 1
.Range("D20").Copy
CopyData "C" & iLastRow + 1
.Range("D22").Copy
CopyData "D" & iLastRow + 1
.Range("D23").Copy
CopyData "E" & iLastRow + 1
.Range("D24").Select
CopyData "F" & iLastRow + 1
End With
Application.CutCopyMode = False
End Sub

Private Sub CopyData(Target As String)
Range(Target).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End Sub


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Learning the hard way" wrote
in message ...


"Learning the hard way" wrote:

Can anyone help? i have written a macro to copy what i want from one
sheet to
another,(in the same book) but then i want it to copy on the next line
when i
run it again. so as i keep the first lot of info, thus ending up with a
page
of info as you go.
I have included the code so far.
Sub copy_keiths_invs()
'
' copy_keiths_invs Macro
' Macro recorded 23/05/2006 by Nick
'

'
Range("C10").Select
Selection.Copy
Sheets("Keith's Payments").Select
Range("A11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Payment Advice").Select
Range("D19").Select
Selection.Copy
Sheets("Keith's Payments").Select
Range("B11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Payment Advice").Select
Range("D20").Select
Selection.Copy
Sheets("Keith's Payments").Select
Range("C11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Payment Advice").Select
Range("D22").Select
Selection.Copy
Sheets("Keith's Payments").Select
Range("D11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Payment Advice").Select
Range("D23").Select
Selection.Copy
Sheets("Keith's Payments").Select
Range("E11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Payment Advice").Select
Range("D24").Select
Selection.Copy
Sheets("Keith's Payments").Select
Range("F11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A12").Select
Sheets("Payment Advice").Select
Application.CutCopyMode = False
End Sub




Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6
Default vba, step to next line
Its ok Bob found the problem thanks again, "Learning the hard way"

"Learning the hard way" wrote:

Can anyone help? i have written a macro to copy what i want from one sheet to
another,(in the same book) but then i want it to copy on the next line when i
run it again. so as i keep the first lot of info, thus ending up with a page
of info as you go.
I have included the code so far.
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 216
Default vba, step to next line
Select should have been Copy?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Learning the hard way" wrote
in message ...
Its ok Bob found the problem thanks again, "Learning the hard way"

"Learning the hard way" wrote:

Can anyone help? i have written a macro to copy what i want from one
sheet to
another,(in the same book) but then i want it to copy on the next line
when i
run it again. so as i keep the first lot of info, thus ending up with a
page
of info as you go.
I have included the code so far.


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

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
Creating a Drop Down List with Step by Step Instructions for 2007 remarkable Excel Worksheet Functions 2 March 22nd 09 04:36 AM
Need step by step to add invoice numbering to excel template rmt New Users to Excel 4 July 6th 08 11:45 PM
Creating a step-up line as a graph ? [email protected] Charts and Charting in Excel 1 May 3rd 07 10:55 AM
What is the step-by-step procedure for making a data list? Bobgolfs56 Excel Discussion (Misc queries) 1 April 23rd 05 02:19 PM
I need step by step instructions to create a macro for 10 imbedde. diana Excel Worksheet Functions 3 January 31st 05 01:56 AM


All times are GMT +1. The time now is 10:13 PM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement