Copying Sheet1 to Sheet2 in a certain pattern

[BEE]

I have 2 Worksheets Sheet1 and Sheet2.

Sheet2 contains the raw data and Sheet1 is the raw data arranged in specific
requirement.

I have all this formula =IF('Sheet2'!A1="","",'Sheet2'!A1) on every cells
from A1 to D50000

I am copying Sheet2 Cells A1,A2,A3,B1,B2,B3 to Sheet1

A1 is Name1
A2 is Date/Time
A3 is Name2
B1 is Value1
B2 is Value2
B3 is Result.

Copy to Sheet1
A1 is Sheet2.Name1
A2 is Sheet2.Value1
A3 is Sheet2.Value2
A4 is Sheet2.Date/Time

What I did is manually key in the formula in every cells on Sheet1.

May I know how can i do it using a marco.

 

[Otto Moehrbach]

BEE
What you say is not clear. You say that you have that formula in each
of 50000 cells. Why? Do you want Sheet1 to display every one of those
50000 cells from Sheet2? You say you are copying 6 cells and you give the
addresses of those 6 cells in Sheet2. But then you give only 4 destination
cells in Sheet1. What happened to the other two cell values?
You say that you want to copy the data "arranged in specific requirement."
Is there a pattern to what you want that repeats? If so, provide specific
cell addresses that you want copied to specific cell addresses for at least
2 repeats of the pattern. HTH Otto

 

[BEE]

What you say is not clear.

Sorry for the confusion.

You say that you have that formula in each of 50000 cells. Why? Do you
want Sheet1 to display every one of those 50000 cells from Sheet2?

Yes. Basically is all data of sheet2 to sheet1 in a specific arrangement.

You say you are copying 6 cells and you give the addresses of those 6 cells
in Sheet2. But then you give only 4 destination cells in Sheet1. What
happened to the other two cell values?

Forgot to state.

You say that you want to copy the data "arranged in specific requirement."
Is there a pattern to what you want that repeats? If so, provide specific
cell addresses that you want copied to specific cell addresses for at least
2 repeats of the pattern

Sheet2
A1(Name1) B1(Date/Time) C1(Name2)
A2(Value1)B2(Value2) C2(Result)
A3(Name1) B3(Date/Time) C3(Name2)
A4(Value1)B4(Value2) C4(Result)

Sheet1
A1(Name1)A2(Value1)B2(Value2)B1(Date/Time)C1(Name2)C2(Result)
A3(Name1)A4(Value1)B4(Value2)B3(Date/Time)C3(Name2)C4(Result)

 

[Otto Moehrbach]

BEE
This little macro should work for you. I named the source sheet "Source"
and the destination sheet "Destination". Note that both sheet names are in
the macro. Change these names as needed. HTH Otto
Sub ReArrange()
Dim rColA As Range
Dim ACell As Range
Dim Dest As Range
Sheets("Source").Select
Set ACell = Range("A1")
Set Dest = Sheets("Destination").Range("A1")
Do
ACell.Copy Dest
ACell.Offset(1).Copy Dest.Offset(, 1)
ACell.Offset(1, 1).Copy Dest.Offset(, 2)
ACell.Offset(, 1).Copy Dest.Offset(, 3)
ACell.Offset(, 2).Copy Dest.Offset(, 4)
ACell.Offset(1, 2).Copy Dest.Offset(, 5)
Set Dest = Dest.Offset(1)
Set ACell = ACell.Offset(2)
Loop Until IsEmpty(ACell.Value)
End Sub

 

[BEE]

I have tried the code and the PC hangs that I need to restart the PC.

 

[Otto Moehrbach]

BEE
It works for me. Where did you put the macro (what module)? What
version of Excel are you using (I have 2002)? What is your macro security
setting (Tools - Macro - Macro Security)? HTH Otto

 

[BEE]

Thank you for your response.

It works for me. Where did you put the macro (what module)?
I paste in in
-In the Sheet1 worksheet.


What version of Excel are you using (I have 2002)?
-I am using 2003

What is your macro security setting (Tools - Macro - Macro Security)?
-Low