Macro To Work When Cell Changes Value

Sep 5, 2008

Everytime a cell (say B18) changes value I want the following to happen:

If it changes to "Annual" I want row 20 to ungroup

If it changes to "Quarterly" I want rows 22-25 to ungroup

If it changes to "Monthly" I want rows 27-38 to ungroup

From what I can see on other discussion groups I need to enter a code on the worksheet from view code... but I cant work out the code that I would need to enter.

View 9 Replies


ADVERTISEMENT

Macro To Work Out Top 3 Cell Occurrences

Oct 16, 2013

I have a database full of part numbers for example 0EV2310A06G01JU:S5065

These can vary to having a vareity of symbols/letters/numbers (I am not sure if this makes a difference!)

writing a macro to run a report to show the top three reoccuring partnumbers.

The spreadsheet is currently 1845 cells big but this data will change on a weekly basis, the layout will however remain the same. The column that the part numbers are in is column A.

Is a macro the right way to be approaching this if the data is going to be changing on a weekly basis?

View 2 Replies View Related

Macro Does Not Work From Any Selected Cell On Spreadsheet?

May 21, 2014

I created a very simple macro, which actually works.

There is a button on my spreadsheet, so the user can launch the macro using this button.

[ Code] .......

At the moment the user clicks on the button, the part of the macro which is hiding the rows will not take place if the cell activated at the moment of launching the macro is in the range below :

The table on my Excel sheet covers range A5:E49

If the active cell is anywhere in A6:49 or C5:D24 or C25, the hiding part of the macro will not work. Launched from any other cells on the spreadsheet it works fine.

The rest works fine and I am not getting an error.

To solve this I just need to add the line Range("G9").Select and it will work.

But I would like to understand why it does not work from the cells given above. My sheet is not protected and I unlocked the cells just ion case.

View 2 Replies View Related

How To Get A Macro To Work With Any Work Book Name

Aug 24, 2009

i have a made a macro that copies info to a new sheet now that is working great but if i change the name of the work book it wont work any more so i need the macro to work with what ever name i give the workbook

the current name is

AVERAGE PRICE (update 2009) Mimmos Armico 170809.xls

i have attached the code in notepad ...

View 8 Replies View Related

Macro Doesn't Work On Button, But Fine From Macro Menu!

Dec 15, 2008

I've recorded a macro that copies an entire tab into a new spreadsheet then goes on the copy and paste information from one tab to another.

When I run the macro from the Tools>Macros menu it works perfectly.
But when I copy the code and add it to that of a button it fails and posts the following error: Run-time error '1001': Select method of Range class failed.

The first attachement shows the code for the macro as it is alone, and the second shows how I simply copied and pasted it into the 'view code' window of the button.

Needless to say I'm a beginner at macros and only every record them, I can usually make stuff work that way but this has me stumped!

View 12 Replies View Related

Macro Not Work On Another Tab

Feb 4, 2009

To run a macro from the first tab of the workbook but have it do it work on another tab.

In the workbook in question I have a tab called "input" were certain information in updated and is linked or used in formulas on other tabs. Some of the tabs have macros that do various functions. I would like to set up one macro button on the "input" tab that would run the macros on the other tabs instead of having to run each one individually from its own tab.

Below is on of the macros that I would like to run from the "input" tab and have it update the "AP Accrual" tab

Sub CopyPriorMonthAccruals()
ActiveCell.FormulaR1C1 = "=EOMONTH(Input!R[5]C,-1)"
Columns("L:L").Select
Selection.Copy
Columns("Y:Y").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("B3").Select
ActiveCell.FormulaR1C1 = "=EOMONTH(Input!R[5]C,0)"................................

View 9 Replies View Related

Make Macro To Work On PDF?

Jul 1, 2014

I've got a PDF file that I was hoping to convert into an Excel sheet, but it doesn't look like that's possible, xince I'm working with only Adobe Reader, not Acrobat. Anyway, I need to compare 1 column of this report to another report that IS in Excel. Is there a way to have a macro compare an Excel file with a PDF??

View 2 Replies View Related

Do While Macro Seems To Work In Stages

May 28, 2009

