How to transfer a range from sheet to sheet?

[Jon]

Greeting,

Is there any way to transfer the range e.g. a1:a6 from sheet1 to sheet2 but
in sheet2 should be distributed as follows:

From a1:a2 to sheet 2 b1:b2

From a3:a4 to sheet 2 c10:c11

From a5:a6 to sheet 2 d1:d2

Any help please to do it in one go???

 

[Mark]

Do you know how to use macros and or VBA?

 

[Jon]

No, Please advice??

 

[Mark]

Ok, here goes:

1) "RightClick" the Excel menu bar at the top of your screen: A
dropdown menu should appear that offers an option for "Visual Basic".
Click on it and a new bar should appear on your screen.
On this bar there should be an icon that looks like a page with
some sort of red and yellow on top of it. Click on this. You should
now have a new window open to the Visual Basic Editor.

Try this and then let me know if you got this far, then we will
continue.

 

[Mark]

Ok, there should be 3 panes here, the one on the top left should have
a folder titled: "Modules". Right click and select "Insert"---

Module.

Now the pane on the right should be completely blank, (the big one).
Copy the following code and paste it into this pane.

-----------------------------------------------Start Copying Just
Below This Line-----------------------------------------

Sub CustomPaste()

Dim CopySheet As Worksheet
Dim PasteSheet As Worksheet
Dim CurRng As Range
Dim CopyRng As Range 'Not used, future improvement
Dim PasteRng As Range 'Not used, future improvement

On Error GoTo ErrCustomPaste

'Hold the currently selected cell to return to at end of routine
Set CurRng = Application.ActiveCell
'Hold the values of the sheets to be used in variables (improve
performance)
Set CopySheet = Sheets("Sheet1")
Set PasteSheet = Sheets("Sheet2")

'Freeze the screen to improve performance
Application.ScreenUpdating = False
'Copy and Paste the information
With CopySheet
.Select
.Range("A1:A2").Copy PasteSheet.Range("B1:B2")
End With
With CopySheet
.Select
.Range("A3:A4").Copy PasteSheet.Range("C10:C11")
End With
With CopySheet
.Select
.Range("A5:A6").Copy PasteSheet.Range("D12")
End With
'Return to the originally selected cell
CurRng.Select
'Refresh the screen to update changes
Application.ScreenUpdating = True
Exit Sub

ErrCustomPaste:
MsgBox "An unknown error ocurred within the CustomPaste
procedure.", vbExclamation, _
"Custom Paste Abort!"
CurRng.Select
Application.ScreenUpdating = True
End Sub

-----------------------------------------------Stop Copying Just Above
This Line-----------------------------------------

Save your workbook now!

Now go back to the Excel Window and select: Tools...Macro--->Macros

There should be a window that appears with an option to run
"CustomPaste"

Select it and click Run

If you cannot, then more than likely, we will have to change your
Macro Security Settings before we continue...

Lemme know how it goes...