Macro - Opening, Copying And Saving Workbook To Worksheets

Jul 25, 2008

I have been trying to create a Macro that can do the following.

Open a Excel Workbook, Copy the Worksheets that I need (ignoring ones that are not needed.), Save them in the correct location with a new folder of date and time and saving them as csv files.

I have all the elements but can't seem to get them to gel correctly.

Just wondering if anyone has anything like this that I could adapt or edit.

View 9 Replies


Opening New Workbook And Saving Sheet.

Oct 21, 2008

I am trying to write code which copies the activesheet (called "Data"), opens a new workbook, pastes the data into it and closes the new sheet as "sales 2008" followed by the date. What I have at the moment is:

View 3 Replies View Related

Excel 2013 :: Saving / Opening Workbook With Multiple Windows

Aug 7, 2013

With excel 2013 you're now able to open multiple windows or views of the same workbook. However when a workbook is saved with multiple windows open, the next user to open the spreadsheet will also open it with multiple windows. Which can be very annoying when most people work off of one window vs. multiple. Is there a way to disable saving the multiple windows or a macro to force open excel in 1 window?

View 2 Replies View Related

Creating New Workbook / Copying Sheets And Saving Workbook - Subscript Error

May 30, 2014

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


View 3 Replies View Related

Copying Data From Userform To Another Workbook Without Opening Second Workbook

May 16, 2014

I have created userform and it works fine. Following code assigned to 'SUBMIT' button in userform - works fine. I am trying to include code where certain data from userform is also copied to workbook2 ( of course without opening it)- as marked in red...below


View 4 Replies View Related

Add-in To Saving Groups Of Worksheets As A Single Workbook

Oct 7, 2008

I have another little conumdrum I'm trying to work out.

I'm using one of Ron's scripts as an add-in which I've just amended the output path for.

This script essentially creates a new workbook from each worksheet in the active workbook.

As I have a workbook of about 500 worksheets, I am trying to group the worksheets opposed to having to split all and rejoin them manually.

Luckily all of my worksheets names are initials with numbers after them (as assigned by excel when merged):

e.g., RH, RH (2), RH (3), RH (4), AG, AG (2), AG (3) and so on.

That said there are some random ones too, but I'm happy for them to be kicked out as individual files.

Anyway I'm pretty sure there is a way this can be added into this existing script by addressing Sheet.Name perhaps but I'm a bit unsure exactly..

Sorted in order (i.e. 2,3,4) would also be a bonus but not essential as they need to manually checked and I have a separate macro for sorting worksheets anyway.

View 12 Replies View Related

Saving N Worksheets From A Workbook To 1 Text File

Feb 20, 2008

I am trying to write this code where I need to save an excel workbook with a number of tabs to 1 text file with a name of users choice.

I am able to do this if there is a separate file for every worksheet within the workbook but not if we have the same text file to append every time.

View 9 Replies View Related

Macro For Opening Series Of Spreadsheets And Copying The Data - 1004 Error

Aug 20, 2014

The macro (used to) go down the list of spreadsheets and copy certain data from them into this master workbook. Now I'm getting a "'C:Users310108841DesktopTestFolder' could not be found" error and don't know why.


The error appears to happen here:

[Code] ....

What's more infuriating is that this appears to happen at random. I have been trying all afternoon to get it to work, and had no luck. I have literally just run it now, and it works fine. I can't believe it just works at random.

View 2 Replies View Related

Copying Worksheets From Various Files To New Workbook

Apr 3, 2008

I have 100 files (Book1.xls through Book100.xls)

All files reside in L:MISInernal MIS

I need to open each workbook, copy Sheet1, paste to next available Sheet# in Combined.xls, close the workbook and move to the next workbook.

I have the code for copying a specific sheet from one workbook to a new workbook. But I do not have the code which would perform the above mentioned task for me and I don't have any idea for how am I going to do this without your help

I have used the Search option to find out if this question had already been answered but I could not find a solution for this specific request

View 9 Replies View Related

VBA - Copying Multiple Worksheets Into New Workbook As Values

Dec 14, 2012

I am trying to optimize my code below. It works but it is soo slow, it seems to slow down on the array copy and paste special areas. I believe this is because its doing a cell to cell paste method. These sheets have data as well as graphs that i want to retain on the new sheet, but i could select ranges for each each if that would speed up this procedure.


Sub CreateDataSheet()
Dim ws As Worksheet
Dim sDataOutputName As String
With Application
.Cursor = xlWait
.StatusBar = "Saving DataSheet..."
.ScreenUpdating = False


View 9 Replies View Related

Automatically Copying Data From Master Worksheet To Other Worksheets In Same Workbook

Mar 14, 2014

