I have a macro that I found on the net for copying a filtered selection and copying to a new sheet. I would like to alter it slightly so that it just clears the sheet called 'Interval tasks' instead of deleting it and creating a new one.
View 8 RepliesI need to remove all existing page breaks in a document and add a page break every 72 rows. I've tried some similar codes from this forum with other functions that I don't need in it.
View 7 Replies View RelatedI want to delete a sheet in a macro but when I run the macro, I always get a message warning and I have to answer the msg box to delete the page. Below is the macro command I am using.
Sheets("Tel").Select
ActiveWindow.SelectedSheets.Delete
I don't know if a macro is necessary, but the built in Insert -> Insert copied cells -> Shift cells down function can't accomplish what I require.
I've pasted in two example tables for what I'd like to do. For the first table, the data in column C is a name and the cells from columns D-Z, rows 7-10 is what I want to copy and paste from row 12, column D; row 13, column D; etc, etc. However, I want the full rows below the name in column C to move down when I paste from column D. Using the Insert function on the Insert blade only gives me the data in columns D-Z moving down.
The second pasted table gives an example of how I want it to look.
A
B
C
D
E
F
G
H
I
[code].....
I have searched these boards (and found wonderful ideas), but can't find a better solution.
Is there a quick way to clear all the autofilters on a page (or all of them in 1 row)?
I am using this code, but it is very slow.
For i = 1 To 21
Selection.autofilter Field:=i
Next i
I need a macro that will print a hidden page that is linked to the page I have active.
It needs to be able to find the correct hidden page that corresponds to the active page because there are multilple pairs of the hidden/visible pages. The name of the hidden page is the same as the visible one with "printout" added to the end. When created, the sheet code names are sheetn and sheetm; n and m being consecutive numbers.
I'm having a problem with a macro clearing a formula in a cell. I have the same type of cell that doesn't have the problem but I can't find the difference between the 2 cells or difference in vb that's making it happen. I have to intentionally cause this to happen but don't see why it's happening. Do I need to attach workbook and describe what's happening? I have been copying and pasting from different sources as well as paying to have it created/started but it was expensive(for me) and I make nothing off of it, just use it at work. I am not proficient in Excel or vb but I'm desperately trying to learn as I go so as not to fork out a few hundred dollars again.
here's atleast the vb for the macros:
VB:
Sub clearclientinfo() '
'
'
'
Sheet5.Select
[Code].....
i'm trying to do sounds simple enough. I have 2 Columns of data, example;
1-Apr 1423
1-Apr 1426
24-Apr1430
8-May1454
8-May1460
15-May1500
15-Jun1525
15-Jun1555
15-Jun1556
I want to clear (not delete) the cells with a duplicate date in them while keeping the cell with the number next to it. So in this case they would be A2, A5, A8, A9. I've come up with a code that looks pretty logical but it doesn't work. Here's what it looks like.
I've been pressing on with my project and figured out that I really do not know the difference between Deleting and Clear contents. I really thought they were the same but am now unsure. I guess I have 2 questions. Which is better to use? what I'm doing is removing old data and importing new data. and which is more efficient for writing code.
Sheets("Bodies"). Range("A2:Z65000").ClearContents
Sheets("bodies").Range("a2:Z65000").Delete
Im trying to make life easier by setting up some sort of macro function that will clear the contents of all the cells except for the ones that have formulas in them.
I don't want to delete the 1st row as it contains the titles for each column.
i need a macro that will select 0's in columns h-j.
View 7 Replies View RelatedIs there a way to delete or clear a row on exit if no end date has gone into it. i.e. if the user clicks on the X button in the top right hand corner.
Every time the user clicks on start the date goes into the next empty row, if finish is clicked then the end date will go into the cell next to it. See example below. Here the user has started and ended correctly
column
AB
StartEnd
24/10/09 24/10/09
24/10/09 24/10/09
In the example below the user has forgotten to click on the finish button when they first clicked on start, they exited excel instead and no end date was inputted. When the user clicked on start again the start date went to the row below, however as there was no end date the last time, this time when they clicked on end button, then end date went to the first BLANK CELL
column
AB
StartEnd
24/10/09 24/10/09
24/10/09
I would want the delete row or clear row option, which ever would work best, to happen if the user clicked on the X in top right and corner or Exited excel via File.
Ideally I would like the row cleared and not deleted, I have read Clare Watts thread and we both seems to be having a similar problem, I have used some of the examples from her thread but non seem to work. So I now wish to go with a delete or clear row option on exit, if possible.
The row changes every time. The column for deleting or clearing would be A to AB. The end date goes in to Column B
So if the user clicked on start but did not click on the Finish Button then the last row with no end date would be deleted or cleared on exit from column A to AB
column
AB
StartEnd
24/10/09 24/10/09
24/10/09(This is deleted or Cleared of exit via X top right hand corner)
I am trying to write a macro to find all cells that are returning cell errors (ie: #REF!) within a named range, to select only those cells, and then to clear the contents of only these cells. It is my understanding that replacing or altering the value is not an option because doing so will return a "" string value, instead of a clear cell. Replacing the cell's contents with a =na() is also unacceptable...these cells to be completely clear. I was unable to piece together a macro from searching around this site and thought it would be best to just ask.
View 4 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?
i created a work sheet with the help of the wonderful people on this forum. my boss loved it! one problem they now want it to automaticly take out a date and time of the cells when the date rolls around again.
example:
12/25/05 late 00:20:06 when 12/25/06 rolls around they want the system to automaticly see it and delete it out with the amount of time they were late or sick ect.... or if the sup was out that day and comes in the next day the system will see that the date has past and will up date the info. the other catch is i need it to move everything up one space when it deletes somthing. i want it to read j18 and k18 as one and so through j and k 40 same for l and m n and o ect.... i attached the sheet
When I view a sheet under Page Break Preview, it shows the Page numbers in the centre of the Page. While I am aware that it would not print the page number I was wondering if there is an option to remove/hide the page numbers.
View 3 Replies View RelatedI have a report that contains several rows of data. This data is separated by page breaks, so for example, there could be 1500 rows, separated into 90 pages by page breaks. Each range may have anywhere between 35 and 45 rows, but some of those rows may contain no data.
I would like to loop through the cells column A, look for a certain criteria, then delete the range around that certain cell that is contained within the page breaks.
I have code that will loop through the cells in column A. I also managed to create code that will delete a set range but since the ranges aren't always a uniform number of rows I need another fix.
I would like to protect the worksheet from modification by other users except column A ( suppose A1: A15).
I have been doing this by removing Locked on format cell and then Protect Sheet on Review tab.
Is it the right way or there are other more reliable way?
I have the following forumla: =SUMPRODUCT(($C$13:$F$74>0)*1)
I need this formula to compute only if B13:B74=A4. I am not too sure how to add this in.
I did modify some codes inside my addin, then did save the modified addin file by pressing the save icon from the VBA window..........but i found that the addin file was saved without the modification made to it. so i lost all modification did to the file. i donot know what i did wrong ?
View 2 Replies View RelatedI have been modifying the options available on my right click menu, getting rid of about half of what was there and adding items I use quite often like Paste Values, Paste Formats, Paste Formulas, Sort..., AutoSum, and Format as Percent, as well as adding group dividers. I have it just the way I want it, but I have one issue. If the clipboard is empty, all of the paste buttons are disabled on the Standard toolbar and in the Edit menu as are the Paste and Paste Special... items on the right click menu, but the new paste items I added to my right click menu are still enabled. If I click on one, I get a "PasteSpecial method of Range class failed" error because I have no range selected. I got around this by adding On Error Resume Next to the macros of my added paste functions - not elegant, but functional. Is there a way to have those items appear grayed out and disabled on my right click menu when the clipboard is empty as opposed to my current workaround?
View 9 Replies View RelatedI have an Excel template with 10 Worksheet(tabs). 8 of the sheets have predefined charts built based on the data in first 2 sheets. Through a web application user downloads data on the first 2 sheets. A macro is then executed (at the end of the download) to Update (through Macro) the charts in the remaining sheets and the sheets are password protected. The idea here is that user can generate predefined charts from the downloaded data but cannot modify any information. In addition to that user need an ability to add additional sheets to generate custom reports/charts.
So my requirement is to protect the 10 predefined sheets from modification including the modification of sheet name/deletion of sheet but allow user to add additional sheets. Protecting the workbook does not allow user to add additional sheets. Protecting individual sheets doesn't prevent user from renaming the sheet names.
Search Excel Database - Modification ...
View 9 Replies View RelatedI have a small sub routine below I would like to make a slight modification to. The routine currently references formatting relative to the Offset statement in bold below. Instead, I would like the formatting to come from a cell address listed in a cell just to the left of the cursor when the statement is encountered.
View 2 Replies View RelatedI'm using this formula in a Conditional Format rule.
=CELL("row")=ROW()
Can anyone help me modify it, so that only the active cell is formatted instead of the entire row?
way to save user-settings at runtime, preferably in the same or another module. For example: how can I save a bundle of variables such that the next time excel is started and the workbook is opened, the user can continue to work with his personal settings?
Background:
Using the macro, I am writing on atm, a user will be able to adjust the macro according to his input-samples. Here the columns and rows of an input can be varied to fit the macros requirements. It would be great if those settings could be stored, irrespectively of where the file currently resides. That is, best it would be, if the source-code itself could be changed ... maybe by adding another module?
i was able to setup a email code, which will automatically extract the unique row reference data and sends the email. However, i am struggling to add couple of things, i searched most of the web portals .. wasn't lucky.
1. while email drafting i need to use the data from Column B onwards
2. i need to insert the default email signature during email draft ...
it's possible to set a "key" for a vlookup so that one of the parameters of that key can be any value? For example if the data set key used in the lookup contains a concatenation of 4 parameters (a_b_c_d), is there anyway to modify the lookup array key so that the "c" parameter can be anything, ie. something like a_b_*_d ?
View 3 Replies View RelatedI'm using a script that when you double click on a cell containing Data Validation > List it changes to a combo box that has an autocomplete/increased columns view...It's working great, however there are two small modifications I would love to do and am not having much success with. Currently, you double click the cell w/the Data Validation > List in it, it then changes to a combo box and you can auto complete using the keyboard or select the dropdown and choose from an increased list. However, I would like to be able to simply single click on the cell, have it change to the combo box and auto expand the list if possible.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsList As Worksheet
Set ws = ActiveSheet
Set wsList = Sheets("Rep Assist Report")
Cancel = True
Set cboTemp = ws.OLEObjects("RepName1")
On Error Resume Next
With cboTemp
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error Goto errHandler
If Target.Validation.Type = 3 Then.....................
We are using Excel 2007.
I wanted to add the date of the last modification of the file to the printed pages, so I googled how to do it and found a useful answer on this from from 10 years ago. It said to press ALT+F11, then on the left side go under VBAProject, there go to ThisWorkbook and there paste the next code:
Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
For Each wk In Worksheets
With ActiveSheet.PageSetup
.LeftHeader = "Last Modified on " & ActiveWorkbook.BuiltinDocumentProperties.Item("Last Save Time")
.CenterHeader = ""
[Code]....
Then I saved it and closed. Like I said, it worked perfectly.
The problem is that when I open the file after closing it, it does not appear, and I have to paste the code again every time I open it. Not only is this not comfortable, it also counts as a modification, so even if I haven't changed the work sheets themselves, just wanted to open and print it, it shows today's date.
My question is, who to I save it permanently so it will remain always after the first time I created it?
While we're at it, if there's a way for that code to appear on every NEW Excel file I create automatically