Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Macro Close Workbooks - Large Amount Of Data On Clipboard Msg

I am using the close.workbook command in VBA to close a workbook I opened to copy data from. I am getting a message that says "You have a large amount of data on the clipboard. Do you want it available?" What command do I use in VBA say no to this message?

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Stop Large Amount Of Data On Clipboard Message
I have some code to open up another workbook, take the data from sheet1 in it, copy it to a sheet in the first book, and then close the opened workbook. My problem is that whenever it closes the workbook it gives me the large amount of data on clipboard window that I must then click 'No' on before it will proceed. I thought that making CutCopyMode = False would fix that, but it hasnt worked. Heres the bit of code that does this:

CutCopyMode = False
Workbooks.Open Filename:= _
Windows("Inventory Report.xls").Activate
CutCopyMode = False

View Replies!   View Related
Macro- Large Amount Of Data In A Single Spread Sheet
I have a large amount of data in a single spread sheet. Each row has a branch number on it, there are multiple branch numbers. Each branch number is located in Column A. I want to separate this sheet and put all of the branch numbers into their own sheet. how to do this without manually copying and pasting?

View Replies!   View Related
Close Message Box For "large Amount Of Information"
Is there a macro to close this msg box:

"There is a large amount of information on the Clipboard. Do you want to ...."

I wish for the default to be no which I believe will be FALSE. But I do not know the command

View Replies!   View Related
Converting Vertical Data To Horizontal For Varying Data - Large Amount
I have a one column spreadsheet. The column contains this data:

1 Name
2 Address
3 City
4 State
5 Zip
6 Telephone
7 Fax
11 Name
12 Address
13 City
14 State
15 Zip
16 Phone
17 URL
19 Name
20 Address ... and so on

Where there may be one or two blank rows between the individual records and where there may or may not be a Fax number (or row) in the record.

I am trying to convert this data to a horizontal column format - which works fine if I do a copy/paste special/transpose. However I have to do this for 1,800 records and cannot figure out how to do this reliably.

I gave the above illustration to simplify but, actually this is a two column spreadsheet with individual row labels for every record using the above terminology. In other words the above text is in the first column and the data is in the second. Just thought I'd mention in case there was a way to do some kind of if/then formula.

View Replies!   View Related
Close Workbook Without Clipboard Message
When processing workbooks via VBA, on closing the workbook I sometimes get a request to respond to whether I want to save the clipboard. In the same way that there is the option to open a workbook without refreshing links: updatelinks:=False

I assume there is something on the order of: workbook.close DontSaveClipboard:=True

Question #1 is: What is the parameter syntax for saving or not saving the clipboard?

Question #2 is: Where would I go to look up these parameter settings instead of having to come back to the group every time I find the next one I need?

View Replies!   View Related
Disable Close On Msg And Input Box
Is it possible to disable the 'X' close button on inputbox and msgbox
or any way of making the 'X' not close the box

View Replies!   View Related
Split Data From A Large Spreadsheet By Selected Column Into Multiple Workbooks
I have a large spreadsheet which I need to split into individual workbooks by reference to a particular column.

Rather then doing this manually and splitting the data out one at a time - I would like a macro to do this for me.

I am a novice excel user.

View Replies!   View Related
Update Large Amount Of Worksheets
I have a large amount of worksheets that require a formula update. Update itself is simple. Each formula in the cell has to be devided by different cell. My problem is that I have a big number of worksheets to do this in and they are in different workbooks. Is there a faster way of doing it besides manually updating each cell?

View Replies!   View Related
Link Large Amount Of Cells
I need to link 153 cells from one workbook to another
Is there a code that can do this ? The cells are not contiguous.

Something like.

If not intersect.....
With tartget.Copy.... ??

View Replies!   View Related
Close Workbooks And Move In And Out Of Workbooks In VBA
Basically the main workbook opens 2 files at a time performs a calculate in the main workbook and then copies and pastes information in 3 ranges. Then closes the two open workbooks and loops and performs same operations until it hits the maximum loops. My macro is as follows and I have 3 question in capital letters.

