Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default Help adding info from Multiple Books to 1 sheet.

I'm trying to add code to the following that will allow me to open
another file and copy the same information from cell D1 into my
orderform.xls starting at A2 then A3 and so on for as many files that
are renamed.

He is a copy of the code I currently have.

Thanks for the Help.



Sub CompileCabinets()

Dim Example()
Dim mybook As Workbook
Dim N As Long
Dim MyPath As String
Dim SaveDriveDir As String
Dim FName As Variant


SaveDriveDir = CurDir
MyPath = "C:\Sample\"
ChDrive MyPath
ChDir MyPath


FName = Application.GetOpenFilename(filefilter:="Excel Files
(*.xls), *.xls", _
MultiSelect:=True)

If IsArray(FName) Then
Application.ScreenUpdating = False


For N = LBound(FName) To UBound(FName)
Set mybook = Workbooks.Open(FName(N))

Range("E28").Select
Selection.TextToColumns Destination:=Range("E28"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False,
Other:=True, OtherChar _
:=".", FieldInfo:=Array(Array(1, 1), Array(2, 9)),
TrailingMinusNumbers:=True
Range("E28").Select
Selection.Copy

Range("R40").Select
ActiveSheet.Paste

Columns("D:N").Select
Range("D24").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("E:F").Select
Range("E24").Activate
Selection.Delete Shift:=xlToLeft
Columns("A:A").Select
Range("A24").Activate
Selection.Delete Shift:=xlToLeft

Rows("1:39").Select
Selection.Delete Shift:=xlUp

Columns("A:A").Select
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Cells.Select
Selection.Sort Key1:=Range("A1"),
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Rows("2:3").Select
Selection.Delete Shift:=xlUp

mybook.SaveAs Sheets(1).Range("D1") & ".xls"
mybook.Close False
Next
End If
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True

End Sub

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
need a function that will work using multiple work books and sheet capt c Excel Worksheet Functions 1 March 30th 09 10:20 PM
How do I match 2 cols on two books and get price info for all matc Sonny Excel Worksheet Functions 1 August 26th 06 02:45 PM
Opening Multiple files and Copying the info all to one other sheet MsLucy Excel Discussion (Misc queries) 2 January 6th 06 06:41 PM
combine multiple sheets, each sheet has info in different cells espo Setting up and Configuration of Excel 1 September 10th 05 01:18 PM
Showing Multiple Sheet Info Glenn Excel Programming 0 June 4th 04 02:15 PM


All times are GMT +1. The time now is 03:38 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"