Macro To Work On Increasing/Descreasing Data
Sep 14, 2006
I have a spreadsheet with a worksheet for each month, so as a new month begins I add a new worksheet using a macro
Each worksheet has 5 columns:
A = Vendor, B = Date, C = Debits, D = Credits, E = Balance
Row 30 contains the totals for columns C, D, & E, cell A30 contains the text Totals
The problem I have is occaisionally extra rows are added so the totals may not be in row 30
Is there any way that the macro can be changed so that it looks for the word Total in column A and then reads the contents of the corresponding cell E? to transfer that total to cell E2 on the next worksheet
View 9 Replies
ADVERTISEMENT
Jul 31, 2009
I'm searching for a macro that will allow me to print rows that are constantly changing in number. Attached is a sample of my workbook. The workbook has worksheets by month. There are data from five sales people on each sheet so each sales person has his own section. I have a print button within each section so he can print only his section of the page. No problem creating that macro.
However, at least twice a week the sales people are adding rows or moving a row of data from one month to another month, so the print area is constantly changing.
View 4 Replies
View Related
Sep 5, 2006
I'm facing a charting problem and i can not find any solution with a search here, anyway this is the problem: When I choose a week I would like to see the results of the 5 previous week also.
View 4 Replies
View Related
Jan 17, 2013
lately I have promised to program a little macro which looked easy for me to do but on which I have now spend a considerable amount of time without finding the problem why it is not running. I am working under Windows XP with Excel 2010, here is what i was trying to do:
In a worksheet there are two columns G and H. In both columns it shall be possible to select a number of possible entries from a list. For column G this is done via data validation, no problem. Now, the choices which are there in column H shall depend on the entries made in the appropriate row of column G. Column G thus functions as an overarching category. If I choose "Tree" in G, H would give me branches, leaves, apples. If I choose "car" the choices would be wheel, engine, gasoline etc. This shall be done by a Macro:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cat1 As Range
Dim cat2 As Range
Dim x As Integer
Set cat1 = Range("G:G") 'Task Group
Set cat2 = Range("H:H") 'Task Category
[code].....
When running, it gives me Run time error 1004: Method "Range" of object '_Worksheet' failed. Debug highlights the row
Code:
With Range(Cells(ActiveCell.Row, 8))
View 2 Replies
View Related
Nov 23, 2009
I have a macro code which gets the file list from a folder i specify and puts it in to an excel sheet as a column. I have then made a drop down list from this so the user can select the file they want.
From this file i wish to copy the data on a sheet that i specify. For example the sheet "dump" from file FR7_19.11.2009.xls (which will be a closed workbook) and paste its content in to the sheet "dump" in Summary.xls
I have a macro which opens up the closed workbook FR7_19.11.2009.xls and copies the sheet "dump" and then creates a new sheet of the same name and content in my current workbook (Summary.xls). However when i wish to select a different file to load in to Summary.xls "dump" the formulas i have been calculating information from this sheet all come up with #!Ref errors. I know this is because the macro i use deletes the old dump sheet before re adding a new one containing new data.
I am therefore looking for a macro which will simply just copy and paste the data from any file i select in to a sheet named "dump" as the data is always set out the same in every file but the values are different. I assume this will then mean that any formulas i use relating to this "dump" sheet in Summary.xls will work because the sheet is no longer being deleted and re-added the data within it has just simply been copied over.
View 2 Replies
View Related
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
Aug 20, 2014
I need to read or clear all the line in specific range which will be increasing dynamically. Problem is "Selected File List" table range is not specified. New file chosen with browse will be added to "Selected File List" dynamically. Number of file can't be predicted.
vbaStk.JPG
What I've tried so far is, keep track of the browse button click and add the file path to arraylist. After that, (Row number 9 which is start line + arraylist size) to get the number of line of end of the table. But due to some requirement, I want to read the file from excel file. Something like -Read until found blank line or border bottom or something.
I'm new to VBA and I'm not so sure what I'm doing with vba codes
View 1 Replies
View Related
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
Jan 2, 2009
I am want to copy a formula across several work sheet and have the formula always take data from previous work sheet.
2) I am working with this formula =C12+INDIRECT((MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)-1)&"!"&"C12")
and it comes from this thread http://www.excelforum.com/excel-gene...orksheets.html. I have included a worksheet attachment that has explanations
View 2 Replies
View Related
Mar 27, 2014
I have to pay 190000 $ in 180 months. I need a formula to establish an incresing rate for every month. I have a simulation on a paperwork, but I can't figure out the formula. On the paperwork the first rate in the first month is 875,21 $, the second one is 876.99 $, ..., and the last one in the last month is 1258,97 $.
View 5 Replies
View Related
Oct 22, 2007
I'm just fiddling around with excel at the moment and have a created a cell with a value of 16. It represents the number of days an event has been running for.
I'm not sure how to make it increase by +1 every day without me having to open excel and change the value in the cell.
View 2 Replies
View Related
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
Jun 11, 2014
I'm trying to find a formula that increase a cell by 1/3 that will work with auto fill.
For Example if auto-filling a column:
3
3
3
4
4
4
5
5
5
Or if auto-filling a row:
3 3 3 4 4 4 5 5 5
View 11 Replies
View Related
Nov 26, 2008
in a1 I have a number 1, a2 is a number 2. I want to keep on counting down to number 7691. Just do not know how to do this.
View 5 Replies
View Related
Sep 21, 2009
I have a worksheet that I need a piece of code for, Cell E1 is an amendment number that increases every time a new one is put out,
so they can be tracked.
Every time the sheet is saved the value in cell E1 needs to increase by 1.
View 2 Replies
View Related
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
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
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
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
View Related
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
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
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
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
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
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
Mar 29, 2007
I am doing a hlookup on a range (possibly over 100 rows). My formula is working but my problem is that it takes too much time to go and manually edit the formula to change the row index number and increase it by one. For example the row index number has to change in order to pull in the right data....eg 5,6,7,8 etc up to over 100. Is there a quick way to do this or do I have to manually enter the row index numbers in over 100 rows?
I am attaching a SAMPLE of data. The actual worksheet is much more larger.
Hlookup.xls
View 8 Replies
View Related
Jul 31, 2014
I need a series of cells to increase by varying amounts after each month, however this needs to happen automatically so if I send the spreadsheet to someone else, it will update for them automatically. I'm hoping that I can set up a function that will ONLY increase the cells after 4 weeks after they select a date from ANOTHER drop down menu (i.e they select the starting date from cell A1 - then input their starting numbers into other cells which then increase on that same date, 4 weeks later).
The cells that need to increase already have a formula in them as well; as this number is their predicted 1RM based off formulas in other cells.
I will attach a screen shot when able.
View 2 Replies
View Related
Mar 9, 2014
My wife and I run an apartment complex with 2 employees, a manager and a maintenance man.
We use excel to produce a paystub for these employees twice a month.
There are a number of variables including incentives for renting apartments, overtime, variable hours in a pay period etc.
We have a separate spreadsheet for each employee and a separate sheet tab for each pay period named for the pay date... e.g. the first paystub had one sheet named "Jan 15"... the next pay period the paystub had 2 sheets: "Jan 31" and "Jan 15". The third paystub had 3 sheets and so on.
The paystubs have a column with the numbers for the pay period and then a column with the year to date sum (for straight pay, over time, federal withholding, social security, medicare, state withholding, etc.)
Right now I edit the formulas for all these sums for each payperiod. e.g. =sum('Feb 15':'Jan 15'!B35) for the third pay period is editted to =sum('Feb 28':'Jan 15'!B35) for the fourth pay period.
I would like to be able to just copy the prior spreadsheet and not have to find and replace values in 11 spreadsheet cells for each pay period.
I have figured out how to get the name of the current spreadsheet to appear in a cell on that sheet using VBA... but not how to get that cell content to be part of the arguments for =sum(. on the formula bar...
View 2 Replies
View Related
Feb 20, 2014
I should know this and I'm sure its something very simple that just wont come to me. I have two cells, one has "total billable hours" in a month, the second needs to calculate how many days based on that number. So it needs to increase by 1 for every multiplier of 24 in the first cell.
View 3 Replies
View Related
Jun 10, 2014
I want the first 60 rows of column C to be constant meaning C1, C2, C3, C4..C59 and after 60 rows it should start again with C1, C2, C3.....C59 rather than C60, C61, C62. In other words i+1 but after 60 rows i should be reset to 1 and then again increase by 1. how can i implement these changes
[Code] ......
View 2 Replies
View Related
