The code uses the worksheet name of the original worksheet to make the other
sheet names

sheet 1 = jln
then new sheets are
jln_1
jln_2
jln_3
jln_4


Sub Import_AA_Tabs()

Dim CurWks As Worksheet
Dim myWkbk As Workbook
Dim myFileName As Variant
Dim rng5 As Range
Dim rng6 As Range
Dim rng7 As Range
Dim rng8 As Range
Dim rng9 As Range
Dim rng As Range
Dim LastRow As Long



'code to get the name and open the .csv file
For fileNum = 1 To 4

ThisWorkbook.Activate
Sheets.Add ' add a blank sheet
Set CurWks = ActiveSheet 'or whatever you want it to be
ActiveSheet.Name = CurWks.Name & "_" & (fileNum + 1)


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

Next fileNum

End Sub


"jln via OfficeKB.com" wrote:

OK what i have is for spread sheets they are all named with a number_1 .
There are always4 files for each number. When each file imports I Need 1 to
be called sheet 2 2 to be 3 3 to be 4 and 4 to be 5. Sheet 1 is my template.
Here is the code im using to import one at a time.

Sub Import_AA_Tabs()


Dim CurWks As Worksheet
Dim myWkbk As Workbook
Dim myFileName As Variant
Dim rng5 As Range
Dim rng6 As Range
Dim rng7 As Range
Dim rng8 As Range
Dim rng9 As Range
Dim rng As Range
Dim LastRow As Long


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



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



End Sub

--
Message posted via http://www.officekb.com