Solved

Autosave Macro

Posted on 2012-12-21
6
170 Views
Last Modified: 2013-01-01
EE Professionals,

I'm looking for a little help on building out a small macro that autosaves a range to a desktop or file.  I have a WB/WS now that collects about 180 statements in 12 different sections that each have a range name.  What I am looking for is a macro that can be turned off and on (call it Auto-Save +),  that when a certain section is completed the macro will fire, save the range to a XLS WB with the name of the Range and outputted to a folder on the desktop.

Anyone seen something like that before?

Thanks in advance,

B.
0
Comment
Question by:Bright01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 6

Expert Comment

by:scifo_dk
ID: 38712532
Hi,

Give it at go with this, and modify accordingly:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If (Target.Address = "$A$1") Then
         
        Dim wbI As Workbook, wbO As Workbook
        Dim wsI As Worksheet, wsO As Worksheet
    
        ' Source/Input Workbook
        Set wbI = ThisWorkbook
        ' Set the relevant sheet from where you want to copy
        Set wsI = wbI.Sheets("Sheet1")
    
        ' Destination/Output Workbook
        Set wbO = Workbooks.Add
    
        With wbO
            ' Set the relevant sheet to where you want to paste
            Set wsO = wbO.Sheets("Sheet1")
    
            ' Save the file
            .SaveAs Filename:="C:\Book2.xls", FileFormat:=56
    
            ' Copy the range
            wsI.Range("A1:B10").Copy
    
            ' Paste it in say Cell A1. Change as applicable
            wsO.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        End With
        
        Application.EnableEvents = True
    End Sub

Open in new window


Sources:
Trigger on change
Copy range to new workbook
0
 

Author Comment

by:Bright01
ID: 38712553
Scifo,

Thank you for the code.  Several key questions;

1.) Do I put this in a module, This Workbook or the Sheet that it is monitoring?  I think I put this in the sheet.
2.) Is the Target Address the trigger?  
3.) Do I put the trigger in for each copy range?   wsI.Range("A1:B10").Copy

The way I have this set up is that I have 180 statements in 12 Sections.  I'm thinking that I identify the first statement in a category and that drives the firing of the macro.  I'm thinking that as it saves, it just saves all completed sections so it's not creating new WBs on the desktop.

Am I thinking about this right?

Thank you,

B.
0
 
LVL 6

Accepted Solution

by:
scifo_dk earned 500 total points
ID: 38712627
Edited: 15:08 - Found a bug in the code.
---------------------------------------------------------------

1. You should put this in the Sheet.

2. Yes it is.

3. I've updated the code, to use a range insted:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Application.Intersect(Target, Range("A1:B10")) Is Nothing Then
         
        Dim wbI As Workbook, wbO As Workbook
        Dim wsI As Worksheet, wsO As Worksheet
    
        ' Source/Input Workbook
        Set wbI = ThisWorkbook
        ' Set the relevant sheet from where you want to copy
        Set wsI = wbI.Sheets("Sheet1")
    
        ' Destination/Output Workbook
        Set wbO = Workbooks.Add
    
        With wbO
            ' Set the relevant sheet to where you want to paste
            Set wsO = wbO.Sheets("Sheet1")
    
            ' Save the file
            .SaveAs Filename:="NewDoc.xls"
            ' Copy the range
            wsI.Range("A1:B10").Copy
    
            ' Paste it in say Cell A1. Change as applicable
            wsO.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        End With
         
    End If
         
        Application.EnableEvents = True
    End Sub

Open in new window


I am a bit unsure about what you mean with the last part of your comment?

The code copies the range "A1:B10" to a new sheet and saves it as "c:\NewBook.xlsx" if something has been changed in cells "A1:B10".
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Bright01
ID: 38713385
Here's what I've got.

1.) I see NewDoc.xls, not NewBook.xlsx; is that right?
2.) I installed the code and tested it.   Here's the challenge, I need to have the macro fire when specific cells are changed (e.g. C3, C19, C33, C46).  When the Macro fires, it needs to copy over the existing Workbook (no need to ask about 'are you sure.....').
3.) It's not storing the WB on the desktop.

Finally, is there a way to activate and de-activate the macro (i.e. I'd like to assign a button on the WS to turn on/off Auto Backup).

Much thanks,

B.
0
 

Author Comment

by:Bright01
ID: 38724985
Scifo,

Do you need anything from me on this?  I appreciate your help.

B.
0
 

Author Closing Comment

by:Bright01
ID: 38734397
This solution did not work properly.  Last post was the 11 days ago.  I'm going to have to reissue the question with a clearer set of directions.

Thank you,

B.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

749 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