?
Solved

VBA :: Consolidate ranges in one worksheet and copying the MasterData in another workbook

Posted on 2011-10-07
12
Medium Priority
?
349 Views
Last Modified: 2012-05-12
Hi,

Situation:
Within one repository workbook called MasterData, I have many worksheets with monthly data and one worksheet called MasterData with all the consolidated monthly data.

I have also a second workbook called MasterPivot where a copy of MasterData, the worksheet with consolidated monthly data is transformed.

In MasterData workbook:
- Each worksheet have the same headers but vary widely in the number of rows from 65K to 100K.
- Each individual worksheet data (without its headers) is named as a range with an unique name (i.e.: Aug, Sept, etc).

Objective 1: each time a new named range (worksheet of monthly data) is added in a separate worksheet within the MasterData workbook, I want to consolidate it in the MasterData worksheet.
All the MasterData worksheet (including its headers) should be define as a range named: MasterData.

Question 1.1: what is the macro required to consolidate together all ranges in the MasterData worksheet?
Question 1.2: can the MasterData range itself be updated after the consolidation process?

In MasterPivot workbook:
Currently, I manually copy this MasterData worksheet as is into a workbook called MasterPivot.
There, the MasterData worksheet (with its headers) is again defined as a range named MasterData.
I use the MasterData range to populate formulas, pivot tables and charts within some worksheets of the MasterPivot workbook.

Objective 2: Copy the MasterData range to the MasterPivot workbook one and only once after a consolidation process had been done in MasterData worksheet. Define a range called MasterData from that copied content in MasterPivot workbook.

Question 2: is there a macro that can copy the MasterData range from MasterData workbook to the MasterPivot workbook, after a consolidation process had been performed in the MasterData workbook?

Requirements: this setup is to maintain the following:
- MasterData as a repository of all the raw monthly reports acting as a "paper trail" for future references.
- MasterPivot as a "transformation layer" to apply calculated metrics, to organize data in tables and  graphs.

Thanks for the time you took to read this long email.
Hope it is clear enough.




0
Comment
Question by:fredericgilbert
12 Comments
 
LVL 23

Expert Comment

by:Michael Fowler
ID: 36935380
This is a lot of questions for one post. I think you may be better off splitting up these questions

65K to 100K rows per sheet -  Have you considered transferring this data to a database. This would give you better performance, way more control over your data.

Michael
0
 
LVL 31

Expert Comment

by:gowflow
ID: 36935474
As Michael74 suggested which I totally am of the same view you need to attack each item on its own and this by posting sample workbook (this is if you are seeking a solution and want to be practical) as with the info you provided and no files attached I fear you won't hv more input than few vague suggestions like what we both came up with.

to be practical and if you agree on the above approach then you will need to draft a new question keep it simple and restrict it to the first part you want to address and in the title you may call it whatever ... - Part I of say 4 or 5

then post a link here of the new created question and ask for this question to be deleted (or if you want to award points in this quesiton then its entirely to your discreation) and then depending on replies to your first part question you may ask new questions always with releated question to the first one and maybe with the answers given you will see new things you haven't thought of and change the strategy for new questions ...

rgds/gowflow
0
 
LVL 10

Expert Comment

by:broro183
ID: 36939172
Hi,

I agree that each part of this would be easier to tackle separately, however, after briefly glancing at your initial post, here are some brief responses...

1.1. Have a look at Ron De Bruin's addin: http://www.rondebruin.nl/merge.htm

1.2. You can use a Dynamic Named Range, for example "=A2:INDEX(A:A,COUNTA(A:A))", which is explained in http://www.excelhero.com/blog/2011/03/the-imposing-index.html.

2. I recommend against making duplicate copies of the data as your files can become "out of sync". Why do you need a second copy of the data?
I believe that you will be able to point the source of the pivot's at the Master data file.


hth
Rob
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:fredericgilbert
ID: 36943074

Hello all,

Effectively, the post is long and tackle many requests at once. I wanted to provide the complete scope of my current situation instead of going piece by piece.

Yes, I am aware that the number and the size of my monthly worksheets command the usage of a database instead of pursuing using Excel. I will port my MasterData workbook to Access in the future once, I finish educating myself about it. ETA: within 2 months from now.

However, in the meantime, I have to maintain Excel as my primary tool.
Now, there is only 3 objectives into this long post:

a) a macro to copy and paste named range(s)  to a master worksheet.