I have a macro which loops through data, deleting those rows that are marked with a "Y". However - it appears to always want to do this in three stages. The first time I run the macro, it delete's a large chunk of those marked rows - but it nearly always leaves two rows. The second time I run it, it deletes one of those two remaining rows. And then I run it again and it deletes the final outstanding row. I'm sure i've done something stupid here - the code is below

View 2 Replies View Related

Macro To Work Across Many Workbooks?

Apr 17, 2007

I need to remove hyperlinks across probably 30-40 workbooks, is there a way of doing creating a macro that would do this across many instead of 1 at a time?

View 9 Replies View Related

Macro: How Work With Weeks

Oct 22, 2008

I have a file with date from the whole year.
Now i want that he splits for me in the file the weeks. So that i have all the info for each week. (seperating by 2 or 3 blank rows)

A second thought was to copy paste them to a new sheet for each seperated week.

and if i just splits them by inserting blanks rows between the weeks. I was wondering if it is possible to make a pivot for each week

i thought maybe working with boxname?

View 9 Replies View Related

Transpose Won't Work, Perhaps A Macro Will

Apr 7, 2009

I am sent an email that contains multiple blocks of information.

They're broken down like this:

Title
Link
ID
Department
Date
Status
Value
News

There are about 100 of these blocks of text in the email, and some of them are duplicates. What I'd like to do is copy them into Excel, and run a macro which separates them into the appropriate column and removes the duplicates. The problem that I'm running into is some of the blocks have a value (highlighted in red above) and others don't have anything at all. I'm looking for a solution that will be able to evaluate the text and if the cell after Status isn't value, I'd like it to insert a blank cell, a cell that says ignore, or something that will keep the format correct.

View 9 Replies View Related

Macro Does Not Work Anymore

Sep 19, 2006

I've been using a Macro that was written in an Excel spreadsheet and it's been working fine. For some reason, the Macro does not work anymore.

The Macro is written in Visual Basic and it is launched with button.

What is the first step I should take to debug this?
I think it's something obvious I'm missing.

View 9 Replies View Related

Vlookup Does Not Work In A Macro

Jun 22, 2007

why the macro "try()" in the attached workbook does not work?

I get the error message: "Unable to get the Vlookup property of the WorksheetFunction class"

Sub try()
Dim i%
For i = 1 To 35
Sheets("Sheet2").Select
myValue = Cells(i, 1).Value
Sheets("Sheet1").Select
n = WorksheetFunction.VLookup(myValue, "A1:A11", 1, True)
Sheets("Sheet2").Select
Cells(i, 2).Value = n
Next i
End Sub

View 3 Replies View Related

Work Out A Formula For My Spreadsheet Which I Use To Work Out Cutting Lists For Timber Frames

Jan 11, 2009

i need to work out a formula for my spreadsheet which I use to work out cutting lists for timber frames. I need it to work out if the width of a job is for eg 2400mm i need to work out how many timber studs I need so the space between each stud is between 400mm and 500mm and this will need to work for a range of different sizes of frames. I have it written at the moment and it just devides the width by 400 and gives me a amount of studs but it would work much better if it could space them between 400 & 500.

View 4 Replies View Related

Work Accurately With Times To Calculate The Work Progress Of The People In The Workshop

Mar 3, 2009

In a project i am compiling i need to work accurately with times to calculate the work progress of the people in the workshop thus....here goes....

I have in work book #1 (7) sheets mon to fri + complete week + a sheet where all job numbers are collected.

From monday to friday the workmen log their times as a start time and a end time. This has to be then calculated to a total hours:mins spent per job, wich in turn then has to be calculated to a total hours:mins spent per day. And the on the complete week sheet recalculated as a total time worked per week.

View 9 Replies View Related

Advanced Filter Macro Does Not Work?

Feb 15, 2013

I've written a macro in an attempt to automate the advanced filtering in Excel.

VB:
Sub FilterData()
Sheets("Filter").Select
Range("B10").Select[code]....

View 5 Replies View Related

Work On Other Files While Running A Macro?

Nov 28, 2008

