PDA

View Full Version : Copy Files in a folder into a single Master file.



acsishere
07-25-2008, 11:16 AM
Best Wishes! Dear Friends,

This is with ref. to my own (solved) thread http://www.vbaexpress.com/forum/showthread.php?t=20967 in which the following code is written by Mr. Mdmackillop:




Sub COPYTOE1()
' THIS CODE COPIES DATA FROM MASTERSHEET TO EVALUATION1 SHEET



Dim myFileNames As String
Dim myPasswords As String
Dim myRealWkbkName As String
Dim LastRowA1 As Long
Dim LastRowB1 As Long
Dim PWWorkbook As Workbook
Dim SourceWorkbook As Workbook
Dim WorkbookPath As String
Dim i As Integer
Dim LastRow As Long
Dim LastColumn As Long
Dim rng As Range


Application.ScreenUpdating = False


myRealWkbkName = "C:\TEST\FNAME.xls"
WorkbookPath = "C:\TEST\USER\"


Workbooks.Open myRealWkbkName, UpdateLinks:=False
Set PWWorkbook = ActiveWorkbook


LastRowA1 = PWWorkbook.Sheets("Sheet1").[A1].End(xlDown).Row
LastRowB1 = PWWorkbook.Sheets("Sheet1").[B1].End(xlDown).Row


If LastRowA1 <> LastRowB1 Then
MsgBox "check names & passwords--qty mismatch!"
Exit Sub
End If

For i = 1 To LastRowA1

myFileNames = PWWorkbook.Sheets(1).Cells(i, 1).Value
myPasswords = PWWorkbook.Sheets(1).Cells(i, 2).Value


Workbooks.Open WorkbookPath & myFileNames, UpdateLinks:=False, Password:=myPasswords
Set SourceWorkbook = ActiveWorkbook

Range("E1COPYAREA").ClearContents
Set rng = Range("COPYTOE")

rng.Copy
Sheets("EVALUATION1").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("G4").Select
SourceWorkbook.Close SaveChanges:=True

Next i

PWWorkbook.Close SaveChanges:=False


Application.ScreenUpdating = True
Range("A1").Select
MsgBox "DATA FROM ALL FILES ARE COPIED IN RELATED FILES. THANK YOU", vbOKOnly

End Sub



Actually the above code copies data from one workbook to another workbook.

I need a slight modification in this that the target workbook is only one Masterworkbook (from which I run the macro) in which all data from sourceworkbooks should be pasted.

Any help is highly appreciated.

Thanks in advance. acsishere.

pvanrooijen
07-25-2008, 02:04 PM
Hello acsishere

I have used CombineFiles from the knowledge base.

Hope this helps you on the way.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=829

pvanrooijen
07-25-2008, 02:11 PM
Sorry, the mentioned routine does not work on protected sheets.
As I suspect that is the case?

mdmackillop
07-25-2008, 02:30 PM
Give this (http://www.vbaexpress.com/forum/showthread.php?goto=newpost&t=20989)a try.