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 have a spread sheet I have set up to calculate sales results, I have a column for each week in the quarter with the date at the top and a different page for each individual. I want to be able to enter a number to show how many weeks we have had and the spreadsheet to give me a cumulitive amount for the cells up to that date.
For example if I was to enter 1 in a cell A1 I would want the sum t work out just C3 for example.
If I enter 3 in A1 I would want the sum to be C3+C4+C5 wich is the three cells.
If I enter 6 in A1 it would be C3+C4+C5+C6+C7+C8 etc.
I am looking for a simple formula that would look at column A2-A100 and calculate the whole number for the number of the week I am on. So in cell K22, I would like it to add up the A column and spit out a number 4, then on Monday when I add a new record I would like it to automatically update to a 5; indicating the 5th week I have been tracking the data. This number is needed to calculate the average amount in a work week accurately and automatically in cells K20 & K21. New weeks start on Monday and end on Friday. No data will ever have a date of a Saturday or Sunday. I have colored the cells currently to indicate what the number should be. There is 4 alternating colors now. The color coding is just for reference and will not be used going forward. Data will be entered weekly indefinately.
View 3 Replies View Relatedi currently have a what if statement
=IF(B2>=NOW()-42,"< 6 wks","> 6 wks")
I need it to also give an option of between 3 wks and 6 weeks
Therefore all options are
3-6 WKs
< 3 WKs
> 6 WKs
I have mastered 2 but now need a 3rd.
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 have a raw data in one file and then another file contains pivot table linked to the raw data. In the dashboard i need to show last 6 weeks trend which is linked to the pivot table. Currently i am manually unselecting previous week and selecting the latest week.
Ex: This week i select weeks 5, 6, 7, 8, 9, 10 from the pivot for the dashboard. When new data is updated next week, i want to select 6, 7, 8, 9, 10, 11. I cannot delete as i need the historical data.
I am using Microsoft Excel 2010.
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)"................................
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 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.
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.
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?
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?
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 RelatedI'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"
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.