Instead, I have used RDB Merge routine, more specifically the CopyDataWithoutHeaders VBA
This sub-question is answered to my satisfaction with RDB. Thanks broro183.

b) defining this master worksheet as a named range after consolidation

Still searching for a macro that enable to declare a range with variable number of rows.
I have found a VBA (see below)

c) copying this range into another workbook.
Found it copy a range but not a named range...

Sub Worksheet_Change()
    Workbooks.Open Filename:="d:\test\destination.xlsm"
    Workbooks("List.xlsm").Activate
    ThisWorkbook.Worksheets("MergeData").Range("a1", Range("a1").End(xlDown)).Copy
    Workbooks("Destination.xlsm").Worksheets("MergeData").Range("a1").PasteSpecial Paste:=xlPasteValues
    Windows("Destination.xlsm").Activate
    'ActiveWindow.Close True
End Sub


To the remark about why maintaining a duplicate of the consolidation worksheet in my MasterPivot,
it is because of:
1) the weight of the data document
2)  we are 2 work on each document concurrently and need to maintain some independence from each other during our work.

I am pursuing doing my homeworks on my side
Thanks for your time.

0
 
LVL 10

Expert Comment

by:broro183
ID: 36946144
hi fredericgilbert,

It sounds like you are so close. Let's see if I/we can get you the rest of the way - I'd love to collect some of your points! ;-)

a) yay, I'm pleased it helped :-)

b) Can you understand the Dynamic Named Range (DNR) in the attached file?
This is not a macro, but provided your layout stays the same, the DNR only needs to be created once and it assumes that:
- everything from row 1 downwards is to be copied.
- there are no blank cells in column A.
- you want to include every column that has something in row 1.
- there are no blank cells in row 1.
Are the above assumptions correct?
If not, can you modify the Named Ranges in the attached file or do you have more questions?

c) I haven't tested this, but you can give it ago...
The syntax in this code prevents mistakenly over-writing the Clipboard in the midst of pasting & is shorter too.
Option Explicit
Public glb_origCalculationMode As Long
Public glb_origStatusBar As String

Sub CopyTheDNR()
Const DestFileStr As String = "d:\test\destination.xlsm"
Dim DestFile As Workbook
Dim SourceFile As Workbook
Dim SourceDataRng As Range
Dim FirstBlankDestCell As Range

    'turn off screen refresh for speed
    Call ToggleRefreshXlApp(False)

    Set SourceFile = ThisWorkbook
    Set SourceDataRng = SourceFile.Worksheets("MergeData").Range("ws1_dynRangeWithOUTHeaders")
    'open the destination file
    Set DestFile = Workbooks.Open(Filename:=DestFileStr, ReadOnly:=True)
    'RB: was this needed?... Workbooks("List.xlsm").Activate

    With DestFile.Worksheets("MergeData")
        'find the first blank row
        Set FirstBlankDestCell = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
    End With

    With FirstBlankDestCell
        If .Parent.Rows.Count - .Row > SourceDataRng.Rows.Count Then
            With SourceDataRng
                FirstBlankDestCell.Resize(.Rows.Count, .Columns.Count).Value2 = SourceDataRng.Value2
            End With
        Else
            Stop
            'too many rows exist for copying into the single destination sheet, what do you want to do...?
            Stop
        End If
    End With

    Call ToggleRefreshXlApp(True)
    MsgBox "done"

    Set FirstBlankDestCell = Nothing
    Set SourceDataRng = Nothing
    Set SourceFile = Nothing
    Set DestFile = Nothing
End Sub

Sub RefreshXlApp()
'To Refresh many excel application level settings if any code errors & is stopped without finishing.
'This can be run using [alt + F8] from within excel.
    With Application
        .EnableEvents = True
        On Error Resume Next
        .Calculation = xlCalculationAutomatic
        On Error GoTo 0
        .StatusBar = False
        .ScreenUpdating = True
        .DisplayFormulaBar = True
        .ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
    End With
