File / Workbook Created By Macro Is Making It Heavier?

Jul 4, 2014

I am creating 4 Sheets from a Main Sheet by Filtering Data based on different values in a fixed column, then the Filtered Rows are automatically pasted in the respective sheets.
Then these 4 Sheets are creating another file.

The same thing done manually by Grouping the 4 SHeets and then Move Copy into a New workbook also makes the file heavier.

Everything works fine however though the data is not much the file is very heavy, so what do i do to make it of appropriate size ?

View 1 Replies


ADVERTISEMENT

Refer To Workbook With Name That Is Created By Macro?

Sep 28, 2011

I have the following code that creates a new workbook and names it after the value of a cell in another workbook. Another part of the macro will switch back and forth between the original workbook and the newly created workbook.

What is the best way to refer to new workbook? I'm familiar with the "workbooks "VARIABLE NAME".Activate" style but, the name of the new workbook will change every time the macro runs and I'm not sure how to refer to it.

the code below.

HTML Code:
Sub MakeNewBook()
Dim wB As Workbook
Dim nPath As String

[Code]....

View 3 Replies View Related

Making / Defining Workbook As Variable In VBA Macro?

May 7, 2014

In my VBA macro, it copies data from this.workbook, and paste to another "destination" workbook. That destination workbook must be opened first before the macro is run.

Within the Macro code, I had to specify the exact name of the workbook which the data is pasted to.

The problem is that the exact name of that workbook which it paste to changes every week.

Is there any method where my macro can be changed and updated with the name of the destination workbook each week without manually opening and editing the code. The user of this.workbook is not technical enough to edit and maintain the macro each week

View 8 Replies View Related

Macro To Save Workbook By Referencing Cell In Another Workbook For File Name And File Path

Mar 21, 2013

I have got a master workbook and I have written macro to copy and paste data on another workbook. write a macro to save the new workbook to a file path with a file name where both file name and path are stored in master workbook sheet...

View 5 Replies View Related

Macro Button Disabled When Workbook Date / Time Created Different Or More Than Time Set

Mar 7, 2014

I have a workbook with macro button to run some vba, if i want the button to disable when the user make copies of the workbook (date created is later than original wb saved time) what codes to add in the VBA?

what i assume is to add the time to a cell when the wb is saved, when the wb opens, it check for that cell if the same with the date created, if different, then disable the macro button.

View 9 Replies View Related

Making Backup Copy Of Active Workbook While Workbook Is Open

Jun 30, 2014

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

View 13 Replies View Related

Making Workbook Opened From Other Workbook Active

Nov 20, 2006

I have a workbook that is a formatted report that I need to insert data from a name variable "CSV" file. I have created a macro in this workbook that calls an "Open" dialog for CSV files that I can use to browse to and open the source file. The macro then is supposed to "select all" copy and close the workbook then make the original workbook active and paste the data starting at row 2. The macro "seems" to be working perfectly except when I do the open the macro then makes the original workbook active, selects all copies then wants to close the original workbook. How can I tell the macro that the csv file that I just opened should be the active workbook, baring in mind that the name won't be known before the time it's opened so I can't hardcode the "active. workbook (NAME) "route.

View 9 Replies View Related

FileSystemObject: Finding The Last Created File

Dec 16, 2008

I have tried to use the "FileSystemObject", but i just dont understand the concept around it.

The reference to use it as been selected.

In time, this directory maybe huge, so i want a fast macro to find out the name of the last created file.

View 9 Replies View Related

Closing A Workbook Created?

Mar 25, 2014

I've created a macro which copies the current tab to a new workbook (Book X):

[Code]....

I then manipulate the copied data in Book X. Once this is done I move the manipulated sheet back into original file

[Code] .......

The problem i'm having is I'd like to close the newly created Workbook X file once the macro has run but Workbook X is usually called Book2, Book4, Book7, Book32 etc.

Workbook X will always be the previous file if i use the ALT TAB shortcut.

View 2 Replies View Related

Add-in Opens Workbook It Was Created In

Oct 27, 2006

I've created an add-in that runs a simple macro. The problem I have is that when I run the add-in it opens the workbook I originally created it from - is this what I should expect it to do? How do I stop this from happening?

Also, do I need to have the workbook from which I created the add in located in the same place on every machine I want to use the add in on? At the moment if I move the original workbook the add-in won't work.

View 9 Replies View Related

Newly Created File Not Saving At The Given Location

Jul 1, 2014

I have an issue with saving the file at the desired location below is the code i have currently used . I have given the destination as "D:New folder". The new file does get saved at location "D:" but not inside the "D:New folder", instead names the file as "New folder".

