type mismatch

[k_babb]

hi i need to copy columns from sheet1 to sheet2 in a certain order i have this macro it works fine with 4 or 5 entry but i need to enter this amount of entrys and i get an error runtime error 13 type mismatch how can i resolve this problem

A,z,B,z,C,z,z,D,z,z,z,z,z,z,z,z,z,z,z,z,E,F,z,z,G,z,z,z,z,z,z,H,z,z,z,z,z,z,z,z,z,z,z,z,

z,z,z,z,z,z,z,z,z,z,z,z,z,z,z,z,z,z,z,z,z,z,z,z,z,z,z,z,z,j,z

Sub copypaste()
Dim s As String, i As Long, X

s = InputBox("Enter From column letters separated by comma, e.g. A,C,AA")
X = Split(s, ",")
For i = LBound(X) To UBound(X)
    Sheets("Sheet1").Columns(X(i)).Copy Destination:=Sheets("Sheet2").Cells(1, i + 1)
Next i
deleteheader
End Sub

incase anyone can improve my code z is a blank column that i am using to space the columns so they are in the correct placement

 

[VoG]

On which line of code does the error occur?

 

[k_babb]

this one

Sheets("Sheet1").Columns(X(i)).Copy Destination:=Sheets("Sheet2").Cells(1, i + 1)</pre>

 

[VoG]

I cannot immediately see why that would fail.Please post the complete code.

 

[k_babb]

Sub copypaste()
Dim s As String, i As Long, X

s = InputBox("Enter From column letters separated by comma, e.g. A,C,AA")
X = Split(s, ",")
For i = LBound(X) To UBound(X)
    Sheets("Sheet1").Columns(X(i)).Copy Destination:=Sheets("Sheet2").Cells(1, i + 1)
Next i
deleteheader
End Sub

Sub deleteheader()
Sheets("Sheet2").Select
Rows("1:1").Select
Selection.Delete Shift:=xlUp
part
End Sub

Sub part()
Static wb2path As String: wb2path = Application.GetOpenFilename("Excel files, *.xls*")
If wb2path = "False" Then Exit Sub

Static wb1 As Workbook: Set wb1 = ActiveWorkbook
Static wb2 As Workbook: Set wb2 = Workbooks.Open(wb2path)






wb2.Activate
Sheets("sheet1").Select
Range("A2:bZ30000").Select
    Selection.ClearContents
    Range("A1").Select

wb1.Sheets("Sheet2").UsedRange.Copy Destination:=wb2.Sheets("Sheet1").Cells(2, 1)
wb1.Activate
Sheets("sheet2").Select
Cells.Select
Selection.ClearContents
Range("A1").Select

wb2.Save
wb2.Close
wb1.Activate
Sheets("sheet1").Select



End Sub

 

[k_babb]

it seems ok with a few entries but does not like a large amount of data

 

[P.Holko]

Your code works fine. The error probably results from presence of spaces or additional comas in input.

Try this (note that code below do not solve problem with additional comas):

Sub copypaste()
Dim s As String, i As Long, X

s = InputBox("Enter From column letters separated by comma, e.g. A,C,AA")

i = 0

Do While i <= VBA.Len(s)
    i = i + 1
    If VBA.Mid(s, i, 1) = " " Then
        s = Left(s, i - 1) & Right(s, VBA.Len(s) - i)
        i = i - 1
    End If
Loop
s = Trim(s)

X = Split(s, ",")
For i = LBound(X) To UBound(X)
    Sheets("Sheet1").Columns(X(i)).Copy Destination:=Sheets("Sheet2").Cells(1, i + 1)
Next i
End Sub

Hope this helps

 

[P.Holko]

additional code for input problem with comas:

 Sub copypaste()
Dim s As String, i As Long, X

s = InputBox("Enter From column letters separated by comma, e.g. A,C,AA")
If s = vbNullString Then Exit Sub

i = 0

s = Trim(s)
Do While i <= Len(s)
    i = i + 1
    If Mid(s, i, 1) = " " Then
        s = Left(s, i - 1) & Right(s, Len(s) - i)
        i = i - 1
    End If
Loop
i=0
Do While i <= Len(s)
    i = i + 1
    If Mid(s, i, 1) = "," And Mid(s, i + 1, 1) = "," Then
        s = Left(s, i - 1) & Right(s, Len(s) - i)
        i = i - 1
    End If
Loop

If Mid(s, Len(s), 1) = "," Then s = Mid(s, 1, Len(s) - 1)
If Mid(s, 1, 1) = "," Then s = Mid(s, 2, Len(s) - 1)

X = Split(s, ",")
For i = LBound(X) To UBound(X)
    Sheets("Sheet1").Columns(X(i)).Copy Destination:=Sheets("Sheet2").Cells(1, i + 1)
Next i

deleteheader
End Sub

 

[VoG]

I just tested this and it worked with no error

Sub copypaste()
Dim s As String, i As Long, X

s = InputBox("Enter From column letters separated by comma, e.g. A,C,AA")
X = Split(s, ",")
For i = LBound(X) To UBound(X)
    Sheets("Sheet1").Columns(X(i)).Copy Destination:=Sheets("Sheet2").Cells(1, i + 1)
Next i
End Sub

 

[VoG]

Actually, you will get an error if you enter A, B instead of A,B. Try

Sub copypaste()
Dim s As String, i As Long, X

s = InputBox("Enter From column letters separated by comma, e.g. A,C,AA")
X = Split(s, ",")
For i = LBound(X) To UBound(X)
    Sheets("Sheet1").Columns(Trim(X(i))).Copy Destination:=Sheets("Sheet2").Cells(1, i + 1)
Next i
End Sub