End Sub
Sub ToggleRefreshXlApp(RefreshAppSettings As Boolean, Optional ByRef xlApp As Excel.Application)
'Obj: used to turn off many excel application level settings to make code run faster.
    If xlApp Is Nothing Then
        Set xlApp = Excel.Application
    End If
    With xlApp
        If Not RefreshAppSettings Then
            glb_origCalculationMode = .Calculation
            glb_origStatusBar = .StatusBar
        End If
        .EnableEvents = RefreshAppSettings
        On Error Resume Next
        '        .Calculation = IIf(RefreshAppSettings, glb_origCalculationMode, xlCalculationManual)
        .Calculation = IIf(RefreshAppSettings, xlCalculationAutomatic, xlCalculationManual)
        On Error GoTo 0
        .StatusBar = IIf(RefreshAppSettings, vbNullString, CBool(glb_origStatusBar))
        .ScreenUpdating = RefreshAppSettings
    End With
    Set xlApp = Nothing
End Sub

Open in new window


re the duplication of data:
Goodluck, I think you should be able to use the principles from the above code to copy the data as required.

hth
Rob
0
 
LVL 10

Expert Comment

by:broro183
ID: 36946168
Ooopps, here is the "attached file".

An-example-of-a-dynamic-Named-ra.xlsm

Rob
0
 

Author Comment

by:fredericgilbert
ID: 36948576
HIi broro183,

I will test and study the solution you provided me with.

Indeed, I am so close that I found a working solution to my sub-questions b) and c) but it took me a while to craft a macro out of disparate material available on the web.

For the benefit of all, I am sharing it here:

=====================================
Sub CopyNamedRange()
    Workbooks.Open Filename:="d:\test\MasterPivot_REAL.xlsx"
   
    'Select Destination Workbook and clear designated WorkSheet
    Workbooks("MasterPivot_REAL.xlsx").Worksheets("MergeData").Range("a1:ck1", Range("a1:ck1").End(xlDown)).Name = "MergeData"
    Workbooks("MasterPivot_REAL.xlsx").Worksheets("MergeData").Range("MergeData").Select
    Selection.Clear
   
    'Select Source WorkBook and Define Named Range to Duplicate
    Workbooks("MasterData_REAL.xlsm").Activate
    ThisWorkbook.Worksheets("MergeData").Range("a1:ck1", Range("a1:ck1").End(xlDown)).Name = "MergeData"
    ThisWorkbook.Worksheets("MergeData").Range("MergeData").Copy
   
    'Select and Paste in Destination Woorkbook and at designated WorkSheet
    Workbooks("MasterPivot_REAL.xlsx").Activate
    Workbooks("MasterPivot_REAL.xlsx").Worksheets("MergeData").Range("A1").Select
    ActiveSheet.Paste
    ActiveWorkbook.Save
    Application.CutCopyMode = False
End Sub
=====================================

Do not hesitate to evaluate my macro; it will help me do better next time.

Stay tuned and you might get those points!

thanks for the follow-up.
fg
0
 
LVL 10

Expert Comment

by:broro183
ID: 36951989
hi fg,

Sorry about the slow response, I am still tuned in :)

re b) defining this master worksheet as a named range after consolidation:
Why do you want a Named Range?
Is it used by something else in the file after the consolidation has occurred?
I think a dynamic Named Range (DNR) which stays in the file is the best approach. I recommend a "dynamic" Named Range, rather than one that has a "hardcoded" RefersTo range (see your current macro), because it will then adapt as rows are added or cleared without further interaction from a macro or user. Does my example in the uploaded file make sense?

 
Your code looks good, and it shows that mine is overly complicated because I had mistakenly attempted to copy the latest data below any existing information. I think your code is a good basis for a better solution here's some iterative evaluation of your code with numbered explanatory comments.

