Copy data only from multiple workbooks to Workbook with mulitple sheets using Macros

[nkkhil]

Hi All,

I am new to this forum so apologies if I am asking simple questions!

I have an urgent problem which I have been trying to code for weeks but unfortunately my VB skills at basic to put it kindly.

Excel version: Excel 2003

I have one workbook c:\Documents and settings\khilan.dhanani\Summary Weekly Report\Weekly Report.xls (master) which contains the following sheets:

Summary
Minor Works Projects
Optel T and T
Remote Asset
Control Telephony
Livelink service
.............etc there are 25 sheets in total

I then have separate workbooks which are distributed for update containing 1 worksheet for each project:

c:\Documents and settings\khilan.dhanani\Weekly Reports\Minor Works Projects.xls
c:\Documents and settings\khilan.dhanani\Weekly Reports\Optel T and T.xls
c:\Documents and settings\khilan.dhanani\Weekly Reports\Remote Asset.xls
c:\Documents and settings\khilan.dhanani\Weekly Reports\Control Telephony.xls
..............etc there are 24 workbooks in total

The main summary workbook (master) is password protected as are the sheets.

What I need is a macro which switches the protection of then loops through each workbook and copies the values only from each work book in the range of B2:N34 where the project names on the worksheets match. The reason why I only want the values copied is because the formatting is all held in the master spreadsheet and also each project feeds into the summary sheet and copying the who sheet new seems to break the link and formulas

So in simple terms in need the following

Where Workbook (master). Remote Asset = Workbook 3.Remote Asset then copy/paste (values only) range B2:N34

Hope that makes sense. I can upload a copy of the workbooks if needed.

Will really, really, really appreciate any help with this

Thank you in advance,

Khilan

[nO_OnE]

Where exactly are you planning on running this from, the "master"? Are any of these spreadsheets going to be open? What is password protected, just the "Master", is it protected from opening, or just protection on the sheets, or both? Are they all the same passwords? Are any of the other sheets password protected?

[nkkhil]

Hi nO_OnE and thank you for the prompt reply.

I would like to run the macro from the "master" spreadsheets which will be the only spreadsheet open. All the other spreadsheet will be stored in one network folder.

The master workbook is protected to only allow users to update certain fields so each sheet is protected as are the sheets all other workbooks. All the wokbooks and sheets are protected using the same password.

Hopefully that makes sense. Let me know if you need any further information

[nO_OnE]