View 3 Replies View Related

VBA To Check Version Of Created XLSM File?

Jun 21, 2013

I'm creating a template xlsm file that has built in marcos. This file will be used by the Engineer as a template, where they fill in the data, and then send it to the team. when the case in finished, they will use clear data macro.

The Problem I want the template to have a macro runs with startup of the file that checks for the lastest template in a network folder (shared folder where they will have read only access). If the used file is the latest one, OK. If not, give a custom msg with the location on the network.

View 6 Replies View Related

Can VB Open The Most Recently Created File In A Folder

Sep 13, 2006

I have a folder called 'Refresh'. Every week a new file is dropped into that folder. Instead of me placing that new file into my workbook, i would like my macro to just grab it. Is there a way for VB to grab the most recently created file that comes into my 'Refresh' Folder.

View 9 Replies View Related

Checking File Created And Modified Dates

Apr 17, 2007

Is there a way I can check an excel workbook's created and last modifed dates using a macro in that same workbook?

View 3 Replies View Related

How To Identify Workbook Created From Template

Nov 7, 2013

I have created a spreadsheet that I wish to save as a template. However, when the a new workbook is created from the template it needs to be saved as a Macro Enabled workbook not just a workbook which is the default. To achieve this I added the line 'thisworkbook.FileFormat=xlOpenXMLWorkbookMacroEnabled' to the 'beforesave' macro for the workbook. This works fine except that it makes saving the file as a template quite difficult.

How I can trap the first time a workbook created from a template using New is saved would be most useful.

View 6 Replies View Related

Workbook Does Not Copy To Vba Created Folder

Sep 22, 2006

I am having trouble getting a copy of current workbook to save (with a hopefully unique name) into a vba created folder. The folder creates fine on any drive that I have access to, but the workbook never turns up. The code is only an extract from the whole but it still fails in isolation.

Sub Macro1()
Copyit:
Dim fso
Dim fol As String
Dim flag As String
fol = "E:Excelb4macrobackups" ' change to match the folder path
Ans5 = MsgBox("Create Backup of " & ActiveWorkbook.Name & "?" & vbCrLf & vbCrLf & " Backup File Path:" & vbCrLf & vbCrLf & fol, vbYesNoCancel + vbExclamation)
If Ans5 = vbNo Then Exit Sub
If Ans5 = vbCancel Then MsgBox "Procedure Terminated!", vbExclamation: Exit Sub.........

View 2 Replies View Related

Saving Workbook In Newly Created Directory

Jun 18, 2009

I am trying to have the user hit a button and save the workbook in a newly created directory. Currently it creates the new workbook and the new directory with the correct names, however the workbook is not in the newly created directory.

View 5 Replies View Related

Extra Files Created In Same Workbook Folder

Jan 22, 2009

I have a Workbook that is located in a Networked Folder and is used by several people every day. The Workbook has a lot of VBA and also has links to other workbooks. I am noticing that over time there are several extra files being created from this excel workbook, in the same folder. The Workbook is the only file in this folder. They are not associated with any program and are just a File Type. They are titled a series of 8 random Letters and Numbers and range is size from 700 KB to about 1 MB (Slightly smaller then the Workbook file size).

Also, Is possible to automatically delete these files when they are not needed? or better yet is there some way to Prevent these files from being created in the first place?

View 3 Replies View Related

Remove Inverted Commas In PIPE Delimited File Created From VBA

Jun 3, 2014

i have a Macro, its creating unwanted inverted commas at the start and end of the File.

File:

"SunilManual|123456790|DrivingLicence|908567543|Sunil|Manchan
|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w
|w|w|w|w|w|w|w|w|w|w|w|ww|w|w|w|w|w|w|w|w|w|w
|w|w|w|w|w|w|w|w|w|w|55|55||4|SR-1161||1234567890"

Expected File:

|SunilManual|123456790|DrivingLicence|908567543|Sunil|Manchan
|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w
|w|w|w|w|w|w|w|w|w|w|w|ww|w|w|w|w|w|w|w|w|w|w
|w|w|w|w|w|w|w|w|w|w|55|55||4|SR-1161||1234567890|

VBA:

Option Explicit
Sub txtt_export()
Dim buf As String, col As Long, r As Long, ws As Worksheet

[Code]....

View 2 Replies View Related

Open File Macro: Same Directory As Macro Workbook

Nov 17, 2006

I created folder on my desktop to hold various documents. I have master excel document which contains forms and command buttons that open documents from same folder. Is there a way to change code instead of having full file path to specify current directory. I'm worried that if I move this folder to another location then my open file button will not work since location is changed.

