Copy a range and paste into another workbook - files are password protected.

[acsishere]

Dear Friends,

I need to copy and paste a range from one workbook to another workbook automatically.

Basically, I have 180 files in a folder. The details are:
1) 90 master files
2) from these a range named "COPYTOE" has to be copied and pasted into another 90 report files.
3) I have list of files & its passwords in a separate file named FNAME.XLS. In this, Sheet1, A1:C the details consist like this:
MSFILE1.XLS / REPFILE1.XLS / PASSWORD1
MSFILE2.XLS / REPFILE2.XLS / PASSWORD2
MSFILE3.XLS / REPFILE3.XLS / PASSWORD3 ... till 90 files
(the No.of files may go up if needed)
(Passwords for both first files of Master & Report file are same)

4) Now, I need a macro, which run from a separate file, to do the following:
The macro should first open FNAME.XLS and then to check the filename in ColA1 and password in CellC1 and, if right, then open the first master file to copy COPYTOE range and then check CellB1 for filename & Password for CellC1, if right, then to paste from A4 in Report File.

For Example:
Data already entered in MSFILE1.XLS. It's password is "Password1". Now, I am to copy the range COPYTOE from this file and the same should be pasted into REPFILE1.XLS (It's password also the same).

This should be looped till the last filename exist in sheet1 of Fname.xls.

As always, I hope someone will help me to solve this problem.

Thanks in advance.

acsishere.

[acsishere]

Any help is highly appreciated.

Thanks in advance.

acsishere.

[acsishere]

I am very much depending on the solution, because I am to submit the report on Monday only after incorporating the macro and then to generate the report.

Please.... It's urgent. Any one will give me a helping hand? Please Friends....

Thanks in advance.

acsishere.

[acsishere]

I am very much in need of your help.

Any help is highly appreciated.

Thanks in advance,

acsishere.

[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 IN ALL FILES ARE MERGED IN THIS SHEET.  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? Please....

Thanks in advance.

acsishere.

[acsishere]

Any help?

Thanks in advance.

acsishere.

[acsishere]

I'm badly in need of your support.

Please...

Thanks in advance.

acsishere.

[acsishere]

I still hope some one will help me.

If you need some more information/clarification please let me know.

It's urgent. Help me please...

acsishere.

[broro183]

hi, try the modified but untested version below, does it do what you want?
also, your column layout seems to have changed from your initial post, so I hope I have it the right way around.

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 myRpFile as string    
Dim myRealWkbkName As String
    Dim LastRowA1 As Long
    Dim LastRowB1 As Long
    Dim PWWorkbook As Workbook
    Dim SourceWorkbook As Workbook
dim PasteWorkbook 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
myRpFile = PWWorkbook.Sheets(1).Cells(i, 3).Value
        
        Workbooks.Open WorkbookPath & myFileNames, UpdateLinks:=False, Password:=myPasswords
        Set SourceWorkbook = ActiveWorkbook

       Workbooks.Open WorkbookPath & myRpFile, UpdateLinks:=False, Password:=myPasswords
        Set PasteWorkbook = ActiveWorkbook
with SourceWorkbook 
        .Range("E1COPYAREA").ClearContents
        Set rng = .Range("COPYTOE")
end with
        rng.Copy
with PasteWorkbook.Sheets("EVALUATION1")
.Range("A4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        application.goto .Range("G4")
end with
      SourceWorkbook.Close SaveChanges:=True
PasteWorkbook.Close SaveChanges:=True

'free memory
set SourceWorkbook = nothing
set PasteWorkbook = nothing
          
    Next i
    
    PWWorkbook.Close SaveChanges:=False
    set PWWorkbook = nothing
    Application.ScreenUpdating = True
'Im not sure what file this is meant to be...?  
  Range("A1").Select
    MsgBox "DATA IN ALL FILES ARE MERGED IN THIS SHEET.  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? Please....

Thanks in advance.

acsishere.

hth
Rob

[acsishere]

I am very much in need of your help.

Please.... and it's very urgent.

I request your kind help.

Thanks in advance.

acsishere.

[acsishere]

Thanks for your modified code. It seems that it will work nicely. But when I did some adjustments, it shows some error message (No. 438).

Hence, I am furnishing more details in the attachment workbook (ListFN.XLS) which gives more clarifications.

The macro should run from Runcode.xls.

I sincerely seek your kind support.

Thanks in advance. acsishere.

[broro183]

hi,

Neither of the two files that you have attached include any code therefore no-one can see what "adjustments" you have made to my suggested (untested) code, which in turn makes it hard for us to understand what is going wrong...

Can you please reattach the files with the modified code & comments stating what the errrors are & where in the code they occur?

Unfortunately, I don't think I'll get another chance to look at this before work tomorrow (unless you're very quick with the attachment b/c I'm off to bed soon) - hopefully someone else will be able to help.

Rob