When I run your code its not adding a sheet or pasting it. I dont get an
error either. I have steped though the code as well and i dont see the sheet
getting added or the pasted.
Joel wrote:
I made some changges so you only have to select 1 file name and it will get
the other 3 files
Sub Import_AA_Tabs()
Dim CurWks As Worksheet
Dim MyWkbk As Workbook
Dim MyFilename As String
Dim MyPath As String
Dim NewFileName As String
Dim RootFileName As String
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
MyFilename = Application. _
GetOpenFilename(filefilter:="xls Files, *.Xls", _
Title:="Pick a File (any file _1, _2, _3, _4")
MyPath = ""
Do While InStr(MyFilename, "\") 0
MyPath = MyPath & Left(MyFilename, InStr(MyFilename, "\"))
MyFilename = Mid(MyFilename, InStr(MyFilename, "\") + 1)
Loop
'remove _1 and extension from filename
If InStr(MyFilename, "_") 0 Then
RootFileName = Left(MyFilename, InStr(MyFilename, "_") - 1)
Else
RootFileName = Left(MyFilename, InStr(MyFilename, ".") - 1)
End If
'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 = RootFileName & "_" & (filenum)
NewFileName = MyPath & RootFileName & "_" & _
(filenum) & ".xls"
If Dir(NewFileName) = "" Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If
Workbooks.Open Filename:=NewFileName '....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
The worksheet name works the way it is, what im really trying to get is how
get just the 4 files that i need? the file names look like this 641_1, 641_2,
[quoted text clipped - 61 lines]
End Sub
--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200709/1