acsishere
07-18-2008, 10:04 AM
Dear Friends,
I have a code which does the following:
It runs from a separate file. It opens the Fname.XLS and see the file name & it's password, if right, then opens the source file (where the data is avaiable) to copy and then opens the Evaluation1 sheet in the same file to paste.
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
I need the modification in the above code to do the following:
In Fname.Xls, ColA data consist MASFILEs. ColB data consists Passwords. ColC consists REPFILEs (Masfile1 & Repfile1 are having similar password PASSWORD1 and Masfile2 & Repfile2 password is PASSWORD2 and so on...)
The above code copies & pastes the Range within the same workbook. I need to copy Range COPYTOE and then to paste in another workbook (the related REPFILE).
Any help highly appreciated. Please....help me to find the solution.
Thanks in advance.
acsishere.
I have a code which does the following:
It runs from a separate file. It opens the Fname.XLS and see the file name & it's password, if right, then opens the source file (where the data is avaiable) to copy and then opens the Evaluation1 sheet in the same file to paste.
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
I need the modification in the above code to do the following:
In Fname.Xls, ColA data consist MASFILEs. ColB data consists Passwords. ColC consists REPFILEs (Masfile1 & Repfile1 are having similar password PASSWORD1 and Masfile2 & Repfile2 password is PASSWORD2 and so on...)
The above code copies & pastes the Range within the same workbook. I need to copy Range COPYTOE and then to paste in another workbook (the related REPFILE).
Any help highly appreciated. Please....help me to find the solution.
Thanks in advance.
acsishere.