Hi, I want macro which should copy data from Sheet1 to Sheet2 in
specific way (see below)

Copy Sheet1 Cell A3 value to Sheet2 Cell A10
Copy Sheet1 Cell A4 value to Sheet2 Cell A13
Copy Sheet1 Cell A5 value to Sheet2 Cell A16
Copy Sheet1 Cell A5 value to Sheet2 Cell A19
and so on ….. till Sheet1 Cell A40

Macro should copy all data from Sheet1 Range("A3:A40") to Sheet2 but
it should paste each cell value of Sheet1 to two cells below of first
cell of Sheet2 as mentioned above. Please can any friend help me on
this.

Hi,


Please use the below mentioned macro but you have to put "End" in the
Cell A41 of Sheet1 otherwise the macro will go in never ending loop.


Sub Paste()
Sheets("Sheet1").Select
Range("A3").Select
Sheets("Sheet2").Select
Range("A10").Select
ActiveSheet.Previous.Select

Do Until ActiveCell.Value = "End"
ActiveCell.Copy
ActiveSheet.Next.Select
ActiveSheet.Paste
ActiveSheet.Previous.Select
ActiveCell.Offset(1, 0).Range("A1").Select
Application.CutCopyMode = False
ActiveCell.Copy
ActiveSheet.Next.Select
ActiveCell.Offset(3, 0).Range("A1").Select
ActiveSheet.Previous.Select
Loop
Application.CutCopyMode = False

End Sub

Modify ranges to suit. Pls don't type in ALL CAPS.

Sub copycellstoaltrows()
For i = 3 To 8
Cells(i, "a").Copy Cells(3 * i, "b")
Next i
Cells(9, "b").Insert Shift:=xlDown
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"K" wrote in message
...
Hi, I want macro which should copy data from Sheet1 to Sheet2 in
specific way (see below)

Copy Sheet1 Cell A3 value to Sheet2 Cell A10
Copy Sheet1 Cell A4 value to Sheet2 Cell A13
Copy Sheet1 Cell A5 value to Sheet2 Cell A16
Copy Sheet1 Cell A5 value to Sheet2 Cell A19
and so on ….. till Sheet1 Cell A40

Macro should copy all data from Sheet1 Range("A3:A40") to Sheet2 but
it should paste each cell value of Sheet1 to two cells below of first
cell of Sheet2 as mentioned above. Please can any friend help me on
this.

On Aug 22, 2:01*pm, "Don Guillett" wrote:
Modify ranges to suit. Pls don't type in ALL CAPS.

Sub copycellstoaltrows()
For i = 3 To 8
Cells(i, "a").Copy Cells(3 * i, "b")
Next i
Cells(9, "b").Insert Shift:=xlDown
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"K" wrote in message

...
Hi, *I want macro which should copy data from Sheet1 to Sheet2 in
specific way (see below)

Copy Sheet1 Cell A3 value to Sheet2 Cell A10
Copy Sheet1 Cell A4 value to Sheet2 Cell A13
Copy Sheet1 Cell A5 value to Sheet2 Cell A16
Copy Sheet1 Cell A5 value to Sheet2 Cell A19
and so on ….. till Sheet1 Cell A40

Macro should copy all data from Sheet1 Range("A3:A40") to Sheet2 but
it should paste each cell value of Sheet1 to two cells below of first
cell of Sheet2 as mentioned above. *Please can any friend help me on
this.

Thanks guies for your help. I liked Don's code as its small. Don i
have amended your code (see below)

Sub copycellstoaltrows()
For i = 3 To 42
Cells(i, "A").Copy Cells(3 * i + 1, "C")
Next i
End Sub

This is what i needed. can i ask that what you mean by "Pls don't
type in ALL CAPS" ?

Typing in all caps is considered to be shouting and frowned upon.
Also, most of us TOP post for ease in readability.
Thank you for your cooperation.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"K" wrote in message
...
On Aug 22, 2:01 pm, "Don Guillett" wrote:
Modify ranges to suit. Pls don't type in ALL CAPS.

Sub copycellstoaltrows()
For i = 3 To 8
Cells(i, "a").Copy Cells(3 * i, "b")
Next i
Cells(9, "b").Insert Shift:=xlDown
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"K" wrote in message

...
Hi, I want macro which should copy data from Sheet1 to Sheet2 in
specific way (see below)

Copy Sheet1 Cell A3 value to Sheet2 Cell A10
Copy Sheet1 Cell A4 value to Sheet2 Cell A13
Copy Sheet1 Cell A5 value to Sheet2 Cell A16
Copy Sheet1 Cell A5 value to Sheet2 Cell A19
and so on ….. till Sheet1 Cell A40

Macro should copy all data from Sheet1 Range("A3:A40") to Sheet2 but
it should paste each cell value of Sheet1 to two cells below of first
cell of Sheet2 as mentioned above. Please can any friend help me on
this.

Thanks guies for your help. I liked Don's code as its small. Don i
have amended your code (see below)

Sub copycellstoaltrows()
For i = 3 To 42
Cells(i, "A").Copy Cells(3 * i + 1, "C")
Next i
End Sub

This is what i needed. can i ask that what you mean by "Pls don't
type in ALL CAPS" ?

This is what i needed. can i ask that what you mean
by "Pls don't type in ALL CAPS" ?

Your subject line was typed in "all caps" (short for "all capitalized
letters")... text typed in all upper case letters is harder to read than
normal cased letters (it really is annoying to read) and it is also
considered the written equivalent of shouting.

Rick