Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying from variable named sheet...
Below is code that I found on a site by Ron De Bruin. It seems to work well for what I am asking it to, but I am curious if there is a way to speed it up. The folder that it is pulling information from has several hundred files and it takes a great deal of time. I'm not sure if perhaps I do not have the right code for this type of process or if there is anything I can do other than sit and wait for it to update. Also I was wondering if there is a way to modify it to only update new files.... Help is appreciated. Thanks! Sub Example1() Dim basebook As Workbook Dim mybook As Workbook Dim rnum As Long Dim FNames As String Dim MyPath As String Dim SaveDriveDir As String Dim Cnum As Integer Dim cell As Range SaveDriveDir = CurDir MyPath = "C:\Documents and Settings...." ChDrive MyPath ChDir MyPath FNames = Dir("*.xls") If Len(FNames) = 0 Then MsgBox "No files in the Directory" ChDrive SaveDriveDir ChDir SaveDriveDir Exit Sub End If Application.ScreenUpdating = False Set basebook = ThisWorkbook 'clear all cells on the first sheet 'basebook.Worksheets(1).Cells.Clear rnum = 2 Do While FNames < "" Set mybook = Workbooks.Open(FNames) Cnum = 1 For Each cell In mybook.Worksheets(1).Range("D4,I4,C6,D6") basebook.Worksheets(1).Cells(rnum, Cnum).Value = cell.Value Cnum = Cnum + 1 Next cell mybook.Close False rnum = rnum + 1 FNames = Dir() Loop ChDrive SaveDriveDir ChDir SaveDriveDir Application.ScreenUpdating = False End Sub -- shikamikamoomoo ------------------------------------------------------------------------ shikamikamoomoo's Profile: http://www.excelforum.com/member.php...o&userid=21018 View this thread: http://www.excelforum.com/showthread...hreadid=525078 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
supress prompt for named ranges when copying sheet to new workbook | Excel Programming | |||
Copying named ranges from one sheet to another | Excel Programming | |||
Macro for copying named range to any sheet | Excel Programming | |||
Can A Named Range Be A Variable? | Excel Programming | |||
Copying an array variable to a sheet | Excel Programming |