Multiple Range (union) Copy-Paste

[Renaissance]

Greetings!

I am working on a simple macro that will copy a header row and other row within the same sheet and pasting it into a new sheet. This action repeats until all rows have been copied, however right now I am getting a "This action won't work on multiple selections" error '1004' and can't figure out why.

Here's the code

Sub Transpose()

    Dim cnt As Integer
    Dim i As Integer
    Dim clmns As Integer
    Dim range1 As Range
    Dim range2 As Range
    Dim myMultipleRange As Range
                
    Sheets("Sheet1").Activate
    clmns = Worksheets("Sheet1").Range("1:1").Cells.SpecialCells(xlCellTypeConstants).Count
    cnt = Worksheets("Sheet1").Range("B:B").Cells.SpecialCells(xlCellTypeConstants).Count
    Set range1 = Worksheets("Sheet1").Range("B1:B" & clmns)
        
    For i = 2 To cnt
        
        Set range2 = Sheets("Sheet1").Range(Cells(i, 2), Cells(i, clmns))
        Set myMultipleRange = Union(range1, range2)
        myMultipleRange.Select
        Selection.Copy
        Sheets.Add After:=ActiveSheet
        Range("B1").Select
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
        Sheets("Sheet1").Select
    Next i

End Sub

I'm assuming it's something simple and I've eyeballed code from a few others, but I appreciate any advice you can offer

~Renaissance

 

[AlphaFrog]

Set range1 = Worksheets("Sheet1").Range("B1:B" & [COLOR="#FF0000"]clmns[/COLOR])

The clmns variable is a Columns count, but it's being used as a Row argument. I think you want something like this...

Set range1 = Worksheets("Sheet1").Range("B1").Resize(1, clmns -1)

 

[Renaissance]

Wow, you nailed it! Thank you very much, AlphaFrog!! I knew it was something simple Have a nice weekend

 

[AlphaFrog]

You're welcome. Thanks for the feedback.

 

[Renaissance]

Last question: How do I edit the thread title to include *Solved*? Is that possible?

 

[AlphaFrog]

Last question: How do I edit the thread title to include *Solved*? Is that possible?

Nope. Just say something like; "thank you it's solved". That's it. You've already done it and thanks for the Like.