PeopleSoft Adoption Made Smooth & Simple!
On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool. Claim Your Free WalkMe Account Now
Course Of The Month
7 days, 21 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Autosave Macro
Posted on 2012-12-21
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.
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.
[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
Hi,
Give it at go with this, and modify accordingly:
Sources:
Trigger on change
Copy range to new workbook
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
Sources:
Trigger on change
Copy range to new workbook
Active 1 day ago
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.
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.
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:
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".
--------------------------
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
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".
Active 1 day ago
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.
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.
Featured Post
PeopleSoft Adoption Made Smooth & Simple!
On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool. Claim Your Free WalkMe Account Now
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Suggested Courses
Course of the Month7 days, 21 hours left to enroll
729 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.