Sub CopyNamedRange_ori()
    Workbooks.Open Filename:="d:\test\MasterPivot_REAL.xlsx"
   
    'Select Destination Workbook and clear designated WorkSheet
   ''RB3: (see below) - I only spotted this after getting to RB3
    With Workbooks("MasterPivot_REAL.xlsx").Worksheets("MergeData")
        .Range(.Range("a1:ck1"), .Range("a1:ck1").End(xlDown)).Name = "MergeData"
    End With
    'Workbooks("MasterPivot_REAL.xlsx").Worksheets("MergeData").Range("MergeData").Select
    'Selection.Clear
    ''RB1: selecting/activating objects is not usually necessary and the above 2 lines can be written as...
    Workbooks("MasterPivot_REAL.xlsx").Worksheets("MergeData").Range("MergeData").Clear
   
    'Select Source WorkBook and Define Named Range to Duplicate
    ''RB2: selecting/activating objects is not usually necessary so the next line can
    ''be removed (this means the unqualified ", Range(" statement needs to be changed as per RB3)...
    'Workbooks("MasterData_REAL.xlsm").Activate
    'ThisWorkbook.Worksheets("MergeData").Range("a1:ck1", Range("a1:ck1").End(xlDown)).Name = "MergeData"
    ''RB3: all Range statements are now qualified with the specific sheet by the preceding dot and the enclosing With statement
    ''(rather than being unqualified Range statements which default/relate to the activesheet (what ever that is)
    With ThisWorkbook.Worksheets("MergeData")
        .Range(.Range("a1:ck1"), .Range("a1:ck1").End(xlDown)).Name = "MergeData"
    End With
    
    ''RB6: there is a one line syntax for ".copy" which removes the need
    ''for selecting a different range and also removes the reliance on the clipboard.
    ''So I have replaced the following commented line with a one-liner
    'ThisWorkbook.Worksheets("MergeData").Range("MergeData").Copy
   ThisWorkbook.Worksheets("MergeData").Range("MergeData").Copy Destination:=Workbooks("MasterPivot_REAL.xlsx").Worksheets("MergeData").Range("A1")
   
    'Select and Paste in Destination Woorkbook and at designated WorkSheet
    ''RB4: selecting/activating objects is not usually necessary so the next line
    ''can be removed (if other ".select" & ".activate" lines are also changed)...
    'Workbooks("MasterPivot_REAL.xlsx").Activate
    ''RB5: the next line with ".select" needs to be changed due to the above line being commented out (see RB4).
    ''How can the need for ".select" be removed (see RB6)?
    'Workbooks("MasterPivot_REAL.xlsx").Worksheets("MergeData").Range("A1").Select
    ''RB7: the use of the one line syntax means the previous line & the following line can be commented out.
    'ActiveSheet.Paste
    ''RB8: the next line now needs to be changed (can be made more robust) because of RB4.
    'ActiveWorkbook.Save
    Workbooks("MasterPivot_REAL.xlsx").Save
    ''RB7: ditto
    'Application.CutCopyMode = False
End Sub


Sub CopyNamedRange_v1()
    Workbooks.Open Filename:="d:\test\MasterPivot_REAL.xlsx"
   
    'Clear the designated WorkSheet of the Destination Workbook
    ''RB9: if the Named range is only going to be used for identifying what to Clear,
    ''then the Defined Name isn't really necessary therefore the next lines can be changed/merged...
    'With Workbooks("MasterPivot_REAL.xlsx").Worksheets("MergeData")
    '    .Range(.Range("a1:ck1"), .Range("a1:ck1").End(xlDown)).Name = "MergeData"
    'End With
    'Workbooks("MasterPivot_REAL.xlsx").Worksheets("MergeData").Range("MergeData").Clear
    With Workbooks("MasterPivot_REAL.xlsx").Worksheets("MergeData")
        .Range(.Range("a1:ck1"), .Range("a1:ck1").End(xlDown)).Clear
    End With
   
    'Define Named Range in the Source WorkBook to Duplicate
    With ThisWorkbook.Worksheets("MergeData")
        .Range(.Range("a1:ck1"), .Range("a1:ck1").End(xlDown)).Name = "MergeData"
    End With
    
    'Copy and Paste from the Source Workbook into the designated WorkSheet of the Destination Woorkbook
   ThisWorkbook.Worksheets("MergeData").Range("MergeData").Copy Destination:=Workbooks("MasterPivot_REAL.xlsx").Worksheets("MergeData").Range("A1")
   
    Workbooks("MasterPivot_REAL.xlsx").Save
''RB10: there are various objects that are referred to repeatedly, these could be changed into variables to make the code more readable
''& possibly faster as the computer doesn't have to fully resolve the object each time it is referenced - see "v2".
End Sub

Open in new window


"v2" coming soon...
Rob
0
 
LVL 10

Accepted Solution

by:
broro183 earned 2000 total points
ID: 36952570
Hi Fg,

Here are a few more iterations with comments, note the more iterations that occur the less likely you'll see any benefit, but I've posted them anyway just to show my thought processes. It may or may not help you in this or some other future coding...
Please let me know if you have any more questions.

Rob

Sub CopyNamedRange_v2()
''RB11: These are the objects that seem to be referred to repeatedly, therefore I have created local variables for them.
'' You may recognise them from the code in my first post ;-)
''RB12: I have defined the Destination filepath string as as Constant because it isn't going
''to change while the code is running & constants are more efficient than string variables.
''By convention, I place all constants in my code above any Dim statements because I can then see them (ie my assumptions)
''at the top of my code when reviewing the code and can quickly see if something needs to be changed. However, if the file may change
''(ie it is "variable" rather than "constant") then I would use a different approach such as "GetOpenFileName"  to allow flexibility of choice.
Const DestFileStr As String = "d:\test\MasterPivot_REAL.xlsx"
Dim DestFile As Workbook
Dim DestWs As Worksheet
Dim SourceWs As Worksheet
Dim SourceDataRng As Range
Dim FirstBlankDestCell As Range

    ''RB13: this is also from my original code and it is used to...
    'turn off screen refresh for speed
    Call ToggleRefreshXlApp(False)

    ''RB14: see my original code for slightly different code which opens the file as "Read Only"
    ''to help prevent changes being made by mistake.
    Set DestFile = Workbooks.Open(Filename:=DestFileStr)
    Set DestWs = DestFile.Worksheets("MergeData")
    Set SourceWs = ThisWorkbook.Worksheets("MergeData")

    'Clear the designated WorkSheet of the Destination Workbook
    With DestWs
        .Range(.Range("a1:ck1"), .Range("a1:ck1").End(xlDown)).Clear
    End With
    ''RB15: if there is no data underneath the first block of data on the destination worksheet
    ''the above With statement could be shortened to...
    DestWs.Range("A:CK").Clear

    ''RB16: changed to assign the range to a variable (instead of creating a Named Range)
    'Define Named Range in the Source WorkBook to Duplicate
    With SourceWs
        Set SourceDataRng = .Range(.Range("a1:ck1"), .Range("a1:ck1").End(xlDown))
    End With
    ''RB17: if the Named Range is to be used you could use a With statement which groups the naming of the range & the copy-paste action too
    '    With SourceDataRng
    '        .Name = "MergeData"
    '        'Copy and Paste from the Source Workbook into the designated WorkSheet of the Destination Woorkbook
    '        .Copy Destination:=DestWs.Range("A1")
    '    End With

    ''RB18: if the Named Range is not used for other reasons (which I don't think it is), it can be deleted as it isn't necessary to achieve the purpose of the code.
    ''therefore I have commented out the above With statement & just included the one line syntax for ".copy"
    'Copy and Paste from the Source Workbook into the designated WorkSheet of the Destination Woorkbook
    SourceDataRng.Copy Destination:=DestWs.Range("A1")

    DestFile.Save

    ''RB13: this is also from my original code and it is used to...
    'reset the application settings that were turned off at the start of the code
    Call ToggleRefreshXlApp(True)
    ''RB19: provide feedback because with the changes to the code,
    ''there shouldn't be any visible changes on the screen to let you know that the macro has run.
    MsgBox "done"

    'RB20: many people don't do this as VBA should release the memory used when variables go out of scope, however it is a habit for me.
    'free memory
    Set SourceWs = Nothing
    ''RB21: note the reversal in the order of these statements when compared against the initial "Setting" of the object variables.
    ''This is to prevent "orphaning any variables" by releasing the "children" before the "parents".
    Set DestWs = Nothing
    Set DestFile = Nothing
End Sub

Open in new window


Sub CopyNamedRange_v3()
Const DestFileStr As String = "d:\test\MasterPivot_REAL.xlsx"
Dim DestFile As Workbook
Dim DestWs As Worksheet
Dim SourceWs As Worksheet
Dim SourceDataRng As Range
Dim FirstBlankDestCell As Range

    'turn off screen refresh for speed
    Call ToggleRefreshXlApp(False)

    Set DestFile = Workbooks.Open(Filename:=DestFileStr)
    Set DestWs = DestFile.Worksheets("MergeData")
    Set SourceWs = ThisWorkbook.Worksheets("MergeData")

    ''RB22: IS IT CORRECT TO... Clear the desired columns on the assumption that there is no data underneath the first block of data on the destination worksheet?
    DestWs.Range("A:CK").Clear

    'assign the Source data Range to a variable
    With SourceWs
        Set SourceDataRng = .Range(.Range("a1:ck1"), .Range("a1:ck1").End(xlDown))
    End With

    'Copy and Paste from the Source Workbook into the designated WorkSheet of the Destination Woorkbook
    SourceDataRng.Copy Destination:=DestWs.Range("A1")

    DestFile.Save

    'reset the application settings that were turned off at the start of the code & provide feedback
    Call ToggleRefreshXlApp(True)
    MsgBox "done"

    'free memory
    Set SourceWs = Nothing
    Set DestWs = Nothing
    Set DestFile = Nothing
    ''RB23: to satisfy a personal preference: is there any way the code can be reordered to group actions on specific objects?
    ''I like to code (or "refactor" my code) this way to help identify if there are any duplicated/contradictory
    ''actions and because there can be some performance gains (in some situations).
End Sub

Open in new window


Sub CopyNamedRange_v4()
Const DestFileStr As String = "d:\test\MasterPivot_REAL.xlsx"
Dim DestFile As Workbook
Dim DestWs As Worksheet
Dim SourceWs As Worksheet
Dim SourceDataRng As Range
Dim FirstBlankDestCell As Range

    'turn off screen refresh for speed
    Call ToggleRefreshXlApp(False)

    Set DestFile = Workbooks.Open(Filename:=DestFileStr)
    Set DestWs = DestFile.Worksheets("MergeData")
    Set SourceWs = ThisWorkbook.Worksheets("MergeData")

    'assign the Source data Range to a variable
    With SourceWs
        Set SourceDataRng = .Range(.Range("a1:ck1"), .Range("a1:ck1").End(xlDown))
    End With

''RB24: group actions on the same object using a With statement (as per RB23)
    With DestWs
    ''RB22: IS IT CORRECT TO... Clear the desired columns on the assumption that there is no data underneath the first block of data on the destination worksheet?
        .Range("A:CK").Clear
        'Copy and Paste from the Source Workbook into the designated WorkSheet of the Destination Woorkbook
        SourceDataRng.Copy Destination:=.Range("A1")
    End With
''RB25: the below line could be modified slightly to fit inside the above With statement by placing it inside the above With statement and changing it to ".parent.save".
''fyi, I'm unlikely to do this because it isn't as direct & doesn't make sense when applying a "dot processing" principle
    DestFile.Save

    'reset the application settings that were turned off at the start of the code & provide feedback
    Call ToggleRefreshXlApp(True)
    MsgBox "done"

    'free memory
    Set SourceWs = Nothing
    Set DestWs = Nothing
    Set DestFile = Nothing
End Sub

Open in new window


Sub CopyNamedRange_v5()
Const DestFileStr As String = "d:\test\MasterPivot_REAL.xlsx"
Dim DestFile As Workbook
Dim DestWs As Worksheet
Dim SourceWs As Worksheet
Dim SourceDataRng As Range
Dim FirstBlankDestCell As Range

    'turn off screen refresh for speed
    Call ToggleRefreshXlApp(False)

    Set DestFile = Workbooks.Open(Filename:=DestFileStr)
    Set DestWs = DestFile.Worksheets("MergeData")
    Set SourceWs = ThisWorkbook.Worksheets("MergeData")

    'assign the Source data Range to a variable
    With SourceWs
        Set SourceDataRng = .Range(.Range("a1:ck1"), .Range("a1:ck1").End(xlDown))
    End With

    With DestWs
    ''RB22: IS IT CORRECT TO... Clear the desired columns on the assumption that there is no data underneath the first block of data on the destination worksheet?
        .Range("A:CK").Clear
        'Copy and Paste from the Source Workbook into the designated WorkSheet of the Destination Woorkbook
        SourceDataRng.Copy Destination:=.Range("A1")
    End With
    DestFile.Save

    'reset the application settings that were turned off at the start of the code & provide feedback
    Call ToggleRefreshXlApp(True)
    MsgBox "done"

    'free memory
    Set SourceWs = Nothing
    Set DestWs = Nothing
    Set DestFile = Nothing
End Sub

Open in new window

0
 

Author Comment

by:fredericgilbert
ID: 36953054
Hello broro183,

I think I have all I need here, even more than fist expected.
Thanks for the time you took to write and comment the many codes provided here.
For that, I will accept your solution(s) and award you points.

It surely helps me learn a lot.
Bye
fg
0
 

Author Closing Comment

by:fredericgilbert
ID: 36953067
Broro183 took extra time to provide a lot of code with generous comments that truely help anyone looking to learn VBA. Great solution provider.
0
 
LVL 10

Expert Comment

by:broro183
ID: 36958718
hi Fg,

Thanks for the kind words - I'm pleased I could help :-)

Good luck for your future coding!
Rob
__________________
Rob Brockett. Always learning & the best way to learn is to experience...
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question