Save Active Sheet Of A Workbook In VBA?

May 17, 2005

How can I save only the active sheet of a workbook in VBA?

View 9 Replies


ADVERTISEMENT

Save Active Sheet To Another Workbook

Nov 3, 2008

Copy the active sheet that is open in a work book and save it to another file with the date in the name? For example Report_11-03-08.xls

The only problem I see with this. How could I also make it append a letter to report if the file already exits Example saved as
Report1_11-03-08.xls. Then just continue incrementing.

Sub CopySave()
Application.ScreenUpdating = False
ActiveSheet.Copy
Application.DisplayAlerts = False
ActiveSheet.SaveAs Filename:=ThisWorkbook.Path & "/" & "Report_" & Format(Date, "mm_dd_yy") & ".xls"
ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

View 5 Replies View Related

Save Sheet Then Continue From Active Workbook?

May 5, 2014

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( _

[Code]....

View 3 Replies View Related

Save Active Sheet As New Workbook, Formats & Values Only

Mar 26, 2008

I would like to link a macro to a button on the active sheet, that saves the active sheet In a New Workbook, with Format And Values Only. Preferably saved in the same location as the origonal workbook. This is my main goal.

If possible I would like the new workbook to be named from a chosen cell (E19) on the active sheet with date added.

View 8 Replies View Related

Save As And Close Active Workbook

May 13, 2009

I am trying to create a form button that will save as and close the active workbook. I have the Save As code working but I can't find any code to close the workbook that will work. A couple of issues: I don't have control of what the workbook is called - the user will name it - so I can't do a simple Workbooks (" ").Close. Also, I don't want to quit the application. I don't know if the user will have other worksheets open. I also tried ActiveWorkbook.Close and that didn't seem to work either. Not sure what I am doing wrong.

View 9 Replies View Related

Workbook Save, Make Sure Active Cell Is A1

Jun 10, 2006

When saving a file that has 5 sheets that extend to the right of the users screen, sometimes the left part of the report is left somewhat hidden because the last person to save it was using the part to the far right. Is there a way to move all the active cell to A1 and make sure rows A:D or so are showing?

View 4 Replies View Related

Macro: Create Directory Based On Active Workbook And Save To It

Sep 8, 2006

Read “My Documents” Path And Use Result

Problem:

Note:

Typical user OS will be Windows XP Pro / Win 2K
Excel version : 97 / 2002 / 2003

1. Corporate network security settings will only allow directory/subdirectory creation in the “My Documents” section of customers individual computers.

2. Per customer request, VBA application needs to save extracted files for future use.

3. I can specify an initial “My Documents” subdirectory be made and the VBA application file be loaded/copied into that location – i.e. – “My DocumentsCat”.

4. When VBA application is opened from that specified directory, (first time), the application needs to make an additional subdirectory tree to save future files. I can read the opened from location via VBA with the following:

Dim filepath As String
filepath = ThisWorkbook.Path
As an example – this code would produce a string definition of “filepath” – such as the following:............................

View 5 Replies View Related

Open Workbook, Find Sheet That Contains Cell Value From Active Workbook

Jun 20, 2008

I'm trying to figure out a way to find a specific sheet in a workbook that does not contain the macro. Within the macro I have a cell which holds the name of the specific sheet I would like to find but I can't get it to work for some reason...

'Dim officen As Integer
'Dim thiswb As Workbook

officen = Range("A2").Value
Set thiswb = ActiveWorkbook
' Open the Active Info file
Workbooks.Open "C:My DcoumentsActive 20080616.xls", , , , "xxxxxx"
' Dim sourcewb As Workbook
Set sourcewb = Workbooks.Open"Active 20080616.xls"

Sheets("officen").Select
RowCount = ActiveSheet.UsedRange.Rows.Count
Range("B2").Select.............................

View 8 Replies View Related

Copy Of Sheet From Specific Workbook To Active Workbook

Mar 6, 2014

My requirement is as follows......

I want to get a copy of worksheet from specific workbook to active work book in which i want a copy of sheet get moved.

View 2 Replies View Related

VBA To Print Active Sheet To PDF Auto Save In Specific Folder Then Mail Using Outlook

Jul 17, 2014

How do I write a vba code to print active sheet to pdf, auto save in specific folder then mail using outlook

im using 'Nitro PDF Creator'

also id like it to automatically save the pdf as the same file name as the active worksheet and also auto enter the following when opening the email server:-

Address: Cell D18
Subject: Cell D21
Email body:

find attached quotation

Optional installation available on request

View 2 Replies View Related

Copy Of Sheet From Workbook 1 To X Workbook Which Is Currently Active?

Mar 6, 2014

I have a Picture in a workbook kept open. ( workbook 1 )And i have some X workbook open....i want a copy of sheet from workbook 1 To X workbook which is currently active.

View 1 Replies View Related

SaveAs - Copy Sheet Into New Workbook And Save It In Same Folder As Original Workbook

Jun 17, 2014

I 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] ........

View 2 Replies View Related

Save Single Sheet To New Workbook And Delete VBA Code From New Workbook

Jun 25, 2014

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")

[code]....

View 2 Replies View Related

Copy Active Sheet And Another Sheet To A New Workbook

Jun 10, 2013

I am wanting to create a macro for excel that when run it it will copy the active worksheet and worksheet named "Timesheet" and copy them to a new Excel workbook named the same as the original file + "JobBrief".

View 3 Replies View Related

Hide All But Active Sheet In Workbook?

Dec 25, 2013

With first code user selects a sheet from drop down and that sheet is activated. (This works fine)

Now hide all other sheets is what I'm trying to do with the second code. (This IS NOT working)