I have designed a macro which consists of a form through it takes the input into excel sheet. The macro is running fine and I dont have any problems with it. The problem I am facing is that after I start running this macro, I am not able to access any other open excel files. Is there any option to make all other excel files available during running a macro?

View 3 Replies View Related

Progress Bar To Work While Running The Macro

Jul 6, 2005

I was looking at a previous post to get a progress bar to work while running
a lengthy macro. Below is the post on how to incorporate the progress bar
into your code.

That works fine expect for one thing. There is a cancel button on the progress bar, and when it is pushed the rest of my code continues running. How do I end my code if I press cancel on the progress bar?

Sub Main
Dim PB as clsProgBar
Set PB = new clsProgBar

'if your subs are being called from a userform, hide the form first using
Me.Hide
With PB

.title = "some title"
.caption1 = "Executing, this may take a while"
.caption2 = "Doing task 1"
.show
doevents...................................................

View 14 Replies View Related

Macro Combining Work Sheets Into One

Mar 14, 2008

I have the below macro in a workbook that I need to combine the two sheets into one, (sheet1 and sheet2). Both sheets in use column "A" for id, but for some reason it is only copying over sheet one? One sheet has 12 columns and the other has 13, not sure if that is making the difference?

View 13 Replies View Related

Macro Work Like Toggle Button?

Jan 8, 2012

I dont like the way the toggle button looks. So i wanted to create a button that when pressed would recognized if row 19 was hidden or not hidden..

Then if it was already hidden it would unhide the row 19

or if it was not hidden it would hide the row 19 when clicked

Is this possible via a macro?

View 1 Replies View Related

Macro To Work On Internet Explorer?

May 23, 2012

Can we get a macro in excel where we can browse web page and get the data from a internet based webpage.

View 3 Replies View Related

Macro Doesn't Work In 1 Out Of 4 Worksheets

Nov 30, 2012

