#1
|
|||
|
|||
simple copy and paste trick?
Hi
I would like to know if you can copy 6 columns and then paste it in order in 2 columns? I have attached a picture as example Thanks |
#2
|
|||
|
|||
You can't.
Or more precise - you can when you edit a table a bit at first, ant then copy and paste pair-wise (7 times for your example) |
#3
|
|||
|
|||
Any interest in a macro solution for this ?
|
#4
|
|||
|
|||
|
#5
|
|||
|
|||
Code:
Sub CopySelectedRange() On Error Resume Next 'incase input box canceled Set rng = Application.InputBox("Use the mouse to select the range to copy", Type:=8) If rng Is Nothing Then Exit Sub Set PasteHere = Application.InputBox("Use the mouse to select the upper left cell for the paste", Type:=8) If PasteHere Is Nothing Then Exit Sub On Error GoTo 0 'turn error notification back on For Each cel In rng If cel.Value <> "" Then PasteHere.Offset(i, j) = cel.Value j = j + 1 If j = 2 Then j = 0 i = i + 1 End If End If Next cel End Sub |
#6
|
|||
|
|||
Quote:
but, if it's not much trouble, can you make a macro for 7 columns? like in example |
#7
|
|||
|
|||
Try this, it asks the number of columns you want to paste to.
Code:
Sub CopySelectedRange() Dim rng As Range, cel As Range Dim PasteHere As Range, NumCols As Integer On Error Resume Next 'incase input box canceled Set rng = Application.InputBox("Use the mouse to select the range to copy", Type:=8) If rng Is Nothing Then Exit Sub Set PasteHere = Application.InputBox("Use the mouse to select the upper left cell for the paste", Type:=8) If PasteHere Is Nothing Then Exit Sub NumCols = Application.InputBox("How many columns should be used for the paste?") If Not IsNumeric(NumCols) Or NumCols = 0 Then Exit Sub On Error GoTo 0 'turn error notification back on For Each cel In rng If cel.Value <> "" Then PasteHere.Offset(i, j) = cel.Value j = j + 1 If j = NumCols Then j = 0 i = i + 1 End If End If Next cel End Sub |
#8
|
|||
|
|||
[QUOTE=NoSparks;127389]Try this, it asks the number of columns you want to paste to.
That's it! Many Thanks |
#9
|
|||
|
|||
You're welcome, glad I could help.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Simple macro to create a copy | eduzs | Word VBA | 3 | 05-17-2017 05:34 PM |
When I copy&paste a second copy appears that can't be edited | makeo22 | Word | 3 | 04-26-2017 07:09 PM |
Copy / paste | BoyertownCasket | Excel | 4 | 04-19-2016 12:32 AM |
Paste and Copy | Quadro | Excel | 4 | 07-15-2014 05:59 AM |
Paste Special: Copy and Paste Formatting Only? | tinfanide | Word | 6 | 03-06-2013 12:21 AM |