I assume I can either put the second code in the change event code or call it from the change event code once it works.

Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address "$A$1" Or Target.Cells.Count > 1 Then Exit Sub

[Code].....

View 2 Replies View Related

Find Active Sheet In A Workbook

Mar 2, 2010

I am creating a macro that will open another workbook and take all the contents off a page in it and paste the contents to itself.

The problem is that sometimes those workbooks will only have one page and it will have the data I need, but sometimes there will be two or three pages in the workbook, but again, with only one sheet that has data on it. How can I have the macro find the one page with data on it?

View 9 Replies View Related

VBA To Import All Sheets From Closed Workbook To Active Sheet?

Aug 20, 2014

code to pull up all the sheet from closed workbook to active opend workbook.

Closed Workbook name : Create Position
Active Workbook name : EIB builder

View 1 Replies View Related

VBA Code To Hide Sheet Tabs In Active Workbook

May 17, 2013

i need a code to hide the sheets tab from the workbook. and only show the horizontal scroll bar and vertical scroll bar.

View 1 Replies View Related

Copy From One Sheet To Another Sheet Then Save In Another Workbook

Apr 9, 2014

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

[Code].....

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

View 7 Replies View Related

Save Excel Sheet In PDF Format With The Name Of Workbook

Jun 23, 2012

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

View 1 Replies View Related

Save A Single Sheet In A Workbook Using A Macro

Apr 2, 2009

I need to save a single sheet to a file (in a workbook of many sheets). I can't use the copy command and then save the new workbook because it truncates the longer info in some cells.

View 9 Replies View Related

Save Sheet With Same Path As Source Workbook

Dec 28, 2006

I've go a shared Workbook, which will be distributed among several users and stored in different places. The workbook uses the following

Sub savemeas()
Worksheets("data").Visible = True
Sheets("data").Copy
Application.DisplayAlerts = False
ActiveWorkbook. SaveAs Filename:="d:" & Range("a1").Value, _
FileFormat:=xlText, CreateBackup:=False
Application.DisplayAlerts = True
ActiveWorkbook.Close SaveChanges:=True
End Sub

to save the sheet "data" as a text file with a name based on the value of cell a1. All I need is to modify the code so that the target path would not be

ActiveWorkbook.SaveAs Filename:="d:" & Range("a1").Value

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.

View 4 Replies View Related

Save Copy Of Workbook, Leaving 1 Sheet Only

Sep 11, 2007

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

View 3 Replies View Related

Automatically Save Sheet To New Workbook When Closing

Jun 30, 2008

I am in the process of setting up an Excel invoice. So far I have managed to implement the following:

On open: Incrementing invoice number
Cells cleared ready for next use

Private Sub Workbook_Open()
Sheets("Sales Invoice").Range("H2").Value = Sheets("Sales Invoice").Range("H2").Value + 1 .....................

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.

View 3 Replies View Related

Save Sheet As Macro Enabled Workbook Using Codes

Jan 28, 2014

Working with a macro enabled workbook that has many sheets what would be a good code that would save Sheet1 into a new MACRO enabled workbook so that the 'Buttons' (which are linked to macros) on this specific sheet will be the only macros that will be copied to the new workbook as opposed to the rest of the macros that are found in different sheets?

Here is what I g=have:

[Code] ......

View 11 Replies View Related

Workbook Sheet Change Event Prompts Save

Oct 28, 2009

I have many worksheets in a workbook that need to be saved if a user changesanything on them.
These sheet names all end in "....SD" and the code needs to only run on those sheets. I have learned alot from the forum but not enough, just yet . . This is what I have so far:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sht As Object
For Each sht In ThisWorkbook.Sheets
If LCase(Right(ws.Name, 2)) = "sd" Then
MsgBox(Prompt:="You must save changes. Save now?", Buttons:=vbYesNo) = vbYes Then ThisWorkbook.Save
End Sub

It doesn't like the 2 "Then's". (Don't laugh - I'm trying.)

View 8 Replies View Related

Copy Current (Selected) Sheet To New Workbook And Save As Tab Name

Feb 28, 2014

I want to take the current sheet and copy to a new workbook (keeping the tab name) and then save as the tab name (xlsl) and close.

I've found ones that do a Saveas box or ends up saving as "Sheet1," "Sheet2" etc but can't seem to find one that keeps tab name and saves.

Here is the one I was using, but it saves all tabs not just selected:

Code:
Sub CopySheet()
Dim fname As Variant, ws As Worksheet, wb As Workbook
Dim nCol As Integer, iCol As Integer

[Code].....

View 1 Replies View Related

Save Workbook In Folder & Sheet Named In Cells

Aug 23, 2007

What is the code to automatically save the new template worksheet in a folder and name defined in a couple of cells.

The folder name will be always be: "Q:AssetsCustomersfolder name"

I need the folder name to come from cell C3 on the spreadsheet and the filename to come from cell R3.

View 2 Replies View Related

VBA Copy From Sheet To New Workbook And Save As From Name In Target Cell Then Loop

Aug 27, 2012

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.

View 4 Replies View Related

Date - Sheet To Copy The Info To Another Workbook, Save And Close

May 3, 2007

I have a sheet that users fill out and there is a macro on that sheet to copy the info to another workbook, save and close.

for some reason, the date, which originates from a cell with the value "=Today()", and when pasted in to the new book, it is pasted values.

the issue is, every so often, the date will say 09/26/10 and continue to do so for quite a few entries.

i have tried almost everything. I checked the dates on the computers, and all are fine. i even went through it step by step, and when the data pasted it, it was 09/26/10.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved