To any lines of code

[0774don]

I am a relative beginner at writing macros, self taught but enthusiastic. I would appreciate any help you could offer with my coding problem.
Attached is a simple macro to illustrate my problem.
The macro code, amongst other things, enters data from a row into a column.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
The value in the first cell entered manually.
Then the macro, (lines 4-33) auto fills the range C20:R20, and cuts and pastes
the individual cells values in row 20 into the column C.
As further data is entered into row 20, it is copied into column C
<o> </o>
Even in this simple example this action generates 29 lines of repetitive code, and lots of opportunities for errors.
<o> </o>
Is there any way of condensing these 29 lines of code, and achieve the same result?
Any assistance would be gratefully received.
<o> </o>
Thank you,
Don.

Sub DOUBLE_ENTRY_TRIAL()<o:p></o:p>
'<o:p></o:p>
' DOUBLE_ENTRY_TRIAL Macro<o:p></o:p>
' Macro recorded <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:date Month="5" Day="25" Year="2012">25/05/2012</st1:date> by Don<o:p></o:p>
'<o:p></o:p>
<o:p> </o:p>
'<o:p></o:p>
    Range("C20").Select<o:p></o:p>
    Selection.AutoFill Destination:=Range("C20:R20"), Type:=xlFillDefault<o:p></o:p>
    Range("C20:R20").Select<o:p></o:p>
    Range("D20").Select<o:p></o:p>
    Selection.Cut Destination:=Range("C21")<o:p></o:p>
    Range("E20").Select<o:p></o:p>
    Selection.Cut Destination:=Range("C22")<o:p></o:p>
    Range("F20").Select<o:p></o:p>
    Selection.Cut Destination:=Range("C23")<o:p></o:p>
    Range("G20").Select<o:p></o:p>
    Selection.Cut Destination:=Range("C24")<o:p></o:p>
    Range("H20").Select<o:p></o:p>
    Selection.Cut Destination:=Range("C25")<o:p></o:p>
    Range("I20").Select<o:p></o:p>
    Selection.Cut Destination:=Range("C26")<o:p></o:p>
    Range("J20").Select
      Selection.Cut Destination:=Range("C27")<o:p></o:p>
    Range("K20").Select<o:p></o:p>
    Selection.Cut Destination:=Range("C28")<o:p></o:p>
    Range("L20").Select<o:p></o:p>
    Selection.Cut Destination:=Range("C29")<o:p></o:p>
    Range("M20").Select<o:p></o:p>
    Selection.Cut Destination:=Range("C30")<o:p></o:p>
    Range("N20").Select<o:p></o:p>
    Selection.Cut Destination:=Range("C31")<o:p></o:p>
    Range("O20").Select<o:p></o:p>
    Selection.Cut Destination:=Range("C32")<o:p></o:p>
    Range("P20").Select<o:p></o:p>
    Selection.Cut Destination:=Range("C33")<o:p></o:p>
    Range("Q20").Select<o:p></o:p>
    Selection.Cut Destination:=Range("C34")<o:p></o:p>
    Range("R20").Select<o:p></o:p>
    Selection.Cut Destination:=Range("C35")<o:p></o:p>
    Range("D20").Select<o:p></o:p>
    ActiveCell.FormulaR1C1 = "=R[-1]C-RC[-1]"<o:p></o:p>
    Range("D20").Select<o:p></o:p>
    Selection.AutoFill Destination:=Range("D20:D35"), Type:=xlFillDefault<o:p></o:p>
    Range("D20:D35").Select<o:p></o:p>
    Range("I31").Select<o:p></o:p>
End Sub

 

[AlphaFrog]

[color=darkblue]Sub[/color] DOUBLE_ENTRY_TRIAL()
[color=green]'[/color]
[color=green]' DOUBLE_ENTRY_TRIAL Macro[/color]
[color=green]' Macro recorded 25/05/2012 by Don[/color]
[color=green]'[/color]
    Range("C20").AutoFill Destination:=Range("C20:C35"), Type:=xlFillDefault
    Range("D20:D35").FormulaR1C1 = "=R[-1]C-RC[-1]"