View Replies!   View Related
Large Data Set Summing Macro
See attachment. For example, imagine data in cells A2:N25000 (obviously, this example in the attachment has been shortened). At this point, also imagine that rows 7 & 8 did not exist. So, there is a continuous stream of data for vaious counties in A2:N25000. For the purpose of context, the data contain mortgage information for all the lenders in a particular county for an entire US state.

Is it possible to create a macro that would insert two rows after each county in the data range (e.g., rows 7 & 8 in the attachment)? In other words, in the first row a macro would insert a row (row 7) that sums the results of the top 25 lenders in each county. Then, the macro would insert a second row (row 8) that sums the results of ALL the lenders in each county?

View Replies!   View Related
Close Workbooks In Other Instances
I have one main workbook that, when opened, opens three other workbooks each in its own instance of Excel. I'd like to know how to close all three of these upon closing this main workbook.

View Replies!   View Related
Close All Workbooks In Other Folder
I have several workbooks in a make your day folder categorized under several sub folders. Each workbook has a macro that calls the following macro:

View Replies!   View Related
Close All Open Workbooks Except......
I made an Excel based program that uses multiple workbooks with two main workbooks (“Master List” and “Products”) that all the others pull information from. I run into a problem when a user clicks the close X in the upper right hand corner and the entire application closes. I found some code that will let me close all the workbooks but one (the code is put in the “Products” workbook in the BeforeClose event).

For Each wb In Workbooks
If Not wb Is ThisWorkbook Then
wb.Close SaveChanges:=True
End If
Next wb

Is there a way to modify the code so it will close all the workbooks but the two main ones if someone clicks the close X button in the upper right hand corner?

View Replies!   View Related
Close Multiple Workbooks
How can I close the currentmonthend AND previousmonthend workbooks in the following code, but leave workbook "MoEnd Compare" open? I'd like to do some additional comparisons with the "MoEnd Compare" file. I tried the code below to close those two workbooks, but it is not working.

Sub check_month_end()
Dim currentmonthend
Dim previousmonthend
Dim project_current
Dim ptcurrent
Dim yearcurrent
Dim project_previous
Dim ptprevious
Dim yearprevious
Dim r As Integer
Dim c As Integer
Dim l As Integer
MsgBox "This will check project type and year on current and last period month end report."
currentmonthend = Application. GetOpenFilename("Excel Files (*.xls),*.xls", , "Select CURRENT Month End Report?")
previousmonthend = Application.GetOpenFilename("Excel Files (*.xls),*.xls", , "Select PREVIOUS Month End Report?").......................

View Replies!   View Related
Close All Workbooks Except For The Main Workbook
Is there anyway to change the following code so that it will save and close all workbooks except the one that has this code?

View Replies!   View Related
VBA Code To Close Inactive Workbooks
I tried to use Workbooks("Inactivebookname.xlsm").Close False
and many variations thereof, but I simply cannot close this workbook when currently another workbook is active.

I have no problem in closing an active workbook with

View Replies!   View Related
Close Workbooks Saved As Elements In An Array
I have an array of data type Variant, who's elements are workbooks opened by a user.

The array size is static, which for now isn't a concern but I can't work out how to close the workbooks in the array via a loop and the usual vba code of Workbooks("file").Close

Code I have that doesn't work is:

Sub Close_Workbooks_In_An_Array ()

Dim dFile (1 to 6) As Variant
Dim i As Integer, j As Integer

' // Some code to open files, set each dFile(i) as a file and then process
' // them. Max value for i is 6

j = 1
For j = 1 To i
MsgBox ("Closing: " & vbNewLine & vbNewLine & dFile(j))
Next j

End Sub

View Replies!   View Related
Dynamic Open/Close Of Workbooks Crashes
I'm working on developing a stock option trading system in Excel using a live data feed and VBA. It tends to crash after running flawlessly for a half hour to 2 hours. It seems to crash when loading a support workbook, so I was hoping you could take a peek at my code and see if there's a problem with the way I'm dynamically opening/closing workbooks.

In order to cope with limitations in the amount of live data that I'm accessing (through DDE with Reuters Station), I've split up the data I need into about 600 workbooks, which open when they're needed or close when they're not (through VBA). At any given time, about 35-40 of these workbooks are open.

The main workbook that uses all this live data and support workbooks is set to have the application recalculate every 5 seconds (automatic recalc crashes the program almost immediately). After that refresh, it calls other subroutines, including opening/closing these support workbooks:

View Replies!   View Related
Workbooks.Close Function Stops Code
We're using XP Pro using Excel 07

The background is there are about 40 people who use a Excel based program that contains a pivot and a bunch of other tools that they use on a regular basis, this set of tools has a version number. In this excel workbook, it has a function that looks on the network drive that we have and checks the local version vs the version on our network drive, if its wrong, then the user gets a popup stating your tools are out of date, would you like to update, then they click yes and I have these lines of code

Public Function GetNewTools()
Dim MyFullName As String

'Turn off alerts
Application.DisplayAlerts = False

'Open the new version of tools
Workbooks.Open Filename:= _
"Network DriveUpdate.xlsm"

End Function

The update file has this code that executes on fileopen in the thisworkbook section by calling the following sub

The main issue we're running into is near the very end, the code never makes it to "TEST 2". After the first workbooks close, the code just stops running. No crashes, errors, freezes, anything. It just stops running and never makes it to the second msg box.

View Replies!   View Related
Before Close Code Errors When Closing From Other Workbooks
I have a workbook which includes a simple set of options on closing such as selecting the front sheet, restoring scrollbars and saving the workbook. To avoid problems with subscripts out of range I am using the ThisWorkbook statement to close the workbook.

This works fine and causes the workbook to close when close is clicked on any excel window. The problem is that excel falls over when it tries to resume closing the other workbooks. I am given (ironically) an error saying "excel has encountered a problem and needs to close". Does anyone know how to work around this?

My code is below:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call Toolbars9(True)
With ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayWorkbookTabs = True
End With

View Replies!   View Related
Open, Update And Close Multiple Workbooks
I have 30 workbooks closed and i want update a cell (f.e. b4) in all the workbooks.
Need code to open the files, update the data in that cell and close the workbook.

View Replies!   View Related
Paste Macro - Clipboard Validation
If the user clicks the button to do the paste special macro twice - the second time they get an error (I am assuming that the clipboard has been wiped clean and they need to go back and copy the data again). The specific error message is: "Run-time error '1004': PasteSpecial method of Range class failed".

What I would like to do is add code to the pastespecial macro that looks to see if there is something to paste - if not, I want to give my own message and not the default one.

View Replies!   View Related
Automatically Open, Copy, And Close Multiple Workbooks
I have 77 excel workbooks that are created each week, they are all in the same directory. I have to open each one and copy the data into a single workbook. The file names change slightly each week.

example: DIST_91124_GROWTH_PRODUCT XXX _07072006.xls The 07072006 is the week ending date and will change. Each of the 77 files has a different DIST_number.

Is there a way to automate this process, it takes me about 4 hours to do it manually.

View Replies!   View Related
Stop Data On Clipboard Message
I've just written a macro to copy large amount of data from one worksheet into another. It works well right now, except a little problem. Every time I run the macro, there is always a message box appeared. It let me to choose whether to save or delete data on the clipboard. And I usually choose 'no'. (The message box has been screenshotted and attached into this thread.) Because I use this macro very often, it really makes troubles to me. Is there anyway to block the message box. I mean let it never appear?

View Replies!   View Related
A Macro To Paste The Most Recent Entry From The Office Clipboard
I'm doing a data/filter/advanced filter/show all/unique records to get rid of any duplicate rows. I tried creating a macro and but the macro recorder doesn't know that I want to do NOT a regular paste, but a paste from the most recent entry from the Office Clipboard. Is there a formula and/or macro that will allow me to do this?