I have to input information into a master worksheet which contains data (employee names, location, start date and client) that then needs to be repeated in several other sheets. I want to input the data into a master worksheet and then automatically populate the other sheets with the data. If the data could then be filterable on each sheet that would be good.

View 9 Replies View Related

Excel 2010 :: Copying Worksheets From Multiple Workbooks Into Current Workbook?

Apr 2, 2014

Im trying to copy multiple workbooks and just save it into only one worksheet. I have 2000 diffrent workbooks with the diffrent amount of rows, The ammount of cells is the same and it dosent change.

im working on a excel 2010

This is what i got for the moment..

Sub LoopThroughDirectory()
Dim MyFile As String[code].....

View 1 Replies View Related

Opening Workbook From Macro

Aug 23, 2006

I'm attempting to write a macro (in Excel) that uses a path that is given in one of the fields of the worksheet to open a workbook and get find a field to return to the original worksheet. I have working code to pull the information from a worksheet within the same workbook, but I can't seem to get it to work with an external worksheet. Can someone assist me in getting this to work?

I have attached my original code that works, so all I need to do is translate it to open the external workbook and worksheet instead of using the local worksheet. This is pretty simple right?

Public Function GetRate(strClassification As String, strContractNo As String) As Integer

View 9 Replies View Related

Run Macro Upon Opening Workbook

Sep 29, 2007

I would like to something arranged where a macro I currently have assigned to a button is automatically run once the workbook is opened. The reason being because I would it to be current with todays date. I have done the following and it results in nothing I still need to click the button to get the macro to work.

Private Sub Workbook_Open()
End Sub

View 2 Replies View Related

Copying Data From Multiple Worksheets To Multiple Worksheets In Another Workbook VBA

May 14, 2012

I have 2 nearly identical workbooks and I need to update historical data from the old workbook into the newer one.

My current Coding Snippets that I want to use look like the following:

Sub UpdateWorkbook()
Dim ws As Worksheet
Dim r1 As String
Dim r2 As String
Dim r3 As String
Dim r4 As String
Dim r5 As String
Dim r6 As String


Now, this code isn't working I suspect because the Copy and PasteSpecial Functions don't work the way I wish to.

View 4 Replies View Related

Macro For Saving Workbook

Dec 24, 2007

I Want A Code That Will Save My Workbook To A Specific File (different Form The One It Is Currently In) Using The Name That I Have Typed In Cell D13 In The Workbook

View 9 Replies View Related

Macro Opening TXT As Workbook Changes Format

Apr 3, 2014

I am having problems to read a number inside a .txt file.

I use this code to open a .txt

Workbooks.OpenText filename:=data_DMC_filename _
, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:= _
False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array _
(1, 1), Array(2, 1)), DecimalSeparator:=",", ThousandsSeparator:=".", _

At original file the number is like this format: 003791689000011403700010, but after opening as workbook it is like this 3,7917E+21

I tried changing the forma to text with this code bellow, but it looses information

Selection.NumberFormat = "0"

and the result is: 3791689000011400000000

I already wrote a lot of my code for other values, using this Workbooks.OpenText command, opening as workbook so I would not like to open the file in text format or so...

View 1 Replies View Related

Automatically Run Macro On Opening Workbook

May 19, 2009

I have attached a spreadsheet with a simple macro. How do i make the macro automatically run when the file is opened.

View 2 Replies View Related

VBA / Macro Automatically Opening Different Workbook

Dec 1, 2011

I wrote code for a few macros in a "save as" or practice version of a spreadsheet. Once I got the bugs worked out on the practice book I pasted the code into the actual spreadsheet I want to use. The problem is that whenever I run one of the macros it opens the practice book that I was using when I wrote the code. There should be no need for this and I can't figure out why that's happening. The code doesn't mention any workbook by name so I don't know why it would want to open the practice book.

View 4 Replies View Related

Autorun Macro When Opening Workbook

Jul 12, 2013

I need the following macro to 'autorun' when I open the workbook.

Sub stcopypaste()
' stcopypaste Macro

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

View 2 Replies View Related

Macro Stops After After Opening Another Workbook

Sep 3, 2007

I am a novice at excel macro programming. My Excel 2000 macro inexplicably stops after the line: Workbooks.Open Filename:=stAddrFullname, ReadOnly:=True

1) I know that the remainder of the code is not executed because a breakpoint on the following line is never reached.
2) I know that this line is executed because the stAddrFullname workbook is clearly opened and is displayed when the code stops prematurely.
3) No error message is displayed and I have no 'OnError' instructions in the code.
4) (This is the bit I really don't understand). If I put a Breakpoint in the macro anywhere in the function preceding the "Workbooks.Open" statement, the macro reaches the Breakpoint and then runs through to completion with no problem. When I remove the breakpoint, it reverts to stopping as soon as it has opened the Workbook.
5) The stAddrFullname workbook has no macros in it.