[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[kpark91]

Hi, try

Sub DOUBLE_ENTRY_TRIAL()
    Range("C20:R20").Cut Destination:=Range("C21")
    Range("D20").FormulaR1C1 = "=R[-1]C-RC[-1]"
    Range("D20").AutoFill Destination:=Range("D20:D35"), Type:=xlFillDefault
End Sub

.Select method is known for its slowness.
So, You can reproduce this code by just replacing the object (whatever is in front of .Select) with the following Selection

So, replace this code with

Range("F20").Select
Selection.Cut Destination:=Range("C23")

with

Range("F20").Cut Destination:=Range("C23")

Just a simple way to speed up the code from recording macro

 

[BiocideJ]

I am assuming there is a legitimate reason you autofill it to the right first.
This emulates the entire process, but with less code.

Sub DOUBLE_ENTRY_TRIAL()
'
' DOUBLE_ENTRY_TRIAL Macro
' Macro recorded 25/05/2012 by Don
'
 
    Range("C20:R20").Formula = Range("C20").Formula
    Range("D20:R20").Copy
    Range("C21").PasteSpecial xlPasteAll, Transpose:=True
    Range("D20:R20").Clear
    Application.CutCopyMode = False
    Range("D20").FormulaR1C1 = "=R[-1]C-RC[-1]"
    Range("D20").AutoFill Destination:=Range("D20:D35"), Type:=xlFillDefault
    Range("I31").Select
End Sub

I suspect if we understood exactly what this was doing there is an even easier way.

 

[0774don]

Re: To many lines of code

Further to my post,
Windows XP, Office XP Pro SP2, Excel 2002
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Sorry didn’t know how to post my example worksheet.
<o> </o>
Attached is an Excel worksheet to illustrate simply, a method of accounting that I have developed for my own use.
The example consists of a sample “weekly cash book”, and one specimen “ledger sheet”.
The entries in the cash book would be copied and pasted from weekly collection records, and automatically from there onto the individual ledger sheets.
<o> </o>
When a new ledger sheet is created, the invoice value is entered manually into Cell B19 (ledger).
Cell D5 (cash book) is copied and pasted to Cell C20 (Ledger),
Then the macro is run.
<o> </o>
In the macro, (lines 4-33) selects, and auto fills the range C20:R20, and cuts, and pastes the individual cells in the range into column C. in the “ledger sheet.”
<o> </o>
As further data is entered into the “Cash Book” it is copied to the correct “ledger sheet”
<o> </o>
Even in this simple example this action generates 29 lines of repetitive code, and lots of opportunities for errors.
<o> </o>
The Transpose Function, or the Copy> Paste special> Transpose, route will not work as only existing values are transposed.
Hope that makes my question a little clearer
<o> </o>
Thank you,
Don.

Excel 2002
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Weekly Cash Book
2				Payments
3	Account name	Invoice	Balance	Wk.1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
4
5	A.N.Other		60.00	5.00	10.00	-	5.00	5.00	4.00	6.00	-	-	5.00
6	G Raffe			10.00	4.00	5.00	6.00	5.00	6.00	4.00	-	-	-
7	N.E.Bodie			10.00	2.00	10.00	10.00	41.00	2.00	3.00	6.00	4.00	5.00
8
9	Totals		60.00	25.00	16.00	15.00	21.00	51.00	12.00	13.00	6.00	4.00	10.00	-	-	-	-	-
10
11
12
13
14	SALES LEDGER SHEET
15
16	Account Name	A.N.Other
17
18		DR	CR	BAL
19	Invoice	100.00		100.00
20	Payment Wk1		5.00	95.00
21	2		10.00	85.00
22	3		-	85.00
23	4		5.00	80.00
24	5		5.00	75.00
25	6		4.00	71.00
26	7		6.00	65.00
27	8		-	65.00
28	9		-	65.00
29	10		5.00	60.00
30	11		-	60.00
31	12		-	60.00
32	13		-	60.00
33	14		-	60.00
34			-	60.00
35			-	60.00
36		100.00	40.00	60.00
Sheet1