Making changes after workbook is consolidated with marco

Dave made a macro that consolidated multiple workbooks into one and it works excatly how I wanted it to. But, just noticed a small problem. After the workbooks are consolidated, it appears that some of the formulas and drop down menu's don't work. It is important to my project that the sheet is still functional after consolidated. Not sure if some of my marco's might be the issue on how I have them named.

I did read through the code and studied my formula's but don't know enought to do much.

I am waiting a phone call to pick my mom up from the hospital, so if I don't respond for a bit, that is the issue.

thank you for any help.


expert-template.xls
Expert.xls
saved-after-conso.xls
bvanscoy678Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
I see what's going on.  You apparently have a lot of named ranges that could be invalidated as a result of the sheet-copy approach in the workbook consolidation macro...  At least that's what I'm inferring from looking at the consolidation workbook.

Is this also your impression?

I'm going to customize based on that - assuming the files you're consolidating are also similar in nature, I'm going to leave the tab names alone - so if they're dups, you'll get tab name(2) etc., as that's what your consolidated file looks like...

Dave
0
dlmilleCommented:
Please try the attached - its a bit more intelligent:

1.  It attempts to unhide all sheets (in case any are hidden) and then moves them all over, enmasse - as a result, if the workbook is protected, an error will be raised.
2.  If you save this file as an Excel 2007 file, then it will import Excel 2007 files that have more rows than Excel 2003 can support (FYI, mostly)
3.  It has a separate button for clearing the workbook template (better for users to understand)
4.  As a result of copying enmasse, there should be less issues with named ranges/linkages.   PS - it should use the existing (if it exists from a prior import) named range if a new workbook with the same name range at the workbook level was copied over.
5.  There will be a log of all import attempts (as long as the workbook template is not cleared) with date/time stamps in case several attempts are made from different directories, or the user "forgot" a file at another location, etc...

I hope this suits your needs better.

Please advise!

Dave
Expert-r2.xls
0
dlmilleCommented:
PS - here's the main code:

Also, one minor mod - use this attached file, which reports the right # of errors as it logs...

Dave
Option Explicit
Option Base 1
Public outCursor As Range
Sub ClearWorksheets()
Dim xMsg As Integer
Dim myWks As Worksheet
Dim myName As Name

Application.DisplayAlerts = False

    xMsg = MsgBox("Clear all non Control Panel Sheets?", vbYesNo, "Hit No to preserve existing sheets")
    If xMsg = vbYes Then
        For Each myWks In ThisWorkbook.Sheets
            If myWks.Name <> "Control Panel" Then
                myWks.Delete
            End If
        Next myWks

    
        For Each myName In ThisWorkbook.Names 'delete all names - should be none in this sheet in original state...
            myName.Delete
        Next myName
        
        Sheets("Control Panel").Range("J:J").ClearContents
        Sheets("Control Panel").Range("J1").Value = "------------------- Import Log --------------------"
    End If
Application.DisplayAlerts = True
End Sub
Sub ImportAppendWorkbooks()
Dim myWkb As Workbook, myWks As Worksheet
Dim fname As Variant
Dim fnWkb As Workbook
Dim xMsg As Integer
Dim myLog() As String, myArrayOSheets() As String
Dim importCnt As Integer, errCnt As Integer
Dim i As Integer, j As Integer

Application.DisplayAlerts = False

        Set outCursor = Range("J" & Rows.Count).End(xlUp).Offset(1, 0)
        outCursor.Value = Format(Now, "MM/DD/YYYY HH:MM:SS") & " ----------------------------------- Next Import ----------------------------------"
        Set outCursor = outCursor.Offset(1, 0)
    
        importCnt = 0
        Set myWkb = ThisWorkbook
        fname = OpenMultipleFilesFCN(True)
        Application.EnableEvents = False 'to prevent event handling by full sheet copies importing sheet code over
        If IsArray(fname) Then
            For i = LBound(fname) To UBound(fname)
                Workbooks.Open Filename:=fname(i), UpdateLinks:=2, ReadOnly:=1
                Set fnWkb = ActiveWorkbook
                On Error Resume Next
                Call unhideall(fnWkb) 'just in case some are hidden - will fail on protected sheet and not copy
                fnWkb.Sheets.Select (False) 'group all sheets for move
                ActiveWindow.SelectedSheets.Move after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
                importCnt = importCnt + 1
                ReDim Preserve myLog(importCnt) As String
                If Err.Number <> 0 Then
                    errCnt = errCnt + 1
                    myLog(importCnt) = "Error(" & errCnt & "): " & Err.Number & " processing file: " & fname(i) & " -> " & Err.Description
                    fnWkb.Close savechanges:=False 'close it as no copy was done - no need to close an empty workbook if all sheets did move!
                    On Error GoTo 0
                    Err.Clear
                Else
                    myLog(importCnt) = "Success! File: " & fname(i) & " imported all sheets correctly!"
                End If
            Next i
        End If
        Application.EnableEvents = True
        Sheets("Control Panel").Activate
        Call reportErrors(myLog, importCnt, errCnt)
Application.DisplayAlerts = True
End Sub
Sub reportErrors(myLog() As String, importCnt As Integer, errCnt As Integer)
Dim i As Integer

    outCursor.Value = "There were: " & importCnt & " workbook import attempts and " & errCnt & " errors -> check for Errors which could occur re: protected sheets, etc. - see below"
    Set outCursor = outCursor.Offset(1, 0)
    
    If importCnt > 0 Then
        For i = 1 To UBound(myLog)
            outCursor.Value = i & ") " & myLog(i)
            Set outCursor = outCursor.Offset(1, 0)
        Next i
    End If
    
    outCursor.Value = Format(Now, "MM/DD/YYYY HH:MM:SS") & "-------------------------------- End of Log ----------------------------------------"
    
