COPY DATA IN SPECIFIC WAY


K

K

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.
 
Ad

Advertisements

H

Heera

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
 
D

Don Guillett

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
(e-mail address removed)
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.
 
K

K

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

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" ?
 
D

Don Guillett

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
(e-mail address removed)
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

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" ?
 
Ad

Advertisements

R

Rick Rothstein \(MVP - VB\)

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top