ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   vba, step to next line (https://www.excelbanter.com/excel-programming/362150-vba-step-next-line.html)

Learning the hard way
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.

Learning the hard way
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

Bob Phillips[_14_]
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

Learning the hard way
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

Learning the hard way
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.

Bob Phillips[_14_]
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.


All times are GMT +1. The time now is 04:20 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com