inputing data

[Guest]

hi how can i modify this code so that the data is entered in the next empty
cell in
column a

Sub ImportDataFile()
Dim myFName As Variant
Dim myWkSht As Worksheet

Set myWkSht = ActiveSheet

ChDir "C:\mydata"
myFName = Application.GetOpenFilename(, , "Select the Data File")

If myFName = False Then
MsgBox "You pressed Cancel"
Exit Sub
Else
Workbooks.OpenText Filename:=myFName, _
StartRow:=1, DataType:=xlDelimited, Comma:=True
End If

Range("A").Copy _
myWkSht.Range("A")
ActiveWorkbook.Close False
'
End Sub

thanks for the help guys

 

[Dave Peterson]

Untested...

Option Explicit
Sub ImportDataFile()

Dim myFName As Variant
Dim myWkSht As Worksheet
Dim DestCell As Range

ChDir "C:\mydata"
myFName = Application.GetOpenFilename(, , "Select the Data File")

If myFName = False Then
MsgBox "You pressed Cancel"
Exit Sub
End If

Set myWkSht = ActiveSheet

With myWkSht
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

Workbooks.OpenText Filename:=myFName, _
StartRow:=1, DataType:=xlDelimited, Comma:=True

ActiveSheet.Range("a1").CurrentRegion.Copy _
Destination:=DestCell

ActiveWorkbook.Close savechanges:=False

End Sub

 

[Guest]

You can select cell A1 in the activeworksheet and then use the code below to
move to the next blank cell, if A1 is not empty

If ActiveCell.Value <> "" Then
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
End If