Try this code:
You need to change the "Password" to the your the actual password (Make sure you leave the "Quotations" on your password).
Check the directories
This also assumes that you do not want the 'Summary' sheet copied (you didn't have it listed in your first post)

I haven't actually tested this code, so let me know if there is a problem, and where it is...

Public Sub CopyMaster()
    Dim Pass As String
        Pass = "Password"
    Dim DirectoryPath As String
        DirectoryPath = "c:\Documents and settings\khilan.dhanani\Summary Weekly Report\"
    
    Dim wb As Workbook
        Set wb = Workbooks("Weekly Report.xls")
    
    Dim sht As Worksheet
    Dim wbtmp As Workbook
    Dim shttmp As String
        
    For Each sht In wb
        'This assumes you do not want to copy the "summary" sheet, and skips it
        If sht.Name = Summary Then
            Next sht
        End If
        
        Dim wbnm As String
            wbnm = sht.Name & ".xls"
        
        'This will unprotect the sheet
        sht.Unprotect Pass
        
        'This opens the workbook that has the same name as The sheet name
        Workbooks.Open (DirectoryPath & wbnm)
        Set wbtemp = (wbnm)
    
        'This copys the current sheet values into memory
        sht.Range("B2:N34").Copy
 
        'This pastes the copied range's values into "Sheet1" of the target workbook
        'starting at cell "B2"
        wbtemp.Worksheets("Sheet1").Range("B2").PasteSpecial xlPasteValues
        
        'This saves and closes the target workbook
        wbtemp.Save
        wbtemp.Close
        
        'This will re-protect the sheet with the same password
        sht.Protect Pass
        
    'moves to the next sheet
    Next sht
 
 
End Sub

[nkkhil]

Hi nO_OnE

Sorry about the delayed reply and thank you so much for taking the time to help me with this, your efforts are really appreciated.

I have added the code but seem to get a compile error : Next without For
on line no.17

Any ideas?

[nO_OnE]

Try this instead...

Public Sub CopyMaster()
    Dim Pass As String
        Pass = "Password"
    Dim DirectoryPath As String
        DirectoryPath = "c:\Documents and settings\khilan.dhanani\Summary Weekly Report\"
    
    Dim wb As Workbook
        Set wb = Workbooks("Weekly Report.xls")
    
    Dim sht As Worksheet
    Dim wbtmp As Workbook
    Dim shttmp As String
 
    For Each sht In wb
        'This assumes you do not want to copy the "summary" sheet, and skips it
        If sht.Name <> "Summary" Then
        
            Dim wbnm As String
                wbnm = sht.Name & ".xls"
        
            'This will unprotect the sheet
            sht.Unprotect Pass
        
            'This opens the workbook that has the same name as The sheet name
            Workbooks.Open (DirectoryPath & wbnm)
            Set wbtemp = (wbnm)
    
            'This copys the current sheet values into memory
            sht.Range("B2:N34").Copy
 
            'This pastes the copied range's values into "Sheet1" of the target workbook
            'starting at cell "B2"
            wbtemp.Worksheets("Sheet1").Range("B2").PasteSpecial xlPasteValues
        
            'This saves and closes the target workbook
            wbtemp.Save
            wbtemp.Close
        
            'This will re-protect the sheet with the same password
            sht.Protect Pass
        
        End If
    'moves to the next sheet
    Next sht
End Sub

[nkkhil]

Hi nO_OnE.

Thanks for the updated code

I have added the code but seem to get a compile error : Object required
on line no.26 (wbnm is highlighted)

Any ideas?

[nO_OnE]

Change that line to:

       Set wbtemp = Woorkbooks(wbnm)

[nkkhil]

Runtime Error 438 - Object doesn't support this property or method

Line 14 . For each sht in wb is highlighted when debugging

[kaliman79912]

Just a thoght. Why dont you use direct references or queries to the original workbooks instead of trying to copy the values?

[nkkhil]

Unfortunately due to the distribution of the master spreadsheet once populated it is not possible to query or reference the data.

I agree though it would have been alot easier to do so.

[nO_OnE]

Ok, I see the problem there...

Line 14 should be:

For Each sht In wb.sheets

I seen another problem, which is the source file, and which is the target? are you copying the information from the "Master" to the individual workbooks, or are you copying the data from the other workbooks to the "Master"?

[nkkhil]

Thanks i'll try that.

I am copying the data from the separate workbooks to the master which can be stored in two separate locations/folders or the same folder whichever is easier

[nO_OnE]

Change the "Password" to the actual password (remember to keep the "")

This also assumes that the data is on "Sheet1" of the source workbooks.

Public Sub CopyMaster()
    Dim Pass As String
        Pass = "Password"
    Dim DirectoryPath As String
        DirectoryPath = "c:\Documents and settings\khilan.dhanani\Summary Weekly Report\"
    
    Dim wb As Workbook
        Set wb = Workbooks("Weekly Report.xls")
    
    Dim sht As Worksheet
    Dim wbtmp As Workbook
    Dim shttmp As String
 
    For Each sht In wb.Sheets
        If sht.Name <> "Summary" Then
            Dim wbnm As String
                wbnm = sht.Name & ".xls"
            sht.Unprotect Pass
            Workbooks.Open (DirectoryPath & wbnm)
            Set wbtemp = Workbooks(wbnm)
            wbtemp.Worksheets("Sheet1").Range("B2:N34").Copy
            sht.Range("B2").PasteSpecial xlPasteValues
            wbtemp.Save
            wbtemp.Close
            sht.Protect Pass
        End If
    Next sht
End Sub

[nkkhil]

Hiya

The code works up until is skipped the summary sheet and looks for the next sheet but is for some reason looking for Sheet2.xls in the directory. I renamed one of the xls to Sheet1.xls and renamed the worksheet to Sheet1 but this also failed

The master spreadsheet is made up the following sheet in the following order
Summary, Optel TandT, Remote Asset, Control Telephony etc....

And the spreadsheet that feed the data are named as follows sheet name in brackets
Optel TandT.xls (Optel TandT)
Remote Asset.xls (Remote Asset)
Contol Telephony.xls (Control Telephony)

I can rename the sheets on the individual spreadsheet to sheet 1 if that is easier but the master has to remain as is.

So Optel TandT.xls (Optel TandT) need sto write to the Optel TandT worksheet within the master spreadsheet, Remote Asset.xls (Remote Asset) needs to write to the Remote Asset worksheet within the master spreadsheet and so on


Hope that helps?

[nO_OnE]

So the sheet name on the source workbooks are going to be the same names as on the "Master"?

I am not sure what you are asking about the summary, is there a summary workbook?

I modified the last post so it would have line numbers,
To fix the sheet name issue, replace Line 21 with:

            wbtemp.Worksheets(Sht.name).Range("B2:N34").Copy

[kaliman79912]

Unfortunately due to the distribution of the master spreadsheet once populated it is not possible to query or reference the data.

I agree though it would have been alot easier to do so.

Excuse me for insisting but you could distribute a version of the master spreadsheet that has no external connections and still do it with queries. One option is to have the query built in the macro, execute it and copy the result. This would be much more efficient than oppening each workbook to copy and paste, which in itself is very resource consuming.

[nkkhil]

Hi nO_OnE, That code it working now. Thanks you so much for your help I really appreciate it. It has also spurred me to get more educated in the use of macros and general excel functionality. Thanks once again

Hi kaliman79912,

Unfortunately i am what you might call an excel amatuer but alway open to learn new functions. How is it possible to have a version of the master spreadsheet that has no external connections and still do it with queries?

[nO_OnE]

Glad its working...
VBA opens a lot of doors, doors that most people didn't realize even existed

[kaliman79912]

You can create the query in code and destroy it after it retrieves the data.

Here's an example on how to create it:

Sub CreateQT()
 
    Dim sConn As String
    Dim sSql As String
    Dim oQt As QueryTable
 
    sConn = "DSN=Excel Files;DBQ=C:\Reps\OT.xlsm;" & _
        "DefaultDir=C:\Reps\;DriverId=1046;" & _
        "MaxBufferSize=2048;PageTimeout=5;"
 
    sSql = "SELECT `CUTLOT$`.CUORD, " & _
        "FROM `CUTLOT$` GROUP BY `CUTLOT$`.CUORD"
 
   Set oQt = ActiveSheet.QueryTables.Add( _
        Connection:=sConn, _
        Destination:=Range("a1"), _
        Sql:=sSql)
 
    oQt.Refresh
 
End Sub

To delete the query:

Sheets("Sheet1").QueryTables.Item("MyTable").Delete

This disconects the query but does not delete the data