I have a workbook that has worksheet called "Data". In that workbook, there is some codes for the event of "workbook_beforeprint". My question is whether i could create a copy of the "master" workbook and delete all the unwanted sheets but "Data" sheet using VBA. There are some codes of creating a new workbook but i suppose i need to the exact copy of the current workbook using VB
have a basic knowledge of VBA. The task I am trying to perform with VBA is to
1. Copy from sheet1 A1 to sheet2 E9 2. Copy from sheet1 B1 to sheet2 E11 3. Save sheet2 in a new workbook file with the file name cell data from sheet1 A1 (511894.xls) 4. Copy from sheet1 A2 to sheet2 E9 5. Copy from sheet1 B2 to sheet2 E11 6. Save sheet2 in a new workbook file with the file name cell data from sheet1 A2 (097219.xls) 7. Repeat the process down columns A and B to the end of the columns. 8. Columns A and B will end at the same time but the data and the last cell will change with each scan added to the columns.
This code will accomplish this task with a single column of data but not two columns. Also column B will have a mixture of numbers and letters in its cells, Column A is only numbers.
VB: Sub MoveData() sName = "temp" Const csPath As String = "C:Documents and SettingsmcgaulcDesktopTestFile" 'MyName = ActiveWorkbook.Name
Here are some images of the data sheet and the saved sheet
Sheet1.jpg Sheet2.jpg Note: sheet2 in image is "temp" sheet in code
I need VBA code for the following - I have a worksheet with seven colums of data (A to G) - I need to copy the first column (A) from the active worksheet then open master workbook called 'master' and paste the data in to column D - then save the 'master' as the name in cell Z1 of the 'master' workbook. Once this has been been completed I need to repeat the process but this time copying column (B) and so on.
I'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")
How to copy the entire worksheet into a new workbook and save the file to a specific directory with the following filename format ("exceptions191011 - ie. the word exceptions followed by today's date in ddmmyy format).
How to add the following condition that if a file already exists with that name then to bring up an error message stating the file already exists? As well as save the new workbook, it appears to close the original workbook - can I stop this?
I know that the line in my code Worksheets.Copy is NOT what I need to have to make this work, but with it it's the closest I could get to what I want.
I want the user to be able to click the SAVE command button, be allowed to either accept or change the default save options, save the current workbook and then go right back to the form that has the SAVE command button. Sounds simple, I know, but when you don't know the right commands to save the WORKBOOK, not just the WORKSHEETS, it becomes a P.I.A.
I know there's a lot there, but here's my code, which is just a slightly altered version of the code by Ron de Bruin:
Private Sub SaveData_Click()
'If no date has been selected then don't allow SAVE If PubDateLabel.Caption = "" Then
MsgBox "Publication Date not set. You must select a date before saving.", vbCritical, "No Publication Date!" Exit Sub
I have a workbook with a worksheet named "Report"... this sheet and it's macros generate a report from data on another sheet. I'm adding a button to copy the (filled) Report sheet to a new workbook, rename the new sheet in the new workbook "Rep[Name]", then save and close the new workbook. I've recorded a maco of me doing these tasks manually, but what's recorded seems very generic, and does not seem to specify what's really occurring...
For example (in part); > Rightclick on the Report tab > Move or Copy > To Book: (new book) > [x]Create a Copy > [OK]
Sub Macro1() Sheets("Report").Select Sheets("Report").Copy End Sub
This recorded macro does not specify (new book), or Before:"Sheet1" as was the result of my actions... and when replayed back, only makes a copy in the active workbook, before the active sheet...
I have managed to pull together code that does the required task - save two sheets from a work book in to a new workbook on to a dorectory each day. However I woul;d liek to paste special the values and cannot figure our how to reference that on the below:
'saves text file in day on day folder Dim WS As Worksheet, CheminDest As String, fNAME As String 'create directories as needed On Error Resume Next CheminDest = "T:DMRatesReportsChecks" & Year(Date) & ""
Is it possible to use VBA code to save a copy of your workbook while removing all formulas and replaceing them with values and formats only? I know how to save and rename, but can;t find the command for a "Static Copy".
I'm trying to save a copy an excel file in the same directory of the original file. The code is pretty standard but it does not work if the file is stored in C: (only c: ) Is that a bug or am I doing something wrong? Anyways here is the code in case someone is interested in trying it out:
Sub CreateCopy() ChDrive ThisWorkbook.Path ChDir ThisWorkbook.Path fileSaveName = Application.GetSaveAsFilename( _ fileFilter:="Excel Files (*.xls), *.xls", _ InitialFileName:="CMS_" & Format(Now(), "mm-dd-yyyy")) If fileSaveName <> False Then MsgBox "Backup copy saved as: " & fileSaveName End If End Sub
I want to have a user press a button on a worksheet page to save a copy of the workbook (and be able to name it and choose where to save it to) without closing or modifying the original. Note that if you "save as" manually it renames the workbook and closes the original.
I have a report which I have written a macro to reformat, I then want to copy the sheet (Remake) containing the report into a new workbook as the only sheet in the new workbook. I want the sheet name to be changed from "Remake" to "Duty to Consider" and I want the new workbook to save itself automatically as "Duty to Consider - Month/Year" - in this case it would be "Duty to Consider - August 2008".
I think that a Pop Up box which lets me input the Month and Year of the report I am working on would be useful in naming the new workbook, if this box popped up when I click the button which runs my Macro that'd be great. Here is the code I am using to reformat my report. This works fine I just need the changes I mentioned. Application. ScreenUpdating = False 'this will turn on screen updating, making the macro run faster
'This will select the DTC Tab and remove rows 1-4 Sheets("DTC").Select Rows("1:4").Select Range("A4").Activate Selection.Delete Shift:=xlUp
'This will add a filter to the data to remove duplicate rows Range("A1").Select Range("A1:J1307").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
'This will copy the data on the DTC tab and copy it into the Remake Tab Cells.Select.....................
I have the below macro steps to save an excel sheet in PDF format in "C:UsersxxxxxDesktop" and with the name of the workbook.
But when the PDF report gets published in c: drive , the PDF file name has .xlsm added to it instead of just the file name. I would like to know how to publish the file in PDF format just in the name of the workbook.
Find the below macro steps:
Sub Docsave() Dim docname As String docname = ThisWorkbook.Name ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:UsersxxxxxDesktop" & docname, Quality:=xlQualityStandard, _ IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:= _ True End Sub
I got a workbook with a database in sheet1. I filter that database and copy - paste values the results to a new sheet. Next, I save that new sheet to somewhere on my drive. The thing is that I want to go back to my original database, and continue filtering. But that workbook is no longer the "active workbook". This is how it works:
sub DifferentClients() For Each FieldWorkBy In Array("Client1, Client2,...") MyDatabase.AutoFilter Field:=CountryCode, Criteria1:=Array( _
but be the same as the source Workbook's - so that I wouldn't have to modify the code for each user separatly, because the sheet would alwayas be saved in the same folder as the current path of the source workbook.
There is only one task left I need to automate. On closing the document I would like a copy to be saved in a specific folder and be given the file name of a cell (B9). Obviously I would not want the 'saved copy' to retain the Incrementing invoice number or the clear cells function. Even if the invoice copy was exported as a flat jpeg that would be fine. I am using Office Exce 2003 (Sp2) - I have attached my invoice should anyone be kind enough to take a look.
I have 2 worksheets, one called "Consolidated" and one called "Converted".
I would like to have a spreadsheet where I press the update button and a macro runs that searches for the word "Converted" in column E on the Consolidated spreadsheet (the original). When it finds it, I would like the entire row to be cut and pasted onto the "Converted" worksheet.
I have managed to do this with my current code:
However when this macro runs, the row left behind on the original sheet is a blank row and I don't want this. I would like it to be continuous spreadsheet, not dotted with random blank rows!
I need to save an excel workbook when a "backup" button is pressed to a folder named "Backups". I want the name to be the "date" (09/07/2013) only and I want it to automatically overwrite anything previously saved for that day.
Code, 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.
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")
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