View Replies!   View Related
Prevent Loss Of Copied Data On Clipboard
Using a macro I want to paste from the clip board.
I can manually do this by opening the clipboard, selecting the data, which pastes to my specified location.

The macro copies to the clipboard okay, but loses the data that I want to paste because just before pasting I have to clear data which was previously been filtered - I have to do this at this point. The macro bogs down. If I end the macro there, I can manually open the clip board and choose the data I copied from there and paste it that way. I'd like the macro to do this for me.

View Replies!   View Related
Concatinating Data From Two Workbooks (Macro)
I need a macro which will join information contained in two workbooks

The way I want to do this is to open a MASTER WORKBOOK (sHEET1) enter the two filenames in Cells D6 and D7 e.g Ted1.xls and Ted2.xls.

The file path would need to be entered into Cell D5 e.g s:dataspreadsheets

The filename for the concatinated sheet should be entered into Cell D8
e.g TedsConcatinated.xls (the save file path will always be as per Cell D5)

I need to open the file in D6 change to worksheet CopySheet Copy the contents of cells B2 to G48 past the contents into the mastersheet starting at cell C10

Open the file in D7 change to worksheet CopySheet Copy the contents of cells B2 to G48
past the contents into the mastersheet starting at cell C57

Save the sheet with the filename in D8

I need to do a few other things as well but this will be very good for starters.

View Replies!   View Related
Msg Box On Invalid Data
I would like to create a message box that will tell the user if the input they've loaded is valide base on two criteria.

1. The sum of the cells from O2 to O3000 is equal to zero.
2. There are no zeros in the cells from B2 to B3000.

View Replies!   View Related
Disabling "x" To Close & Creating Macro Button To Close
I need to know the command to close a workbook. I have used the information found here: to disable closing the workbook via the upper right "X", but I need a button that, when clicked, simply closes the workbook instead.

View Replies!   View Related
VBA To Copy Selected Cells To Clipboard With Text Data
Need aid in copying certain cells with text/labels to clipboard to format a body of email to be sent via Yahoo webmail, so I can paste it where needed.

I did a couple of Excel VBA's back in 2000 or so at last job but haven't seen/used VBA since.

Have a spreadsheet with customer data, (one cust per row) and wish to create something like the following in clipboard (in this example, assuming row 2 is the selected row)...I'll only be sending out one or two of these per day so nothing fancy is needed, just a way to avoid retyping critical data that could get mistyped.


Rental details for 2008

Customer Name : A2
Unit nbr : A3
Nbr of weeks : A4
Balance due : A5

I'm using Office 2000 still...I've done some searching but results found seem to complicated for my situation or not quite close enough for me to see how to apply it.

View Replies!   View Related
Macro To Copy Data From Open Workbooks
I'm just starting out on my journey into VBA and this forum has been a valuable resource for picking up hints and tricks

I've decided to cut some corners and ask for help for the final piece of my current jigsaw - effectively this comes in two bits.

Part one:

I want to copy a sheet from two open workbooks and paste them into my active wookbook. Both source workbooks only have one sheet. I want a dialogue box to select the desired workbook, select and copy all data and then paste to a specified sheet (replacing the current data) in the destination workbook. I then want to select the other source workbook from the dialogue box and copy all data to a separate sheet in the destination workbook. I would like the dialogue box to have two options - Ok to select, copy and paste data, Cancel to end the macro.

Part two:

I want to copy a sheet from my source workbook and paste it as a separate sheet in a new workbook (a one page workbook would be ideal). I then want to save the new workbook in a specified location as "Data - Date" in the format 2009 02 12.

View Replies!   View Related
Macro To Extract Data From 3 Workbooks With Execptions
I’m not to sure this can be done but I will try and explain what I would like. I have a Master workbook simply called Master.xls (Price) and I need to extract data from 3 other workbooks, these workbooks are in a folder called INFO, the workbooks are called, Summary.xls,(Sheet1) Supplier.xls (Sheet1) and List.xls.(Sheet1). I have attached sample files of all the workbooks, the original Summary has about 2000 colums and the original List.xls has about 20000 columns.