i got a long macro to delete all rows with contains a column with the value 'xx'. The macro works in sheet 2K, 2F and 1G. But the weird thing is it doesnt work in sheet 1K (i copied the exact same part of the macro out of the other sheets). Is it possible that there are to many rows in the sheet (sheet 1K has 24.000 rows, 50% or more will be deleted with the macro. The error says bug in the line: If Not rng Is Nothing Then rng.EntireRow.Delete It stops showing all the rows wich should be deleted.

Sub GrondeigenarenVerwijderen()
Application.Goto ActiveWorkbook.Sheets("2K").Cells(1, 1)
Dim DeleteValue As String

[Code]....

View 5 Replies View Related

Make Macro Work Conditionally?

Sep 13, 2013

I have a fairly simple macro (below). I want it to do one more thing.....not run if the filters have not been changed. I would like to prevent the error that occurs if someone tries to run it and there are no filters active.

Private Sub AutoFilter_Remove()

Sheets("Data").Select
ActiveSheet.Unprotect "password"
ActiveSheet.ShowAllData

[Code].....

View 9 Replies View Related

Getting Macro To Work On Several Ranges On Same Sheet

Jun 24, 2014

I have the following Macro:

Code:

Private Sub Worksheet_Change(ByVal Target As Range) ' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
Set rng = Target.Parent.Range("C13:D25")
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub

[code]....

Which does not allow entry to range C13:D25 if D12 is empty. Works great!But I need it to work across ranges E13:F25 if F12 is empty, G13:H25 if G12 is empty etc.

I tried copy pasting the same macro over and over again but renaming it to e.g. Worksheet_Change2 causes the Macro to stop working completely.What am I doing wrong?

View 5 Replies View Related

Apply Macro To All Work Sheets

Dec 20, 2006

How do I apply 1 Macro to all the sheets in a Work book. That is one Macro should execute the function in all the worksheets of the Workbook.

View 9 Replies View Related

Trying To Get A Workbook.Open Macro To Work

May 29, 2007

I'm trying to get a Workbook.Open macro to work. I"m using the exact name for the location. For example:

Workbooks.Open ("O:AdministrationPM Weekly's2007PMWeeklyWE")

My other locations are working fine. For example I am using:

Workbooks.Open ("O:AdministrationDCS Week EndingStats"

View 9 Replies View Related

Macro That Doesnt Work In 07 But Works In 03

Oct 28, 2008

I've been searching around here to see if anyone had an answer about codes and macro changes from 03 to 07. The closest thing I found was something about lists being tables and such. However, I am still unsure of what the issue might be in my case.

Sub ArrangeColumns()
'
' ArrangeColumns Macro
' Macro recorded 3/7/2008 by ****
'

'
endRow1 = ActiveSheet.UsedRange.Rows.count + 1
Range1 = "A1:O" & endRow1

Range(Range1).Select
Range("A3:O39").Select
Application.CutCopyMode = False
ActiveSheet.ListObjects("List1").Unlink
ActiveSheet.ListObjects("List1").Unlist
Columns("A:B").Select
Selection.Delete shift:=xlToLeft
Columns("I:I").Select
Selection.Cut
Columns("B:B").Select
Selection.Insert shift:=xlToRight
Columns("L:L").Select
Selection.Cut
Columns("C:C").Select
Selection.Insert shift:=xlToRight
Columns("M:M").Select
Selection.Cut
Columns("E:E").Select
Selection.Insert shift:=xlToRight
Columns("L:L").Select
Selection.Cut
Columns("G:G").Select
Selection.Insert shift:=xlToRight
Columns("B:B").ColumnWidth = 11.29
End Sub

So here, the red text is what gets flagged when you run the macro. I seem to be getting a Run-time error '9': Subscript out of range error.

I am not exactly sure what the macro does besides clean up and sort a portion of a worksheet. Also, there is not worksheet called "List1," but changing that value does nothing. Is there an equivalent command to those highlighted in red? Or, perhaps if anyone knows of a place where I may reference these commands myself, that would be great as well.

View 9 Replies View Related

Macro To Work In A Differnet Workbook

Sep 24, 2009

I have the below macro that works well

Sub Button68_Click() 'Creates delivery note

Dim stIDSelect As String
Dim stC As String
Dim C As Range

stIDSelect = InputBox("Enter RMA number you want to create a delivery note for")
If stIDSelect = vbNullString Then Exit Sub

Dim idRow As Range
Set idRow = Intersect(Range("A:A"), ActiveSheet.UsedRange).Find(stIDSelect)
If Not idRow Is Nothing Then
Dim i As Integer................

View 9 Replies View Related

Make Macro Work In Many Rows

Jan 31, 2007

I'm having some difficulty with this one. I have found several posts on how to make a macro work through several rows, but cannot seem to figure out how to apply it to my macro.

Right now I'm having to write the macro for every line and it's a bear! Here's what I'm working with right now and it's ridiculous (I've actually got through line 51, but I'll only post a few here, for your sanity!)- there has to be a better way:

If Range("C9") = "" Then
Range("T9").Formula = "=IF(K9>I9,IF((I9-H9)>=180,180,(I9-H9)),IF((I9-H9)+(K9-J9)>=180,IF((K9-J9)>=180,0,180-(K9-J9)),(I9-H9)))"
Range("U9").Formula = "=IF(K9<I9,IF((K9-J9)>=180,180,(K9-J9)),IF((I9-H9)+(K9-J9)>=180,IF((I9-H9)>=180,0,180-(I9-H9)),(K9-J9)))"
Else
Range("T9").Formula = "=IF((T8+U8)>=180,0,(IF(K9>I9,IF((I9-H9)>=180,180,(I9-H9)),IF((I9-H9)+(K9-J9)>=180,IF((K9-J9)>=180,0,180-(K9-J9)),(I9-H9)))))"
Range("U9").Formula = "=IF((T8+U8)>=180,0,(IF(K9<I9,IF((K9-J9)>=180,180,(K9-J9)),IF((I9-H9)+(K9-J9)>=180,IF((I9-H9)>=180,0,180-(I9-H9)),(K9-J9)))))"
End If............................

View 4 Replies View Related

Work Around Running Macro On Protected Workbook?

Jun 5, 2014

I cannot run a macro because I am encountering error 1004, intersect of object. i am using a protected workbook, and I dont have the password to unprotect it, but it needs to be fill out using a macro from a different workbook. Is there a way around this?

View 1 Replies View Related







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