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.

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

View 7 Replies


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"

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

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:

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


View 1 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

Macro To Copy Dynamic Sheet In New Workbook - Save As Temp / Mail And Delete

Sep 15, 2014

Code to copy the sheet that get selected by if-then-elseif statement and paste in a new workbook, save the workbook as temp, email it and delete it,

sub sending_mail

Dim OutApp As Object
Dim OutMail As Object
Dim rng As Range
Dim DataWB As Worksheet

[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.

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


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

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
Application.DisplayAlerts = False
ActiveSheet.SaveAs Filename:=ThisWorkbook.Path & "/" & "Report_" & Format(Date, "mm_dd_yy") & ".xls"
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

View 5 Replies View Related

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 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:= _
End Sub

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


View 3 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
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

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

Copy Sheet & Save As CSV

Nov 7, 2006

I have a workbook with one sheet. Some of the information in this sheet do I want do move to a new workbook, whit only one sheet. Then save the sheet as a CSV file.

Iím sure that this is simple, but I am very new in VBA.

So can someone help me with how to write a macro that create a new file whit only one sheet, and than saves it as a CSV file?

View 5 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

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

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

Copy Sheet - Save As - 400 Error

Jun 13, 2008

When I use this code in the workbook I want it gets as far as creating a new workbook and pasting the data, but then an error comes up saying nothing but "400", and does not complete the saving part.

Private Sub Workbook_Open()
End Sub
Sub SaveASheet()
Dim fName As String
Dim myPath As String
Dim sht As Worksheet
myPath = "K:3. DesignSupply OrdersLog"
For Each sht In ThisWorkbook.Worksheets
If sht.Range("D1").Value "" Then
With ActiveWorkbook
.SaveAs myPath & ActiveSheet.Range("G11").Value & ".xlsx"
End With
End If
Next sht...................

View 9 Replies View Related

Copy Cells From One Sheet To Another & Save

Sep 28, 2007

I need some code that copies from one sheet into another sheet... I am thinking to have a middle sheet with all the code pulling the data from "infosheet" into another sheet "vendorsheet". I'm just wondering if this is the right way to approach this issue.. because the vendor is going to want to use the sheet and we want to automate this operation.

This is what I have so far... but this is on the actual file that we are working with so I want it to be from a middle file. It creates a new file but it doesn't save it also... I'm not sure whats wrong here so any help is greatly appreciated!

Private Sub Workbook_Open()
End Sub

Sub Copy()

Application.CutCopyMode = False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

View 4 Replies View Related

Excel 2007 :: Generate Single Sheet Workbook And Save To Sharepoint Site

Jan 4, 2012

Automating Excel from Access, I'm attempting to generate a single sheet workbook and save to a Sharepoint site. This process works fine on my machine (XL2007, XP), but on a coworkers computer (XL2003, XP), the code throws a 1004 error on the save as line. However, the really odd thing is that stepping through the code doesn't throw an error on the coworkers machine.

Here's the sub being ran; the line it errors out on is the first branch in the .saveas block.

Private Const csSharePointSaveAs = "\sharepoint-us.mycompany.comsitesfinance adminlah blahlah-blahCustomer Publication Tracking.xls"

Private Sub PublishXLtoMOSS()
Dim objXL As Excel.Application 'Object
Dim wb As Excel.workbook 'Object
Dim ws As Excel.Worksheet 'Object
Dim rs As DAO.Recordset
Dim i As Long

'Set objXL = CreateObject("Excel.Application")

[Code] ...........

View 1 Replies View Related

Copy A Sheet Into A New Book And Save File Name As The Current Date

Jul 5, 2009

I have an excel file which I use when I book keep journals in work. Each time I come across a new piece of bookkeeping I add to the file, name the sheet and index it. (I've attached a loose example for illustration) so the bookkeeping template.xls gets bigger every day.

Currently, when I book keep on a particular day, I open the template.xls workbook;

draft my journals;

and then manually copy the worksheets I have completed during the day from the template.xls workbook and paste the sheets into a new book and save the days bookkeeping as the current date.

With the file I have attached can someone show me how to write a macro and apply it to each of the buttons on the various work sheet tabs (each button will run the same macro).

Upon clicking a button, I would like the macro to;

Copy the active sheet into a new book and save the file as the current date.

Each subsequent click of a button the macro will check if the .xls file exists for the current date, if it does, then it will just copy the active worksheet to the file that has already been created.

In the new file which is saved as the current date, I need to remove the macro button and the hyperlink that reads back to index.

I have had some helpful macro's provided from another forum, however, the other forum does not appear to enable users to upload files, so I can't quite convey what it is I am trying to achieve.

View 14 Replies View Related

Copying Cells From One Sheet To Multiple Sheet And Naming Sheet As Copy Text?

Dec 24, 2013

I want to do a loop where you can copy say A3 worksheet 1 then add another sheet naming the work sheet "A3" then copying A3 worksheet 1 to A1 "A3". After that looping to A4 to a new work sheet naming the work sheet "A4"copying the value to A1 "A4", etc...

Is there a simply way of doing this loop? I can probably fit my other coding into the structure.

View 4 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

Excel 2010 :: Paste Link Chart From One Sheet Of Workbook To Another Sheet Of The Same Workbook

Jul 20, 2014

I've created a chart in sheet 1 in a workbook. I want to copy and paste that chart into another sheet (lets take sheet 2) of the same workbook. I am using Excel 2010 version.

Whenever, I try to copy a graph and want to do "Paste Special as Link picture". The problem I am facing as "Paste Link" option is inactive.

I am attaching the Excel for your reference.

View 2 Replies View Related

Extract Each Sheet To Text File & Save As Sheet Name .txt`

Jun 9, 2007

provide me a code for extracting data and outputting it into individual worksheets situated within the excel workbook file. I am wondering what the code would be if the data were extracted to individual text files, individual workbooks or *.prn files?

For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
ws.Name = wsraw.Cells(i, "D")
With ws
.Range("A1") = wsraw.Cells(i, "D")
.Range("A2").Resize(2) = wsraw.Cells(i, "A")
.Range("B2").Resize(2) = wsraw.Cells(i, "B")
.Range("C2") = 0
.Range("C3") = 1000
.Range("A4") = -999
End With

View 2 Replies View Related

Copy Data From One Sheet (Fixed Cells And Sheet) To Another Sheet

Apr 18, 2009

I want to be able to copy a name from one sheet (Available Players), paste it to a cell in another sheet (Round 1 through Round 20). The cell that will be copied is fixed but the place where it will be pasted will be different and may be on a different sheet.

also i would like to change the color of the copied cell to "greyed" out or cut if it can not be greyed out. I have created a button and put in a macro that i created but have been having problems with it, generic 1004 errors that i can not figure out. i am attaching the document.

View 8 Replies View Related

Copyrights 2005-15, All rights reserved