I have a piece of code that prompts me to choose which column to be filled bottom up, I would like to modify this and add it in as a macro so that it fills columns H and columns I in a bottom-up fashion
View 13 RepliesI have a sheet where a user will enter x number of rows of data.
im trying to get it so that the macro, when run, will goto the last row of Data in Colum A, and delete all rows BELOW to the end.
then select the first empty row and do the following
in A =SUM(A30:A last row of data above this one)
in B ='HASH TOTAL'
in D ='TOTAL
in E = same as A but for colum E
in F = same as A but for Colum F
in K =same as A but for Colum k
in J =same as A but for Colum J
then select the row below that one and from cells A:M
and format
With Selection.Interior
.Pattern = xlCrissCross
.PatternColorIndex = xlAutomatic
.ColorIndex = xlAutomatic
.TintAndShade = 0
.PatternTintAndShade = 0
End With
I am currently trying to adapt the following code which is in VBA (Sheet1). It currently ensures mandatory cell entry of cell C2 before saving, and if this is not done then it would not save the item into the relevant directory. I would like to adapt this code so that cells B2 to R2 are all mandatory, and if they are not filled in a message box with the cell title (which would be in B1 to R1) would pop up. If the cells are not filled in then it will not save (the same function it carries out for cell c2 currently). The code is attached to a command button, i have tried to make the additional cells mandatory by playing around with the If Trim(.cells(2, 3).value) part of the code with the AND function etc however i have had not had any luck.
View 7 Replies View RelatedI have a simple spreadsheet. A column for a persons name and 31 columns, one for each day of the month. I want to apply conditional formatting, either fill color or border color, to the date column of the current date when the spreadsheet is opened.
View 2 Replies View RelatedI am trying to set the controlscource property for a listbox but i get a "Invalid Property Value" error. I am useing the
ActiveWorkbook. Names.Add Name:="Amm_Edit", RefersToR1C1:="=Developments!R" & (Rynommer + 4) & "C" & Kolomnommer & ":R" & (Rynommer + 33) & "C" & Kolomnommer
ListBox0.ControlSource = "Amm_Edit"
The first line definetly works but the second gives me the error. Why is this?
I want the user to be able to edit some of the data I have in excel. It there some other way around this problem?
Is there a way to organize worksheet tabs in a tiered fashion? (i.e. making 2 or more rows of tabs at the bottom)
View 6 Replies View Relatedi currently have created a macro for copy a selection of cells from one work book to another. The file were i am copying the cells from is Track_&_Trend_00.xls. In this there is a password to open and modify. The sheet that the cells are being pasted to is TRACK & TREND.xls. There is no password on this. My macro runs fine only that midway through it asks for the password to open and the password to modify for the sheet Track_&_Trend_00.xls.
I wonder ifany one would know about entering input lines of code below to automatically put these passwords in and not having the two password dialog boxes pop up midway through running the macro.
Range("A8").Select
ChDir "T:Track & Trend"
Workbooks.Open Filename:= _
"T:Track & TrendTrack_&_Trend_00.xls"
ActiveWindow.SmallScroll Down:=-102
Range("A8:S115").Select
Selection.Copy
Windows("TRACK & TREND.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
Once I open a new workbook, how do I make it the active workbook to have a function make changes to it? Problem I am having is even after it is open, the function will only make changes to the workbook containing the Macro (Thisworkbook)....
View 9 Replies View RelatedI have set of data in multiple range ,need to fill the and replace the old values depends upon two column values (AH & AL)
IF Active Calls is "TATA" In AH:AH, and IF Action Onwer Col is "Blank",in AL:AL
Then Fill the Blank cells by Values "SVC" in the col Action Owner,Then Replace Old values by "Updates Awaited" in Status Col(AM:AM)
Find the attachment & basic code take this code for this task
[Code] ....
toggle-2.xlsb
I have the following code that Ger Plante very kindly helped me with which, depending on whether there is an 'x' by someone's name in a list, creates a new workbook, copies some information to it and saves it before moving on to the next 'x'. Loop Through Rows & Copy Each Row To New Workbook
For lLoop = 2 To 251 'first row of data to last row.
If ws1.Cells(lLoop, 4).Value = "x" Then '4 = Column D
ws1.Activate
ws1.Range("e" & lLoop & ":g" & lLoop).Copy
ws1.Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Rng1.Copy
Workbooks.Add
ActiveSheet.Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.Range("A1").Select
ActiveSheet.SaveAs varPath & "Student Data Files" & ActiveSheet.Range("B1") & ".xls"
ActiveWorkbook.Close
Else
End If
Next lLoop
how I can modify the code such that any cells in the range "b1:b504" in Sheet1 of the the new workbook can't be selected or edited without a password....I have tried unsuccessfully using Protect but am not sure how to get vba to set it to specific cells and determine exactly what is allowed in those cells.
I am putting together a corporate document which requires many different peoples access. It also requires these people to update/comment within specific columns to them.
I am really struggling with editing the columns to ensure only these specific people can access them.
Essentially I want to the document to be accessed by everyone, but column A can only be edited by Person A, column B can only be edited by Person B etc.
I've used 'Allow Users to Edit Ranges' and added a new range with a specified person - but this has not cured the problem, other people can still edit the column I'm trying to prevent them to.
Is it a case of just 'allowing' the specific person, or do I also need to 'deny' absolutely every other person to ensure that it works?
how do I restrict editing to certain columns/Rows and allow some to be edited by users?
View 2 Replies View RelatedIt's quite easy, I am trying to do a droplist (that's easy) but I want that drop list to take infos on 2 different columns and fill the 2 columns I want in the main sheet (see attachment)
On tab 'dealers' I have dealers name and account numbers. On tab 'Mileage' I have a cell call "NAME' and a cell call 'Accnt #/NB'. Here is what I want to do:
On sheet 'Mileage' I want to choose from a drop list a dealer name and I want Excel to populate the cell "Accnt#/NB" automatically by chosing the account number that is appropriate to the dealer choosen which is column 'B' in the dealers tab.
I have a spreadsheet with 10 columns which are for tags for products. Some columns are filled in for 10 products and some are not. For instance, product 1 has 7 tags while product 2 has only 2 tags and so on. Now what I am looking to do is combine all the tags for each product with a "," inbetween each one. The problem is if I just use a simple =A2&","&B2&","&C2 etc formula is that if there are only 2 tags then I get 8 ","s. I also tried it with an IF statement but by the time I was done nesting items I became cross eyed and said there has to be an easier way to do this.
I have create a spreadsheet with the data and the result that I typed into the "result" column.
I currently have the following macro - as you can see it is quite repetitive. I know it is possible to do this using a loop any ideas on how to do this would be appreciated.
Sub Clean()
Rows("4:4").Select
Selection.Delete Shift:=xlUp
Rows("5:5").Select
Selection.Delete Shift:=xlUp
Rows("6:6").Select
Selection.Delete Shift:=xlUp
Rows("7:7").Select
Selection.Delete Shift:=xlUp
I thought that this would work - but not so probably some basic syntax error:
Sub Runny()
For x = 4 To 17
Rows("x:x").Select
Selection.Delete Shift:=xlUp
Next x
End Sub
I'm trying to add a textbox at the current position (selected cell) with a set size, fill color, and border color. I found this: http://msdn.microsoft.com/en-us/libr...8(VS.80).aspx:
View 2 Replies View RelatedI got this code the other day
Private Sub CommandButton11_Click()
Sheets("Invoice Page").Select
Rm = 3
For c = 1 To 5
Rm = Application.WorksheetFunction.Max( Cells(1000, c).End(xlUp).Row, Rm)
Next c
Range(Cells(3, 1), Cells(Rm, 5)).Select
Selection.Copy
Sheets(" Records Page").Select
c = Cells(3, 256).End(xlToLeft).Column + 2
Cells(3, c).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
And instead of pasting the cells i want it to paste speical so it just pastes values rather than formulas.
I have the following code linked to a drop down list in my workbook.
[Code].....
It populates textboxes on the sheet with a lookup value result, the lookup value is taken from the dropdown box. The problem I have is any other excel workbooks that are open at the same time, the code is trying to run against those workbooks when I edit them, I can't seem to restrict the code to run only against the workbook in which the dropdown box which the code is linked to, exists in. I have tried moving the code to a module in the workbook but then the code does not seem to work as it does know which workbook the combo box and text boxes which should be filled with the lookuip result belong in
Link to workbook is [URL] .....
Code:
Sub Final()
Dim NameCell As Range
For Each NameCell In Sheets("! Names").Range("B1", Sheets("! Names").Cells(Rows.Count, "B").End(xlUp))
[Code]....
I want to pull data from ! Names. This code already Renames a new sheet from the ! Names sheet and also puts there name in cell C2 and phone number into cell C3 of the created sheet. I want to put more data from the ! Names sheet. How would i go about adjusting current code to do that?
What is the code to identify the current worksheet name? I want to store it in a variable so I can return to the worksheet later. I thought that the code was this:
myWorkSheetName = worksheet.name
:
:
Sheets(myWorkSheetName).Select
I receive a "Run-time error 424: Object required" message on the worksheet.name statement.
I know this is a simple question for all VBA droids...I guess the challenge is in how quickly I'll receive an answer. I hear the turn-around time on this board is pretty impressive. So here goes...
What is the VBA code for recording the current date without the date recalculating when the form is re-opened. I know the formula for automatic entry [=today()], but I need the date to stay the same. I can assign the date to any cell, so you may theorize any cell in the code. Remember...the trick is getting the date to stay the same once it's been automatically entered.
I need a macro that keeps only the top 25 rows in the current range and deletes the rest.
View 9 Replies View RelatedI want to schedule a print, for lets say every Sunday at midnight. I have managed to find some what of a solution by using the following vbscript and scheduling a print to default printer. Works perfectly if that is all I wanted.
Dim objExcel, objWorkbook, strFile
' Connect to Excel
Set objExcel = CreateObject("Excel.Application")
strFile = "C: est.xls"
' Open the Excel File for Printing
Set objWorkbook = objExcel.Workbooks.Open _
(strFile)
' Print to Default Printer
Set objSheet = objExcel. ActiveWorkbook.Worksheets(1)
objSheet.PrintOut
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
I do not want to print to an actual printer, therefore if I set my default printer as the document writer, the vbscript gets it as far as the "Save As" page. Is there a way I can add to this vbscript so it will save as well? Can I get it to save as "name"&[DATE], thereby having it save as a different name each time?
Can I change this vbscript to print to a named printer rather then a default printer? I am sure a PDF convertor may be of some assistance with this, but I do not have an PDF converter on my computer, and as this will be carried out on a computer that I will not have administration rights, I can not download one.
I want to compare performance data YoY, updated monthly on a YTD basis.
Sheet1: columns of data as follows
Jan11 Feb11 ... Dec11 Jan12 Feb12 ... Dec12
1000 200 ... 500 800 900 700
...
Sheet2: If we have data through July 2012, I want to show a sum of Jan-Jul11 against Jan-Jul12 on a separate sheet.
Cell1 (updated each month) = July
ColumnA: Jan-(cell1) 2011
ColumnB: Jan-(cell1) 2012
Is there a formula I can use in columns A & B that will reference cell1 and update using the data from sheet1 automatically when cell1 is changed each month?
I've been heavily updating my Budget file I've written in excel. The code I'm working with and having slight trouble with is as follows
Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas
Z100 =IF(TODAY()>DATE(2013,1,31),"January's Ending Balance is:",IF(TODAY()<=DATE(2013,1,31),"January's Current Balance is:"))
Basically, if the current date is after the last day of a given month (in this case January 31), then the cell should display "January's Ending Balance is:", if between January 1, (current year), and January 31, (current year), it should display "January's Current Balance is:". The code above does work great, but I need it to look at the current year according to the computer's date, and go by that, instead of having to change the code where it says 2013 to 2014 etc every year. This would be a hassle, as I have a tab coded for each month of the year. Id rather it be automated.
I want code to:
Open EstimatingSheet.xls
SaveAs current workbook as Range B13 &".xls"
Open EstimatingSheet.xls workbook from somewhere on C:
Then close the one with B13.xls
i have a sheet for monitoring sickness. i have used a macro before which automatically selects a range and emails it to recipients when i press a button. what i would like to do is have excel automatically choose the range for me based on today's day. in the attached example i would like column "A" copied along with the 5 columns before today's date, today's date column and also the 5 columns after today's date. paste the lot into an email and send. (i have highlghted the parts i would like copied and pasted into an email based on today's date (21st july).
View 4 Replies View RelatedIs it possible to display both the current code line number and the total line count of a macro while it's running?
I was thinking it would be handy for a progress indicator if the ProgressBar max value could be set to the LineCount total of the procedure and each line of code would increment the progressbar as the macro ran.
I have a spreadsheet that has a userform attached to it with text fields that enter additional data regarding each record. The userform takes these additional 5 fields and inserts them back into the spreadsheet, the final information to be exported back into Access. ( There are of course other ways to do this, but this is how my supervisor wants it done!)
My problem is that now that I can scroll through the spreadsheet without closing the userform, the userform doesnt show the current row's information as I scroll or change rows on the spreadsheet. It will only show the information that was in the row that it was on before i switched back to spreadsheet.
The code as it stands allows me to push a button and it immediately picks up on a meeting category and date of meeting, then sends emails to those that are supposed to attend based on a date that is within 7 days from the current date of pressing that button. HOWEVER; it seems to still send the email when the date has passed...is there a line I can put in this code to make it so that every date that has already passed will be discounted from future emails?
The code is as follows:
[Code].....