Also when I open with command button word document, I have a button on word document to close. I can close document but word application is still active. Can I close word application with the document at the sam time. This is the code I'm using: "ActiveWindow.Close"

View 3 Replies View Related

Copy Paste Cells To Newly Created Workbook

Aug 9, 2008

My code in the first workbook generates a new workbook with a variable in the filename, then copies the cells from the first workbook to the newly created workbook. This code is working properly when used in a draft worksheet I use for testing. As soon as I put it in the master file, an error message pops up "Runtime error 1004 : select method of range class failed". Then the debug sends me to the line " Cells.Select ". What should I be looking for, why does my code work on one file but when i put it in another one it doesn't ?

Private Sub CommandButton1_Click()
Dim line_counter As Long
Dim prm_line_value As Boolean
prm_line_value = False
Do
prm_line_value = Sheets("Rate").Cells(1 + line_counter, 1).Value
line_counter = line_counter + 1
Loop Until prm_line_value = False
line_counter = line_counter - 1
Dim Wk As Workbook
Dim number_of_new_wb_needed As Integer
If line_counter < 5000 Then.....................

View 3 Replies View Related

Making Labels From Excel File?

Sep 9, 2012

making labels from excel file

View 1 Replies View Related

Making A Workbook Readonly

Dec 4, 2008

I know that a workbook/spreadsheet can be made a readonly, but i find that this can be altered even if password protected.

I m looking for is a way of making it so when the user has finished entering in data and they save it, ready to send to anther user to view, that the whole sheet is readonly with no way of editing it ( like protecting the whole sheet).

View 5 Replies View Related

Making List Of Excel File Authors?

Sep 18, 2013

I have a bunch of excel files in a folder, and I want to make an excel spreadsheet that makes a list of them all. This drive is a shared drive and their are different authors for files. I have already made the list, with the file name, date modified, date created, etc. However, I am having trouble importing the list of the authors for the files. Is there a way to automatically import a list of excel files in an excel, with an "authors" column? I can't do it manually, for their are hundreds of excel files.

View 1 Replies View Related

Making Text To Columns Permanent In A File

Sep 7, 2007

I have some text that I am having to analyze which comes out of our computer looking like this:

N/0/N/Y/N/N/N/N/N/N/N/N/N/N/N/N/N

I use the text to columns function and headers above it to translate what each field is.

As I'm doing my testing if I do this once, I can keep the file open and any time I paste a similar entry in it takes care of the formatting.

But, is there way to make the file do that permanently? I want to let the other testers use it without making them have to go through the steps of clicking on the delimiter, etc.

View 9 Replies View Related

Making Sheets In Workbook Correspond Using VBA

Sep 23, 2011

A VBA problem has given me a workbook with two sheets. The first one lists the product names and their unit prices, and there is a third column labeled quantity purchased that is blank. The second sheet is an invoice, which will list the product name, the unit price, the number of units sold and the total price of the purchase. Assignment is to create a sub that will automatically fill out the invoice with any number I type into the blank column. Any good code to get two sheets to correspond in that way?

View 1 Replies View Related

Run Macro If File/Workbook Name Matches

Sep 5, 2006

I have a macro attached to a button. The file with this button is distributed among several people via outlook. The macro can only then work properly if the user saves the file on his/her hard drive with its original name. Outlook sometimes automatically saves the files as: Copy of... etc. I was wondering if there is a way to add a condition to the code so that the macro could only then be executed if the name of the saved file was exactly the same as stated in the code.

View 9 Replies View Related

Making File Smaller With Faster Load Times

Mar 4, 2014

Is there a way to reduce a file's size to make it smaller with faster load times? I have a file that has ballooned in size due to the number of formulas and images I have installed.

Sometimes the file shuts down on it's own and make's my computer really slow.

View 6 Replies View Related

Macro To Save Workbook/File As Cell Value

Sep 15, 2006

I need to be able to save a workbook by running a marco and it save the file automatically by pulling what ever the value of cell A1 is.

Ex: I want to run the macro and it save my excel workbook in C:Documents as (Value of Cell A1) What code would you put into Visual Basic.

View 3 Replies View Related

Run Excel Macro From Batch File. Workbook Open

Feb 24, 2009

I wanted to run a batch file which does something like,

Dir /b >Test.csv
Start Test.csv

And then runs an Excel macro once the workbook is open.

I looked into this and discovered that it’s not really that possible to run an excel macro through batch file but other options were to use ‘Workbook_Open’ etc.

I thought this might cause problems because Test.csv doesn’t contain a macro to auto open and because csv’s usually don’t like macros.

I do however have personal.xlsb which opens every time if that helps.

View 6 Replies View Related







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