VBA code issue copy multiple cells and transpose paste in different sheet

Marius Nielsen

New Member
Joined
Feb 12, 2014
Messages
10
I'm quite new to VBA and have a code which is not working correctly. Its working for a singel cell but not multiple cells selections.
Please note that selcted cells to be copied are merged cells so wonder if this is causing the issue.

Sub Main()
Call Nummber
Call Mail_ActiveSheet
Dim NextRow As Range
With Sheets("Database")
Set NextRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0)
End With
ThisWorkbook.Sheets("Ocean Export FCL").Range("e6,e5,e2,e1,e3,e4,e7,t2,t3,t4,af4,t5,t6,af6,ad19,y27").Copy
NextRow.PasteSpecial Paste:=xlValues, Transpose:=True
Application.CutCopyMode = False
Set NextRow = Nothing

ThisWorkbook.Save

Application.Quit
End Sub

Any chance for some help on this issue ? Thanks in advance.
 

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,720
If you try that manually you will get an error message, "That command cannot be used on multiple selections". Which is why you are getting the error when you use the above code.
I believe this code will do what you are looking for:

Code:
Sub CopyTest()

    Dim rng As Range
    Dim rngCell As Range
    Dim rngTarget As Range
    Dim lCount As Long
    Dim lX As Long
    
    With Sheets("Database")
        'The next empty cell in column B would be:
        Set rngTarget = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0)
    End With
    
    Set rng = ThisWorkbook.Sheets("Ocean Export FCL").Range("e6,e5,e2,e1,e3,e4,e7,t2,t3,t4,af4,t5,t6,af6,ad19,y27")
    For Each rngCell In rng
        lX = lX + 1
        'Debug.Print rngCell.Address, rngCell.Value, rngTarget.Offset(0, lX - 1).Address, rngTarget.Offset(lX - 1, 0).Address
        rngTarget.Offset(0, lX - 1).Value = rngCell.Value 'For horzontal output
        'rngTarget.Offset(lX - 1, 0).Value = rngCell.Value 'For vertical output
    Next

End Sub
 

Marius Nielsen

New Member
Joined
Feb 12, 2014
Messages
10
If you try that manually you will get an error message, "That command cannot be used on multiple selections". Which is why you are getting the error when you use the above code.
I believe this code will do what you are looking for:

Code:
Sub CopyTest()

    Dim rng As Range
    Dim rngCell As Range
    Dim rngTarget As Range
    Dim lCount As Long
    Dim lX As Long
    
    With Sheets("Database")
        'The next empty cell in column B would be:
        Set rngTarget = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0)
    End With
    
    Set rng = ThisWorkbook.Sheets("Ocean Export FCL").Range("e6,e5,e2,e1,e3,e4,e7,t2,t3,t4,af4,t5,t6,af6,ad19,y27")
    For Each rngCell In rng
        lX = lX + 1
        'Debug.Print rngCell.Address, rngCell.Value, rngTarget.Offset(0, lX - 1).Address, rngTarget.Offset(lX - 1, 0).Address
        rngTarget.Offset(0, lX - 1).Value = rngCell.Value 'For horzontal output
        'rngTarget.Offset(lX - 1, 0).Value = rngCell.Value 'For vertical output
    Next

End Sub

This worked perfectly. Thanks for your support !
 

Forum statistics

Threads
1,082,048
Messages
5,362,890
Members
400,696
Latest member
Kclynn

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top