The only workbook i would like to open would be the Master.xls, would it be possible to exract the data without opening the other workbooks? or at least to look like they are not opened. On the Master.xls

When the data button is clicked I would like the following info displayed, if possible. A3 info comes from looking at Summary.xls columns B & C and comparing it against List.xls columns A & B, when a match is found in either of the columns, the code in List.xls C column will be displayed in the Master.xls A3 cell

View Replies!   View Related
Macro Combines Data From Several Workbooks Into One Workbook
I have several individual excel workbooks all in one folder that I need to do some analysis on, but in order to that i need to have them all in one workbook. all of the files are saved in the same folder on my shared drive. each record from each of the files needs to be transfered over into this blank template that i have. I used the Blank template to create all of the individual files, Now i just need to get them all into one file that is easier to work with

View Replies!   View Related
Selecting Dates – In Msg Box And Data
I need a macro that will, (i) make a copy of the current sheet, (ii) let the user select the month to be selected from a list (Message box), then (iii) delete all other columns in the sheet that is not from the selected month and then, (iv) total the data of the selected month in the empty cell on the right.

The sheet contains data in the following way:

Column A = Description of data in rows
Column B to IV = in each column is a specifics day data
Row 1 = Branch Name
Row 2 = the date in the format 2006/05/16 for 16 May 2006
Row 3 – 361 = financial data

View Replies!   View Related
Macro- Code To Transfer Data From Multiple Workbooks Within Folders
I have been given the following code to transfer data from multiple workbooks within folders and subfolders to retreive the same line of data from each of the workbooks and place them in a master workbook.

the folders are set up as follows,

there is a main folder, (a yearly folder)
within this are 12 monthly folders (named January to December)
within these are four weekly folders (named week 1 -week 4)
contained within these weekly folders are the workbooks that i wish to copy data from.

for example a1 - k1

the code i am using transfers the file names but comes up with #REF! instead of transfering the data

Here is the

View Replies!   View Related
Programmable Print Area: Macro To Set The Print Area According To The Amount Of Data In A Particular Range Of Cells
Using Excel 2003 I am trying to write a macro to set the print area according to the amount of data in a particular range of cells. I find I can include this instruction

View Replies!   View Related
Formula- To Calculate The Amount Due Based On Cumulative Sales Once A Breakpoint Amount Is Reached
I need a formula to calculate the amount due based on cumulative sales once a breakpoint amount is reached.


cum sales are > 500 pay at 3%
cum sales are >1,000 pay at 2%

month/ sales/ cumul sales/ amount due
jan/ 100.00/ 100.00/ 0
feb/ 600.00/ 700.00/ 6.00
mar/ 600.00/ 1,300.00/ 18.00

and so on...until the end of year.

I tried using an if formula by could not get it to work.

View Replies!   View Related
Referencing Weekly Data From Large Raw Data Set
Need to produce a formula to reference a large amount of data and pull one month at a time:

View Replies!   View Related
Large CSV File: Too Large To Open. Split
I've got a 80 Mb CSV file and would like to open and work with it. Too many lines (90000 or so).

Is there a way to split ( ) this file so I can open two files instead?

View Replies!   View Related
Conflict Between Auto Save&close Macro And Show/hide Sheets Macro
I am trying to make a save&close workbook macro.

I found several examples on google, but unfortunatly it conflicts with another macro I use for forceing users to enable macros (hide all sheets except one if macros are disabled).

The attached file is an example contaning the save&close code and the show/hide sheets depending on macros enabled.

If the file is opened with macros disabled then only one sheet will be visible.
If the file is opened with macros enabled other sheets are visible.

The problem if that this code uses a custom save, witch makes the save&close not save... (in module1 and in ThisWorkbook)

The pourpose of the save&close is to make sure some users don't forget the excel open and thus block access to it. So if a certain idele time passes excel has to save and close without any confirmation messages.

View Replies!   View Related
Macro - Procedure Too Large
I am using following event macro and it repeats itself more than 300 times. I am at a stage where I am getting a message "Compile Error, Procedure too large", I tried to break it into two but that's not working.