View 5 Replies View Related

Closing A Workbook Without Saving - Macro

Nov 28, 2008

Need the VBA code to close a workbook, and not save it? I need it to open a workbook, run a macro, and close without saving. The code I have thus far is:

View 3 Replies View Related

Macro To Close Workbook Without Saving

Jan 9, 2007

i m designing a test for a school and want to add a macro that will be assigned to a button at the end of the test i want the macro to close down the the excel program running and not prompt the user to save the workbook is there a vb code that can do this i have tryed a few but they all come back object not found.

View 6 Replies View Related

Copying Between Worksheets With A Macro

Mar 20, 2013

I have 2 worksheets. The first worksheet contaions raw data. On the second worksheet I am pasting certain data from the first worksheet. Everything copies except the data in column BI. This column has blanks as well as numbers. I need the column copied as is. The range should equal the number of entries in column A. Why am I getting the error, and Is there an easier way than what I'm using?

Sheets("Raw Data").Select
Range(Range("A4"), Range("A4").End(xlDown)).Select


View 5 Replies View Related

Macro To Import Data From One Workbook Into Another And Saving

Jul 3, 2014

I've got a spreadsheet listing applications on PCs (assets), one line per application, and need to import that info into another document for each PC and save it with a file name which = the PC name (asset number). I've attached a couple of sample files to try to explain what i mean.For asset number PC037014 I need the corresponding fields filled and the list of applications pasted under where is says Additional Software List (inserting more rows if there are too many applications to fit in). Once the information has been populated, i'd like the file to be saved in a specific folder then move onto the next asset number in the list.

View 13 Replies View Related

Stop Links Updating Opening Workbook With Macro

Jul 24, 2007

Currently I am using VBA Code below to Stop Update links prompts

Private Sub Workbook_Open()
Workbooks.Open "C:TempBook2.xls", UpdateLinks:=False
End Sub

Is there code I can use such This Workbook so that I can save this file as another name and it will work? Biz Auto Merged Post;Dear All, I have found VBA code that works.

Private Sub Workbook_Open()
Workbooks.Open ThisWorkbook.Name, UpdateLinks:=False
End Sub

View 3 Replies View Related

Macro To Lock All Non-Blank Cells After Saving Workbook

Jul 23, 2014

I have been searching for a macro that would lock all the non blank cells (cells that have any value/text) after the workbook is saved.

When the user clicks on save a message box should prompt stating that "The data you entered has been locked successfully".

View 4 Replies View Related

Show Custom Error Message If Opening Workbook Macro Code Fails

May 17, 2008

I cannot figure out how to get my error handler to work, or actually, not work. It seems to work fine when there is an error, but the code still gets read even when there was not an error. Basically, I am trying to open a file, which may or may not be there. When it is not there I want a message to pop up informing the user. However, when the file is there and it opens, the error handler still gives the message box. Any ideas what I am doing wrong?

Private Sub btnOK_Click()
Application. ScreenUpdating = False
Dim LCSfile As String
LCSfile = frmSelectFile.Listbox1.Value
On Error Goto ErrHandler
Workbooks.Open Filename:=sPath & sDate & "" & LCSfile & "QUANT.CSV"
MsgBox ("File is not quantitated. Please select another file.")
Application.ScreenUpdating = True

End Sub

View 2 Replies View Related

Macro For Row Copying From One Workbook To Another?

Mar 29, 2013

#in order of priority

#1 So what I want to do is copy multiple rows from a table into another table, but only info from column a,b and c of that row into the other workbook's table. So I need to search for the number that is in E1 of workbook#1 and pull the rows that have that number from anywhere down column E of workbook#2. But I only want the info from cells a,b and c of the rows to transfer over.

#2 Also how to pull that off on all the sheets 1,2,3,4,5,6,7,8,9,10,11 and twelve of workbook#2's column E's into one list in workbook#1.

#3 How to put a button for the macro so if I change the value in E1 I can just hit it to repopulate the workbook#1 with a new list.

#4 How to pull from page 1-12 of workbook#2 but also a workbook#3 as well.

View 5 Replies View Related

Opening A File Without Opening A Workbook

Apr 17, 2007

I have a need to open a file from my companies intranet. My current method was to open said file via the method that the recorder gave me. However, I would like ot be able to open a said file without having to start open another workbook.

This is the path:

So the command is this:
Workbooks.Open [url]

Links are not actual links

So what I need to know. Is how can I open this file without opening a workbook. I haven't been able to use the VB "Open Statement" to open a file and I don't believe that I've been successfull using the Filesystem object either.

View 9 Replies View Related

Copyrights 2005-15, All rights reserved