End Sub
Sub unhideall(wkb As Workbook)
Dim wks As Worksheet

    For Each wks In wkb.Sheets
        wks.Visible = True
    Next wks
End Sub

Open in new window

Expert-r3.xls
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

dlmilleCommented:
PS - I hope your mom is now doing better....

Dave
0
bvanscoy678Author Commented:
Good Morning Dave,

Yep, took mom home yesterday. Much better.

I understand now. I have the hidden worksheets and that is what caused the problems. The name ranges.

I think what I might do is take my name ranges and placed them on the same sheet as my log. That way there is only 1 sheet. The idea is that my supervisors are taking about 20 workbooks and consolidate into one so they can manage easier. If I have 3 sheet coming over per workbooks, that creates more of a mess.

Let me give it a shot and thanks.

Brent
0
bvanscoy678Author Commented:
Okay, I got rid of the extra worksheets and changed all my name ranges.

I placed 3 workbooks in a folder with 3 different names. I ran the marco and it merged 3 workbooks into the one. It changed the names of one of the workbooks and made it the same as the other with a (2) behind the name.

One it was merged, I attempted to make some changes (I added a new date). in Row 14. Nothing happends when I enter the times (in the F and H column).  But, when I hit save, it will then convert my time in F and H column.

Also, none of my macros (the 3 buttons) will work.

I'll keep working on it.
update-merge.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dlmilleCommented:
I will look into your comments, today - hopefully this morning.

Dave
0
dlmilleCommented:
I assume you're interested in moving your sheet code (not workbook vba modules), correct?  I think that's doable and you could get reliable results.  I wouldn't guarantee outcome if you wanted modules moved as well (re: duplicate subroutine names, etc.), but within sheets (which are private) this should be doable.

Please advise,

Dave
0
bvanscoy678Author Commented:
That is correct. The sheet codes, not necessarily the Modules.

Thanks
0
dlmilleCommented:
Brent - in the example you sent, the buttons are referring to public macros, not sheet code.  The workbook/sheet move process in the code DOES move the sheet code.

If this is dedicated (at least your initial use) to one application - e.g., all the tabs have similar format, and have these buttons - then you can put your few macros in a module in the driving spreadsheet.

Thoughts?

Dave
0
bvanscoy678Author Commented:
Yes, this will be dedicated to this one project, which is this application. All of the worksheets that I am consolidating are exact copies with the exception of the data that is entered. But, set up, format et al are the same. I post a template to the exchange server so they all get the same file.

Let me read up on how to put my marcos into a module for the spread sheet.

When I make a macro, I simply just use "record Macro", then walk through the steps, then hit "stop recording". I have started to learn a little bit of code so I can get away from using that method. It usually messes everything up in the long run.

thanks
0
dlmilleCommented:
copy your code out to notepad

Go to the driving spreadsheet (ALT-F11) right-click on "ThisWorkbook" and select Insert Module.

Paste your code there...

The way your macro buttons are setup, they're looking for code in the consolidation worksheet (the driving spreadsheet), so that should suffice.

Let me know.

Dave

0
dlmilleCommented:
You can "Get your code" from your recording, by opening one of those worksheets, right clicking on the button, and hit "edit".

If you have the consolidation/merge workbook open at the same time, you can see on the left and drag/drop your modules in your data work book up/down to the consolidation/merge workbook (will copy it over for you)

Dave
0
bvanscoy678Author Commented:
Okay. I'll try this first thing tomorrow.

Thanks
0
bvanscoy678Author Commented:
Hi Dave,

Ok, I was able to do some reading to follow what was going on. I now get the difference between the sheets and module for the macros.

I copied the code over. and it seems to work perfect!

I also figured out why my total time would not work until I saved the workbook. I reset my tools>options>calculate. I changed it more manual to automatic.

I studied the primary code and looked up a lot of the commands. I won't pretend to know excatly what it is doing, but I do have a much better idea of how it works.

Again, this help has been a life saver and is greatly appreciated. Hopefully it puts me a  step closer to being able to create some of my own projects without so much help.

Thanks
Brent
0
dlmilleCommented:
If you want to get the time stamp to work properly when you might be in manual mode, add these commands at the top of the main routine:

dim calcMode as long

     calcMode = application.calculation
     application.calculation = xlcalculationmanual

     code to do work goes here

    application.calculate
    application.calculation = xlcalculationautomatic

.....

See attached - glad this is working well for you!

Dave
update-merge.xls
0
dlmilleCommented:
Whooops - that code would be (and is in the previously attached):

dim calcMode as long

     calcMode = application.calculation
     application.calculation = xlcalculationmanual

     code to do work goes here

    application.calculate
    application.calculation = calcMode
0
dlmilleCommented:
One more time ;()

        calcMode = Application.Calculation
        Application.Calculation = xlCalculationManual
        application.calculate

       code to do work...

        Application.Calculate
        Application.Calculation = calcMode
update-merge.xls
0
bvanscoy678Author Commented:
Okay, I'll take a look at this first thing monday. The time stamp is not a huge deal, but it would be nice to have.

thanks for the help!
0
bvanscoy678Author Commented:
Very Helpful
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.