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.
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 ...
I suddenly can not edit in a cell by double-clicking on it. I can only edit in the fx bar on top. Other sheets in the workbook still work normally. What causes this, how can I fix it and how can I make sure it doesn't
View 5 Replies View Relatedi've been programming a bit in excel and quite new to the whole thing but managing nonetheless... however, after having built a couple of macros in excel, i can now no longer delete all the worksheets listed at the bottom of the workbook.
View 9 Replies View RelatedA couple years ago i made a giant spreadsheet and had 2 custom toolbars to run macros from to manipulate the sheet.
This year i re-did the sheet, meaning all my old data was replaced (it's a giant schedule basically).
However, the Macros in the custom toolbars stayed exactly the same and performed the same function and are directed to the exact same cells, rows, columns, etc..
But now when i hit the buttons on the custom toolbars to run my macros, i get the following message... all the time...
**
A document with the name "NHLsheet.xls" is already open. You cannot open two documents
with the same name, even if the documents are in different folders.
To open the second document, either close the document that's currently open, or rename one of the documents.
**
Trouble is, i have only 1 document open!
There's something in the macros that thinks i'm trying to open a new document but i'm clueless as to where or why.. they are very simple macros
that do not require the opening of any new workbooks..
All they do is point to another sheet within my open workbook, "NHLsheet", copy a particular row from that sheet, and paste it to a specific spot onto another sheet within my workbook, "NHLsheet".
Here is a sample:
At work we use a connection to a SharePoint List within Excel to refresh the data in the files.
When excel is refreshed I get this error (this happens for all users in the team and multiple workbooks)
Image 1.png
The weird thing is I can export the SharePoint list to an excel file and it is fine but as soon as i go to refresh it (Data>Refresh All) it produces the above error.
Learn about the "copy only visible cells" when I've filtered down a filter. Now Excel copies that way by default everytime I copy multiple rows when I've filtered. But NOW I want to copy everything that is hidden between the filter as well, i.e. how it did it before.
How can I tell Excel to do that?
Microsoft Excel is no longer displayed at the top of the screen, and I can't see my worksheet tabs at the bottom of the screen.
View 3 Replies View RelatedI attached 2 examples. The first one is before the screen flickering fix I got from Andy Pope. The second is when I added the fix. When you open Example List 1 and you enter 0 at E180, the screen will flicker, but it will change (red) Watching to (green) Planning to Watch in G180.
When you open Example List 2 and you enter 0 at E180, the screen doesn't flicker anymore. The status Watching in G180 still changes to Planning to Watch, but it stays red while it has to become green. How can I have the color changes and no screen flickering? Marco:
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!
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)"................................
Currently, Im running a button macro. When this button is clicked, the table from "315 Employee Data" will copy the names from column C to "315" sheet B12 onwards.
Now, what i want is when I update the employee data on "315 employee data" sheet, I want "315" sheet to automatically match the names from "315 employee data", delete and add names when I add or remove employees from the column the next time i click on that button again.
After the above is achieved, when i add new employees,run the macro and it displays the updated list of names, I want "315" sheet column A to do automatic numbering.
I have attached the file for your reference.samplesample.xlsm
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 RelatedI 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 RelatedI 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 RelatedEverytime 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.
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?
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.
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
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 RelatedIn 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.
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]....
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 RelatedI 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...................................................
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?
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?
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 Relatedi 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]....
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].....
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?