simple copy and paste trick?

Urraco · #1 04-19-2018, 03:30 AM

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

ArviLaanemets · #2 04-19-2018, 04:13 AM

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)

NoSparks · #3 04-19-2018, 06:33 AM

Any interest in a macro solution for this ?

Urraco · #4 04-19-2018, 06:39 AM

Quote:

Originally Posted by NoSparks

Any interest in a macro solution for this ?

Of course, please

NoSparks · #5 04-19-2018, 07:50 AM

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

Urraco · #6 04-20-2018, 01:02 AM

Quote:

Originally Posted by NoSparks

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

Works .Many thanks
but, if it's not much trouble, can you make a macro for 7 columns?
like in example

NoSparks · #7 04-20-2018, 06:08 AM

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

Urraco · #8 04-20-2018, 06:15 AM

[QUOTE=NoSparks;127389]Try this, it asks the number of columns you want to paste to.

That's it!
Many Thanks

NoSparks · #9 04-20-2018, 06:19 AM

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