Option Explicit
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cl As Range
Dim lng As Long
Application.EnableEvents = False

Select Case Target.Address
Case "$E$3"
If Target.Value = "Yes" Then
lng = Application.InputBox("Please enter number 0 to 100", , , , , , , 1)
If IsNumeric(lng) = False Or lng < 0 Or lng > 100 Then GoTo SelectNumber
Range("G3") = lng
Else: Range("G3") = 0

.................................................(above statements repeat more than 300 times for different cells)..............................
Application.EnableEvents = True
End Sub

View Replies!   View Related
Macro: Compare The Amount & Paste OK
Hi all,

I have a workbook with 2 sheets: "dan" and "resources"

On the "Dan" sheet, in column B there is a list of names and in column column C, a corresponding dollar amount.
On the "resource sheet" in column B is a subset of the names found on in column B of the Dan sheet. On the Resourse sheet, there is a corresponding dollar amount in column c also.

I would like a macro to compare the dollar amounts to ensure they match. If they match, I would like to place an "OK" in column Q on the appropriate row of the Dan sheet. If the amounts do not match, place an "X" in column Q of the appropriate row on the dan sheet. If the name on the Dan sheet is not one that appears on the Resource sheet, then place an "N/A" in column Q of the appropriate row on the Dan sheet.

View Replies!   View Related
Macro Error: Procedure Too Large
Auto Merged Post Until 24 Hrs Passes; Auto Merged Post Until 24 Hrs Passes; Auto Merged Post Until 24 Hrs Passes;

View Replies!   View Related
Macro To Move Around Large Spreadsheet....
Need the code to put into a command button which jumps from the cell selected, X number of cells to the right. Also will need the code to jump back again when clicking on a different command button.

View Replies!   View Related
Search Data For Largest Amount
I have a file with 2 sheets. The first sheet contains a list of unique invoice numbers. For each invoice, I need to look in the data table (the second sheet) and locate all the products that were sold under each invoice and list the product ID with the largest sale. For example, invoice 12345678 had sales for 5 different products as follows:......

In this example, I would want to list the largest sales amount and the Product ID for this invoice (invoice 12345678). I have attached a sample of the file and completed the first 8 records as an example.

View Replies!   View Related
Macro To Copy And Paste The Correct Amount Of Rows
I want to create a macro that will copy and paste a couple seperate collumns but the problem is that each month, and each invoice, contain a different amount of rows so I can't "record" a macro. Lets say I start on A4 which is the heading of Column D..below it are a bunch of records at the end of the records is a space. I need it to stop there. Then do the same for Column G, I, etc. I would like it to copy each column and paste in a new workbook.

View Replies!   View Related
Macro To Format Cells From Large Range
DoneyOte on this but i have added to the range and im now getting an error that i can't seem to fix

View Replies!   View Related
Macro To Replace 0s With Blanks In Large Ranges
I've been looking around to find something like this for a while and I'm pretty new to VBA, so I haven't figured it out myself yet.

Basically, I need a macro that can take a 7-column range and replace the values in all cells containing 0 (but not 10, 20, etc) with a blank cell so that a count function in another column can function.

When I just select the range and use 'Find and Replace' to remove the 0s, it alters 10s, 20s, 30s, any number that even ends in zero instead of just the value of zero itself,

View Replies!   View Related
Macro: Small & Large Formula
I wondered if there is a possibility to make this

Range("L3").FormulaR1C1 = "=SMALL(R[-1]C[-11]:RC[-11],1)"
Range("L4").FormulaR1C1 = "=LARGE(R[-2]C[-11]:R[-1]C[-11],1)"

more simple so i can get the range for my small and large formula's variable? What i try to reach here is:

Range("L3") = smallest date In Range("A2", Range("A" & Rows.Count).End(xlUp))
Range("L4") = largest date In Range("A2", Range("A" & Rows.Count).End(xlUp))

View Replies!   View Related
Copyright © 2005-08, All rights reserved