macro to copy and paste

[jln via OfficeKB.com]

What im trying to do is this. Have a macro run that opens imports a selected
file, then have it copy and paste into a set cell range of another sheet.

I have it working so that i can select a file and put the file on a new sheet
in my template workbook.

Im having problems geting all the cells that have data. So what i need is
something to count the row that have data. I want to skip the first row since
it has headers.

this is the code I have so far.


Dim CurWks As Worksheet
Dim myWkbk As Workbook
Dim myFileName As Variant

Dim LastRow As Long
Dim rng As Range
Dim rng1 As Range

Sheets.Add ' add a blank sheet


Set CurWks = ActiveSheet 'or whatever you want it to be
'code to get the name and open the .csv file

CurWks.Name = "Import"


myFileName = Application.GetOpenFilename(filefilter:="xls Files, *.xls",
_
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.Open Filename:=myFileName '....rest of recorded code here!

Set myWkbk = ActiveWorkbook

myWkbk.Worksheets(1).UsedRange.Copy _
Destination:=CurWks.Range("a1")
'(Paste into A1 of the original sheet????)

'close the .csv file without making changes
myWkbk.Close savechanges:=False

Set rng = CurWks.Cells(Rows.Count, 2).End(xlUp)(2)



ActiveWindow.SelectedSheets.Delete

 

[Guest]

This code may help

Sub xyz()

Set abc = ActiveSheet

usedRow = abc.UsedRange.Row
usedCol = abc.UsedRange.Column

End Sub