Workbook Archive
Aug 20, 2007way i can save an open workbook using a macro in that workbook
but as a certain filename and have it placed into a certain folder!
ADVERTISEMENT
Save Copy Of Open Workbook And Make PDF For Archive
Apr 27, 2012Code, couldn't make it work with VB HTML Maker. I'm new with VBA Basically my idea is when someone open default file, insert data, try to save file, macros save workbook with inserted data as another file (*.xlsx) and make pdf in another folder. Pdf part is working fine, but with second part just can't find right option.
Pdf part
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Dim Name As String
Dim i As Integer, j As Integer
Name = ThisWorkbook.Path & "PDF Archive" & ActiveSheet.Range("F6") & " Invoice " & ActiveSheet.Range("B11")
[Code]...
Excel part
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ActSheet As Worksheet
Dim ActBook As Workbook
Dim CurrentFile As String
Dim NewFileType As String
Dim NewFile As String
[Code]...
Archive Data From Sheet In Workbook Based On Date
May 17, 2006Here is what I want to do in words- how do I translate it into VBA? I have 2 sheets in a workbook (Tally & Archive) The Tally sheet has aa set of buttons which record tasks completed (the range of these is E9 : E29) I have a current date box ( A" which uses the =today() formula). I want to archive the range E9:E29 in Tally to consecutive daily columns in the Archive sheet
My logic has got me thus far:-
select Archive sheet
look at A1 to the end of the row
If the active cell is empty then
put the Value of "Tally" A2" into the active cell
Copy the range E9:E29 in Tally to the column under the active cell
Return to the Tally sheet
Archive DATA
Oct 9, 2009I Have a Sheet that Have ove 5000 rows and 13 colums of data andn i would to archive this info in a different work book or a different sheet in the same workbook. without copy and pasting and i want to auto update as i type in new data.
View 9 Replies View RelatedCopy / Archive Old Data
Feb 26, 2008I have attached a sample in my second post here.
I have searched for many hours and I cannot find what I would like to do or indeed anything similar.
My issue is that I have been asked to build a spreadsheet (I am of novice ability) to create a league of sorts relating to percentages earned by 8 different shifts at work. These percentages are then converted to points and via that through a ranking formula into a weekly table. That I have done but I have been asked if it is possible (I know it must be somehow) to somehow retain last weeks positions. So for instance, weekly percentages have been added the table has been populated, next week the same happens but what I need is prior to new information being added the previous weeks "league positions" are transferred over into a "last weeks" league positions table. Whether this can be automated or manually via button click I have as yet no idea. This problem has me now reading a "VBA for beginners" book but I do not think I will be proficient quickly enough!
Archive The VALUES Of The Spreadsheet Every Two Weeks
Dec 10, 2008I have a spread sheet that pulls data from several different locations.
I need to update this spreadsheet once every two weeks.
I need to archive the VALUES of the spreadsheet every two weeks.
I have a recorded macro that only seems to work from Personal.XLS Modules and I need it to work from the Workbook itself so that others can use it from the shared drive that it is posted on.
The workbook sheet names are DO7, DO8, DO9, Features, and Source Explanation, but I only need the first 3 listed to archive to the new folder. The range could be A1:J100 on each sheet if the entire sheet can't be copied.
I know there is a way to utilize temp files and would prefer to use that method instead of the kindergarten level of a macro that I have pasted below.
Sub AutoArchive()
YesNo = MsgBox("This will save the document and save an archive file. Do you wish to proceed?", vbYesNo + vbCritical, "WARNING: This document will self destruct in 10 seconds!")
Select Case YesNo
Case vbYes
'Insert code for "Yes"
ActiveWorkbook.Save
Sheets("DO7").Select
Range("A1:J100").Select.....................
Excel 2011 :: Using Macros To Export / Archive
May 2, 2013I'm using Excel 2011 on a MacBook Pro. Here's my situation:
I'm running a monthly budget report that's got three spreadsheets within the workbook. Spreadsheet #1 is summary sheet with a few pivot tables. Spreadsheet #2 is the data sheet, where all of the information is entered, and is what feeds the pivot tables on Spreadsheet #1. Spreadsheet #3 is just a list page that populates the drop-down list on the second sheet.
As this is a monthly budget report, I'd like to find some way to archive the first spreadsheet (the summary sheet) every month. Ideally, I'd like to be able to create a macro-driven button that, when clicked, does the following actions:
1) Creates a new spreadsheet, with each click, named for the current month and year.
2) Archives the exact contents of Spreadsheet #1 by making an exact copy of its current contents, and places this copy into the newly created spreadsheet.
3) Clears the data from Spreadsheet #1 AND Spreadsheet #2.
To summarize, I'm trying to use a button that, when clicked, runs a macro that archives the current month's data into a new spreadsheet, and then clears the data from Sheets #1 and #2 so that I can start each month with a clean slate. If there's a way to only clear the data from certain cell ranges (as opposed to a complete wipe of the sheet), that would be even better.
Archive Data Based On User's Input
Jun 14, 2006I am trying to move (archive) data from one sheet to another sheet based on a date range provided by the user’s input. If the date on sheet “list” in the cells in range R9:R10000 fall within that date range the following takes place. The rows that meet the date range per the user’s input in the range of R9:R1000 are copied and just the values are pasted into the next empty row starting with row 7 on the “report” worksheet. The cells that are copied on the “List” worksheet are NOT deleted but the contents are cleared. The reason for this is because there are formulas/links in those cells.
I have attached a worksheet example and have some VBA code but it does not work.
Open A Closed File To Archive Some Data From A Current File.
Oct 20, 2009I have the need to be able to open a closed file to archive some data from a
current file.
what coding i need to be able to kick the "open file" bit off?
Copy Data From Workbook / Open Existing Workbook - Select Range And Paste
Mar 26, 2012Copy data from workbook, open existing workbook, select range and paste. But my copied data is lost.
Sub Select_Copy_Paste()
'
'
Windows("ElektroFunctiesDatabase.xlsm").Activate
Sheets("PowerSupply's").Select
Range("A2:I6").Select
Selection.Copy
[Code] .........
' Here i need to do something to paste data into r.address?
Copy A Range Of Cells In Another Workbook Opened In A Separate Instance Of Workbook
Feb 11, 2010I m not able to use the standard Excel Paste Special function when I copied a range of cells in another Excel workbook opened in a separate instance of Excel. Instead, Paste Special thinks that I have copied some non-Excel objects and gave me the Paste As options. This is not the case if I open both workbook within the same instance of Excel. Could you share with us if there is a trick to trigger the normal Paste Special options in such situations (without having to invoke Macro procedures)?
View 2 Replies View RelatedCreating New Workbook / Copying Sheets And Saving Workbook - Subscript Error
May 30, 2014Trying to create a new workbook from another open workbook, then copying all the sheets that aren't called "Summary" to that new open workbook and then saving it. I get a subscript error on this line:
[Code]....
Copying Data From Recently Opened Workbook To Workbook Where Form Is Present
Jun 16, 2014All i did was i just created a form to open a workbook from the directories.
Code to copy the data from that recently opened workbook to my workbook where I have my forms. I need to copy that data as in the case that i don't know the workbook and the sheet name i'am going to open as i may open any of the files!!
Refresh All Queries On Workbook Open When Finished Print To PDF Then Close Workbook
Jan 23, 2014I have data that daily needs to be refreshed and printed to pdf.
I figure the simplest way to do this would be to task schedule the workbook to open daily. Then on open it will refresh the data, print it after all data has been refreshed and close the workbook.
I set it up originally without the need to print so I have all the queries set to refresh when opening the file, however when I now try and put the code to print to pdf on the workbook open event it runs before the queries are finished running. (Query notes: queries were created through Microsoft query, and are accessing a MySQL database queries set to refresh when opening the file queries set to enable background refresh).
Pull Data From One Closed Workbook Into Active Workbook With Command Button
Feb 27, 2014I've been using the following code to bring in individual cell values from one closed workbook to an active one. I would like to modify this is possible to bring in multiple cells at once and also pull them into a different worksheet in the active workbook. Basically, my command button is on Sheet1 but I'd like the data to pull into a cell on Sheet2.
Private Sub CommandButton1_Click()
With Range("Q9")
.Formula = "='C:Users[Workbook Name.xlsm]Worksheet Name'! N27"
.Value = .Value
End With
SaveAs - Copy Sheet Into New Workbook And Save It In Same Folder As Original Workbook
Jun 17, 2014I have been trying to edit a code which previously saved a copy in a new workbook to a specific folder/path. (Additionally it copies and clears some figures, but this is working as it should.)
However I would like the copy to be saved at the same location as the original workbook, regardless of the path the original workbook is saved.
I.e if I need to move the workbook containing the code to a new folder/location, when using the macro, the new copy should be stored in the same folder/location as the original one.
For now it is only saving the copy into "My Documents"
Code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 16-02-2009 by ceng
'
Sheets("Bunker ROB").Select
Sheets("Bunker ROB").Copy
ActiveWorkbook.SaveAs Filename:= _
ActiveWorkbook.Path & Range("D3"), _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
[Code] ........
Making Backup Copy Of Active Workbook While Workbook Is Open
Jun 30, 2014I run a model in Excel that automatically saves my file every xx iterations. After saving the file I want to make a backup of the file. Tried
[Code] .....
but get a permission denied error message.
I don't want to use .SaveAs as it is a huge file that takes a while to save and SaveAs has a tendency to break links that should not be broken..
Copy Worksheet From Closed Workbook To Current Opened Workbook
Aug 21, 2014I have an open workbook (A) and this is where the code should reside. I want to use VBA to copy the content of an entire worksheet from a closed workbook (B) to an existing worksheet in workbook A. How would you accomplish this?
View 7 Replies View RelatedCopy Data From Multiple Sheets In Workbook To Different Workbook But On One Worksheet
Feb 7, 2014I am looking for a code that would copy the data from each worksheet in a given workbook and then paste to just one worksheet within a different workbook. The Sheet names are auto generated when I run this canned report but the naming structure is always the same...the first worksheet is named Repair Details and then the next sheet is named Repair Details_1, the next sheet is named Repair Details_2 and so on for every sheet in workbook. So I would like to copy all of the data(Headers to last cell) and then paste in a worksheet(ex: Master Repair Report.xlsx and the worksheet could be titled Master Repair Details) on a different workbook, then the next sheet would copy from the one under the header to the last record and paste to the same workbook. This process would repeat for every worksheet in the Repair Details Workbook and paste to Master Repair Details worksheet in the Master Repair Report workbook.
View 4 Replies View RelatedSearch Cell Value In Another Workbook And Copy Range From Current To Other Workbook
Mar 11, 2014I have 2 workbooks. 1 is where data is entered called wksPB, the other is like a database workbook. The wksPB has data to be feed from column B to F and there is a dropdown.
what im looking to do is, if the combobox value is selected as Decline then it should display a messagebox that reference value in cell B is denied. if combobox value is Agreed then the macro should search the database workbook for the text entered in column b of wksPB and then copy data of C:E of wksPB to H:J of database sheet where that text is found and also the approver name in wksPB C24 to the approver cell of where that text was found. I've reached till finding the text but what I get my head twiting on is how to copy the text from wksPB column B:F to database sheet column H:K. Im attaching the sample workbooks and the code where ive reached till.
[Code] .....
Attached Files
Replacement Records - 2014.xlsx‎
forum file.xlsm‎
Copy Values From A Worksheet To Another Workbook. Source Workbook Name Unknown
Aug 12, 2009I need to copy a range of values (actually two ranges). The ranges are of fixed size. Le't say A2:D20 and E2:H20.
Both workbooks have the exact same layout, and they both have the same named worksheet (in this case it's called Entry). However the actual filename of the source workbook is unknown. I know numerous users have changed the filename of the xls file.
I want to instruct them to open the old and new workbook, and open a third workbook containing the copy macro, they run the macro and it copies the data.
How can I reference a known worksheet name but of an unknown workbook name esp when the source and destination share the worksheet name?
Modify Existing Macro To Copy To Different Workbook Instead Of Open Workbook?
Dec 7, 2012Here's my macro:
Code:
Sub CopyRow()'
'Copies row to new sheet, highlights it, marks column 'A' as copied.
'
Dim cCell As Range
Set cCell = Selection.Cells(1, 1)
Selection.Copy
Sheets("Sheet2").Select
Rows("2:2").Select
[Code] .....
Is it possible to modify it to paste into a different workbook called c:filesDestination.xlsm, instead of the existing workbook (Source.xlsm)? The destination sheet name is the same (Sheet2). It's OK if both workbooks are open at the same time.
Copy Data From Closed Workbook And Append In Active Workbook?
Feb 6, 2013I have some vba that opens a closed workbook, copies data from a named range and then pastes it to the active workbook.
However, what is happening is that the closed workbook is opened and only part of the data is pasted. What I would prefer to happen is this:
Open the closed workbook-->copy the named range-->paste(append) to next empty cell in column B.
Heres the code that I have got.
Sub Workbook_test()Dim wb As Workbook
Application.ScreenUpdating = False ' turn off the screen updating
Set wb = Workbooks.Open("G:WAREHOUSEPlanningSmartNew Training Plan raining plan.xls", True, True)
[Code]....
Userform VBA - VLookup Data Range From One Workbook And Populate Into Another Workbook
Feb 9, 2013what VBA is required to have a combo box in a userform look up data in a different workbook, then populate that data into the drop down list of that combo box for a user to select.
I have managed to successfully create a vlookup for a combo box in a userform that looks up data in a named range in another worksheet within the same workbook and then populate that data into a field in a worksheet within the same workbook. However, I want to change this so the combo box on a userform (in one workbook) will look up data from a named range in a second workbook without opening the second workbook. At the moment, the code I have that will 'submit' the user selected data from the combo box drop down list works.
The code that has worked so far to vlookup data for the combo box from a worksheet within the same workbook is:
Code:
Private Sub UserForm_Initialize()
Dim pName As Range
Dim ws As WorksheetSet ws = Worksheets("vlookupsheet")
For Each pName In ws.Range("ProjectName")
With Me.cboProjectName
.AddItem pName.Value
End WithNext pName
Me.cboProjectName.SetFocus
End Sub
Do I need specify the file path of the second workbook that will contain the data for the combo box as well as the name of the worksheet and named range in that second workbook?
Take Data From Multiple Cells In One Workbook And Copy Them All Into One Cell In Another Workbook
Mar 14, 2013On one workbook I have "column A" that lists serial #'s, one serial number per cell. What I need is to be able to take all the non blank data from that column and copy it to another workbook with all those serial #'s in 1 cell numerically sorted and separated by commas.
View 1 Replies View RelatedCommand Button To Copy Row Of Data From One Workbook To Column In Another Workbook
Jul 24, 2013I would like to use a command button to copy a row of data from one workbook to a column in another workbook. The row of data will have different values on a daily basis but will always be B2 through BE2. I would like the copied row of data pasted in the next available column to the left in the other work book.
View 3 Replies View RelatedMacro To Transfer All Data From One Workbook To Specific Sheet In Another Workbook
Feb 23, 2014I have data in 2 different workbooks, and I need to transfer all data in 'Jx_ex1' to sheet1 in the 'template' workbook, and all data in 'Gr_ex1' to sheet2 in 'template' workbook. All data should be transferred to the same cell numbers, from A1 to Y5000. I actually have loads of workbooks to transfer to the template but if I have an example with the two then im sure I can work out how to modify it. All files will be in the same folder.
View 8 Replies View RelatedSave Single Sheet To New Workbook And Delete VBA Code From New Workbook
Jun 25, 2014I'm trying to find a way to save a single sheet of an excel workbook and in the same process delete all vba code and shapes from the new single sheet workbook. I was looking around and found this code which does save only the single sheet to a new one sheet workbook but doesn't delete the vba and shape that I have used to assign macros to in the original.
Code:
Sub SaveSheetAsNewBook()
Dim wb As Workbook
Dim InitFileName As String
Dim fileSaveName As String
Dim wshape As Shape
InitFileName = ThisWorkbook.Path & Format(Date, "mm.dd.yy")
[code]....
Formula In Destination Workbook Will Not Update When Change Tab Name In Source Workbook
Jul 17, 2014I am working with multiple workbooks with several tabs in each one. I need the forumula to update the "sheet name" from the source workbook even if the destination workbook is closed.
this is a portion of the forumula I am working with:
=SUM(IF(AND([L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet25!$I$4>=182,[L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet25!$I$4=182,
[L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet26!$I$4=182,
[L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet27!$I$4
Copy Single Call Value From Multiple Workbook To Master Workbook
Sep 17, 2009I have multiple workbooks ( with unique names) under same folder. Each workbook has "Report-Corn" sheet.
I would like to copy cell "P15" from each (workbook-> report-corn -> P15) and paste to Master.xls workbook -> Sheet 1 one after another which is also located under same folder.