I keep encountering an error while creating multiple sheets within the same workbook. This is a known error by Microsoft as documented here. In order to avoid using multiple workbooks i need to clear excels cache each time I run the macro as i have limited users sheet creation to 30 sheets which should not trigger the error. Does any one know a code to clear excels cache or memory?
I have used cutcopymode = false but that does not do the trick.
I have a VBA macro that goes through a decently complex system to delete something like 70 old tabs, create new ones, link data from a main page to these tabs, and create 3 plots per tab. When running the script, it will go through something like 50 of the tabs, and then receive an error on something rather trivial that has been done many times before successfully in this macro (like changing the font size on one of the axes on one of the graphs). I tried an On Error Resume Next, and sure enough it ignores the error, but it then does some sort of crazy loop starting over recreating all the tabs incorrectly, and then excel just shuts down. So, that's no good.
Anyway, I can comment out the change of the font size, but it still has the same issue on some other trivial thing. It seems like the system just runs out of room or needs a cache cleared or something. I'm not sure.
I am currently taking over a workbook from a colleague that contains 10 pivot tables. Spaced out over a few sheets.
Each pivot table contains a lot of information and my current workaround to refreshing them all is..
Step1:Refresh 2 Pivots,
Step2:Save and Close document,
Step3: Re-Open document and repeat Steps 1 & 2 until all refreshed.
I was wondering if there was any kind of code to clear the memory space being used by the pivots thus leaving the hard coded information behind.
That way I can just write some vb to refresh all the pivots and not keep saving and re-opening the document which takes sometime as its on a network drive.
I am trying to create multiple pivot tables from the same pivotcache using VBA. The data range is approximately 270,000 records with 100 columns. When I run the macro, I get "Run-time error '-2147352567 (80020009)' Method 'Add' of object 'PivotCaches' failed". Is there a size limit on the data? If there is less than 65,000 records, it works great; if I use a wizard, it works with no problems with the full dataset. If I bring the data in as an Access table, it can work with some tweaking of the code, but the size of the file is huge because each pivot is taking its own snapshot of the data. I also can only create 3 tables before it runs out of memory, so I have to stop, save the file, and open it back up to create 3 more pivots.
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
[Code] .........
The last line is where I am getting the error. I am running XP, Excel 2007.
As I said, if I import an Access table, I can use the code:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table_Pivot_test.accdb[#All]", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="", TableName:=txtPivotTableName, DefaultVersion:=xlPivotTableVersion12
And loop, but the final file size with 14 pivots can be 500MB! I have tried both "Create" and "Add", but they both give me errors.
I have attached a test workbook excel 2010 (ignore ref# errors, I've cut the workbook down for uploading purposes) What I would like to do is have a 'Button' on my 'information Sheet' which when clicked would clear certain cells. I have searched the forum but can't find a solution, everyone seems to want to delete rows or columns but I just want to clear certain cells. The workbook will have 11 sheets each named 'caravan 1' through to 'caravan 11' The uploaded test workbook only only has 3 sheets.
On 'caravan 1' (which is slightly different to the other 10) I want to clear the content of cells
B4 & B5
C4, C22 & C41
D4 & D5
E4,E5, E22,E23,E41 &E 42
On all other 'Caravan sheets' I want to clear the contents of cells
D4 & D5
E4, E5, E22, E23, E41 & E42
It would be icing on the cake if it could give a warning such as " are you sure you want to clear these cells" but that isn't really necessary. The worksheets will be password protected, but the cells mentioned above will not be. If it proves too difficult to clear all the cells on all the sheets with one click, then perhaps a simpler solution might be to have a button on each sheet instead
My macro is designed to look at a summary source tab and create a new tab for each unique project number. It then creates a pivot table from five different source detail tabs and filters on the project number. If a tab already exists it selects the tab and moves on to the next project number. There are six pivot tables created for every project.
New data is added each month to the source tabs and I have a macro to delete all pivot tables and the macro will recreate the pivot tables when ran again.
Issue: Running out of resources At work I'm limited to the use of Excel 2010 (32bit) so I'm restricted on 2GB of memory. At home I ran the file successfully (64bit) and it was around 3GB of memory.
My macro creates a new pivot cache for every pivot table where as I'm trying to only use 6 pivot caches in my coding. I kill it half way through and it's around 100+ caches causing unnecessary usage of memory.
Fix / Solution:
Correctly code the vba to only create six caches and code the rest the pivot tables to use that cache.The only difference in the Pivot Tables is that it’s sorted on the Project Number.
Code:
Dim VBAPPPC As PivotCache
Dim VBAAPPC As PivotCache
Dim VBAPRPC As PivotCache
Dim VBAEXPC As PivotCache
Dim VBAMJPC As PivotCache
Dim VBAIVPC As PivotCache
Dim VBAPT As PivotTable
[code]...
I want to create a command button that will clear whatever is written in a text box. I guess for this to work it needs to select all, then clear. The command button and the text box have been created using the control toolbox.
View 2 Replies View RelatedIn ThisWorkbook Userform2 is triggered upon a double click
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
UserForm2.Show
End Sub
Userform2 has Frame1 (Option Buttons 1-6) and Frame2 (Option Buttons 7-12)
Private Sub OptionButton1_Click()
Call ProjectBuildings
Range("A1").Select
End Sub
Private Sub OptionButton10_Click()
Call BondIndustrial
Range("A1").Select
End Sub
The user can click away and things work great. The macros that are triggered search a number of Workbooks and individual Worksheets looking to select either of two (hence the two Frames) specific items.
If the user clicks any Option Button in Frame 1 (works) then any Option Button in Frame 2 (works) then goes back to the same Option Button in Frame 1 (nothing) I have a problem.
How do I clear the first Option Button in Frame 1 after the user selects another Option Button in Frame 2?
I have a CLEAR FORM command button
What codes can I use so that when I click CLEAR FORM, all the data that I encoded in the textboxes of my current userform will be erased as if I have a fresh new form?
I'm just finishing a project that is a collection of three calculators, each of which returns data based on a series of user-supplied variables. All the mechanics are working [thanks in part to replies to previous posts], but there is one more thing I'd like to add:
Does anyone have a way to clear the contents of another cell [actually several] cells via another cell? As the most complex calculator requires eight variables to be entered, I'd like to provide a quick way [a button?] to "clear" or "reset" the calculator - basically wipe all unlocked cells. I should note that all of the cells in question already have Data Validation of one type or another.
My Workbook with a macro to clear ONLY the highlighted Cells.
The Highlighted Cells can be found on the sheet "Line 5"
I'm putting together a time sheet that can used to calculate total hours worked by department etc. I plan on protecting the worksheet and only allowing certain cells to be selected and data entered.
Is there a way using VBA to place a button on the first spreadsheet that will clear all cells that are 'unlocked'?
I have created a command button, right clicked the button and selected 'View Code'. In the code window I've entered the following code:
Private Sub CommandButton1_Click()
IB_FB_Hedge_3 Outcome.range(W10:X11).ClearContents
End Sub
As soon as I enter the code a message pops up:
"Compile error:
Expected: list separator or )"
Is my syntax wrong? Is the sheet name, 'IB_FB_Hedge_3 Outcome' not allowed?
When I created this code a few minutes before I was not having the 'Compile error' problem, but the Command Button was not working either. When I clicked the command button it would just show the circles at the corners, suggesting that it is still in editing mode and not properly activated as a command button.
How do You Make a Button to Clear Certain Cells?I don't know how to make a button that clears a1-a5 and b1-b5. How do you do this?
View 5 Replies View RelatedI have a button on my userform that I would like to clear all entries in column B in a sheet called "6 Entries" when pressed
Is there a code for this ?
I have seen this before in spreadsheets so I atleast know it is possible.
I have a worksheet that has 6 sheets in it. The final sheet is a formula page that only shows results if sheets 2-5 have data. Sheet 1 holds all the master data. Sheets 2-5 show subsets based on sheet 1. I manually atm filter sheet 1 for certain info and then copy paste into 2-5.
Seeing as I do this weekly. How would I go about adding in a 7th tab or even a macro that will delete all data from row3-end in tabs 2-5, then copy the appropriate data from tab 1 based on a set criteria?
I could write this in a macro just wondering if there is an easy built in way?
Function FindNth(Table As Range, Val1 As Variant,Val1Occrnce As Integer, _
Val2 As Variant,Val2Col As Integer, ResultCol As Integer)
Dim i As Integer
Dim iCount As Integer
Dim rCol As Range
For i = 1 To Table.Rows.Count
If Table.Cells(i, 1) = Val1 And _
Table.Cells(i, Val2Col) = Val2 Then
iCount = iCount + 1
End If
If iCount = Val1Occrnce Then
FindNth = Table.Cells(i, ResultCol)
Exit For
End If
Next i
End Function
To use this function, push Alt+F11 and go to Insert>Module. Paste in the code, push Alt+Q and save. Use the function as shown in graphic example. You can access it under "User Defined" in the Paste Function (Shift+F3).
Type Amount Sex Cost Purchased
Cat 1 Male $ 5.00 22/05/01
Cat 2 Male $ 5.00 15/00/00
Dog 3 Male $ 20.00 25/04/00
Rat 5 Female $ 1.00 15/08/01..................
I am trying to create a command button in Microsoft Excel (Office 2000) that enables me to reset the check box status for a worksheet from checked to unchecked. The worksheet contains approximately 50 check boxes and is meant to be reused. Is this possible? If so, how can I program it to do so?
View 2 Replies View RelatedI have a spreadsheet containing 100 "requests" which can be made by a user
i would like the option for the user to be able to delete a request should they wish it either by deleting a row or clearing the contents
Currently I have a single column which is full of buttons (one for each row)
when i click the button on row 10 I would like row 10 deleted
when i click the button on row 30 i would like row 30 to be deleted etc
is there a way to do this without writing 100 macros which are specific to each line?
Let's say I have a pivot table made from data in another workbook. The other workbook is then deleted. Is there anyway to get the original data back? My particular file is about 10 MB, but if I "save as" it's only 1 MB. Plus, the pivot table "works," so I'm pretty sure Excel still has my data cached somewhere. How can I access it? I'm decent with VBA, so I'm open to any solution at all.
View 5 Replies View Related1) We need the Input screen to auto clear the fields once the " Save " button has been pressed and data transfered to the appropriate tracking sheet
2) We want to ensure that if any of the Red cells are empty the " Save " button will give an error ( one complication If the "Transfer Type" highlighted in Blue has an entry " either STD/ISD/ESP " then " Call Purpose " needs to be mandatory
3) somehow the information on the "Comment " field is not getting captured
4) If possible we want a warning indicator if two rows in a tracking sheet ( example London ) are the same based on Coloums B to I ( except C )
5) Is there any possibility to give a pop calender in the " scheduled date " section of Input screen (Row 33)
What is causing this to error. Basically I'm just trying to highlight cells, and create a pivot table out of that. Here's my Code:
Sub Macro1()
'
' Macro1 Macro
Dim myRange As String
myRange = "Sheet1" & Selection.Address
Sheets.Add
[Code] ..........
When I debug, the value of myRange is something like Sheet1$A$19:$K$37 with the actual cell values being whatever I highlighted. I get a Runtime Error 5 Invalid procedure call or argument. I'm assuming it's something with my sourcedata variable, but I'm not sure what.
Also, I did try commenting out the Sheet.Add and Sheets("Sheet2").Select Cells(3, 1).Select lines, but it still produced the same results.
I am trying to fix a report for a remote coworker in excel, they tell me they get
Run-time error '5': Invalid procedure call or argument
When they run the Macro, here is where it stops executing
Code:
Range("C6").Select
ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data!$A:$Y" _
, Version:=xlPivotTableVersion14)
Now, the problem is that I am unable to recreate the error, and it always works fine for me. I am the 'excel guy' around the office, but I'm still learning VBA, so I am not entierly sure what is going on in this section of code, but I generally follow it.
We are trying to create pivot cache from an external .csv file.
This works fine when the csv file is small (~500 mb).
Attaching sample code for reference -
Code:
Dim cConnection As ADODB.Connection
Dim rsRecordset As ADODB.Recordset
Dim pvtCache As PivotCache
Dim pvtTable As PivotTable
Dim SQL As String
''Creating ADODB connection object
[Code] ...........
I have placed ActiveX checkboxes on my worksheet. I would like to have some code that would uncheck them all at once, rather than some really long code unchecking each one individually (which is what I currently have). Is there code for this?
View 3 Replies View RelatedI am using Excel 2010. I am extracting text and data info from a single cell and inserting the info into different cells. Let's say the following formula is entered into cell M28:
=IF(MID(J28, FIND("IM", J28),2)="IM", MID(J28, FIND("IM", J28),10), "")
This tests cell J28 to see if the text string "IM" is present in that cell.
If it is, then the text "IM" plus the following eight characters become the result in cell M28.
If the text string "IM" is not present in cell J28, I want the contents of cell M28, that is the formula itself, to be deleted from cell M28. EG below:
=IF(MID(J28, FIND("IM", J28),2)="IM", MID(J28, FIND("IM", J28),10), function here to delete this formula from this cell)
( logical test ) ( value if true ) ( value if false )
Using the code below I can't clear the clipboard, the range still selected after copy and paste. Obviously, "Application.CutCopyMode = False" failed. Is there a way to deselect/clear? Using Excel 2010.
View 4 Replies View RelatedI'm using Excel 2011 for MAC.
What I need is to clear and lock some cells based on a drop down list selection.
The case is like this:
In Cell [E14] is a drop down list : 0,1,2 or 3
Cells: (D19:E19), (D21:E21), (D23,E23) Merged are PART DESCRIPTION
Cells: [G19], [G21], [G23] are COST
So I need when 0 is selected in the dropdown list, all of the above cells are cleared and no input is permitted, Cells locked.
If 1 is selected, just the first row is allowed for data entry and the other 2 are cleared and locked as this values populate another worksheet for calculations.
So far I did this code:
Private Sub Worksheet_Change(ByVal Target As Range)
If [E14] = "0" Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("D19:E19").Locked = True
ActiveSheet.Range("G19").Locked = True
ActiveSheet.Range("D21:E21").Locked = True
ActiveSheet.Range("G21").Locked = True
ActiveSheet.Range("D23:E23").Locked = True
[code]...
It works fine to lock the cells, but I can not fined the way to clear the values previously stored before locking the cells.
I am trying to clear the contents of a range of contiguous cells (containing formula) in Excel 2010 64bit. But it is taking a LONG time (read >30min to clear a range 288 x 100). Originally was doing it in VBA but it was taking too long so I started stepping through the macro and identified that the slow down was occurring on a particular sheet only. So the problem is in Excel....not VBA because it is still very slow when deleting the same range in Excel without using VBA.
I've tried all the usual suspects including: Application.EnableEvents = False (run from the VBA Immediate window when using Excel without VBA), Setting to Example of slow clear contents5.xlsxExample of slow clear contents5.xlsxManual Calculation, Turning off screen updating, deleting all the conditional formatting on the sheet, removing any data validation from the sheet (yes all these at the same time). There are no links to external workbooks. I've even tried in VBA changing from .ClearContents to .Value = vbNullString. All to NO AVAIL! By way of background the file is about 72Mb #.xlsm.
If I try to clear contents of larger ranges on other sheets in the same workbook it is instantaneous. If I copy the problem worksheet off to a new workbook, I can delete the problem range almost instantly. Why deleting a range of cells should take so long! Again to clarify, I am using the terms "delete" and "clear contents" interchangeably, but they both mean "clear contents" (not delete and move up cells). P.S I've also looked into the reported conflict between Excel and Google Desktop Office Add-in (Office Button->Excel Options->Addins->Com Addins and deselect Google Desktop Office Addin) but I didn't have Google Desktop Office Add-in so it isn't that.
How do i deselect grid-lines in Excel 2003 so as the sheet is clear.
View 6 Replies View Related