Copy Cells in a loop from text file

[Anna112233]

Hello everyone!
I am new in VBA and I have problems with importing text and copying it in current worksheet.
I created a code that imports text file to last worksheet in current workbook, but an error "Type mismatch" appears when I try to copy it in worksheet 1.

Actually, I need to copy every cell in loop, but I couldn't copy any cell.

Please help me!!!

Picture 1 is the worksheet where I need to copy text file.
Picture 2 is already imported text file to last worksheet.

Here is my code:

Sub Transfer()
Dim mySource As String, wsName As String
Dim destsheet As Worksheet
Dim wbSource As Workbook
Dim wsDestin As Worksheet
Dim lrow As Long


Set wsDestin = ThisWorkbook.Sheets.Add(, ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) 'Add the worksheet at the end
On Error Resume Next
wsDestin.Name = wsName 'set the name
On Error GoTo 0

Application.DisplayAlerts = False
If InStr(wsDestin.Name, "Sheet") <> 0 Then wsDestin.Delete: Exit Sub
mySource = Application.GetOpenFilename("Text Files (*.txt),*.txt")

'If the user pressed Cancel or didn't select a text file, we exit.


'Switch screen updating off for speed.
Application.ScreenUpdating = False
Workbooks.OpenText Filename:=mySource, _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, _
Other:=False, TrailingMinusNumbers:=True, _
Local:=True

Set wbSource = ActiveWorkbook

With wsDestin
'--> get the last row of your destination sheet, i assumed you want Column A
lrow = .Range("A" & Rows.Count).End(xlUp).Row
'--> not comfortable in UsedRange but this should work, else define your range.
'--> i can't because, i can't see your actual data
wbSource.Sheets(1).UsedRange.Copy .Range("A" & lrow).Offset(0, 0)
wbSource.Close False
End With
Application.DisplayAlerts = True
Set destsheet = ActiveWorkbook.Worksheets("Ëèñò1")

Worksheets(wsDestin).Cells(2, 1).Copy Destination:=Worksheets("Ëèñò1").Cells(2, 1)


End Sub

 

[jimrward]

Wsname has no local value for a start so will be empty it is declared but not set

 

[Anna112233]

Wsname has no local value for a start so will be empty it is declared but not set

Actually this part is working. I have problems with copying
How should i set wsName?