click on cell A4 -right click - insert cell- shift to right, so figure 2 moves to C4 pushing the yellow cell to D4 right?
what am trying to do is to make column c to be frozen or locked or whatever you want to call it, so if I insert a cell in A4 shifting to the right, the value in B4 jumps to D4 without shifting the yellow cell
I have a need to insert formulas into a column using vba. I need to skip a row, when inserting the formulas, so a simple drag using vba would not work without some modification.
Here is the formula, that I need to insert... into column "BJ"
Example Cell BI11...=SUM(BH11-BH10)
Cell BI13...=SUM(BH13-BH12), the next cell needing a formula would be ..
Cell BI15...
The formula would be inserted, based on the last row used in column "C".
freezing columns and rows it does not seem possible to freeze MULTIPLE rows AND MULTIPLE columns simultaneously in the same spreadsheet.
In my spreadsheet I would like to freeze columns A, B, C as well as rows 1 - 8.
I've been using this code to make menus for all my Excel sheets since NateO made it back in 2003.
- The workbook has 370 sheets, 365 of them are one day per month with the tab named ddmmmyy.
- The Main Menu in this file puts the months Jan, Feb, Mar, etc in cells A3:L3.
- In the single cells under each month it put the month day tab name (ddmmmyy) and hyperlinks it to the sheet tab.
I would like to do two new things.
1. Put the name of the month in everyother cell in row 2, so Jan would be in A2, Feb in C2 and Mar in E2, etc.
2. Put the day of the week for each date in the cell to the right of the date. So the column heading would be Jan in A2, 01Jan08 in A3 and Tue in B3.
I thought I could make another macro and just insert the rows after the Main Menu was built and then VBA copy/paste TEXT(cell,"ddd") down the ranges, but thought there might be a better idea out there...
Sub Workbook_TOC()
Dim ws As Worksheet
Dim wsNw As Worksheet
Dim z As Integer
resolving a "not responding" problem that occurs when I try to enter data into a cell in one of two columns in my spreadsheet. These columns don't seem to be linked and I can't see a way of getting at where the problem might be coming from.
Is there a tried and tested method for trouble shooting something like this?
I'm using Excel 2003 and was wondering if there was a way I could Lock/Freeze the contents of a column. For Example:
Cell A1 has the formula =B1+C1+E1 - figure shown is £405
Cell A2 has the formula =C2+D2+E2 - figure shown is £650
Is there anyway I can freeze column A so when I delete column B,C,D,E ect the correct figure will still show?
Please find attached a daily copy of a spreadsheet that is used to monitor train running times.
The columns on the left - "Serv No, Serv Start Date,Train No:, Scheduled Arrival, Scheduled Depart" are provided to us automatically and the underlying cell formatting/formula cannot be changed. This information is cut directly from a daily report sent to us the previous day. The cell formatting for the start date is custom - dd-mm-yyyy hh:mm.
The sheet is usually locked out with user access only to the left hand side to cut and paste train times in and the input columns on the right hand side. User has no requirement to adjust any details in the left had column apart from to add additional trains at the bottom is need be. I have left the loaded sheet blank and complete with all underlying cell formatting, conditional formatting and formulas intact for you to play with.
Both sheets essentially do the same thing so whatever formula changes are made to the empty sheet can be adapted for the loaded sheet.
For simplicity, I will focus on the empty sheet. What I am mucking around with is having the user input the train arrival time in the "Actual Arrival" column - (column F)
What I am looking at then making occur is the spreadsheet automatically filling the adjacent columns with the appropriate text and calculations.
To do this excel needs to compare column the number (cell input is text formatted) as a number with the arrival time in column G. It then needs to calculate if the train is Early (16 minutes or more before scheduled arrival), on time (+/- 15 minutes either side of scheduled arrival) or late (16 minutes or more after scheduled). It then needs to automatically place the letter "E", "O" or "L" in column K and the time differential in column L.
My problem is two fold.
Firstly, I cannot seem to make the spreadsheet automatically enter a letter into column K depending on the above conditions.
Secondly, I have had limited success in having the spreadsheet compare column J with column F and working out the differential. However, this only works if the time remains linear. IE only if the train runs on time or late. It gives an incorrect time differential if the train is early. I will attach this spreadsheet tomorrow as an add on to this post.
DAILY TRAIN RUNNING.xlsx‎
I have a spreadsheet with data in 4 different sections with a summary section at the top. Data titles are in column A with the data to the right, arranged by month.
I have created macros to sort the data in each section. Occasionally I need to add a new row to each section for a new data set. When I do this, it screws up my sorting macros for the sections below. It will sort the same rows that it sorted before, not taking into account that the data is now one row lower.
I wanted to know:
Q1. How can we insert new rows in a protected sheet?
For example: In the attached sample protected excel sheet "insert rows.xlsx" , if for Employee ID and Name fields, the user has more no. of records than provided, how can he insert new rows considering the fact that Company ID and Name field are locked.
Or any way to address this issue via some other method like adding a scrollbar,etc.
I am working on a project of 4000+ entries. We will be identifying the number of seasons in a year. We would like to add a row in another worksheet for each of the seasons identified.
So say we had data in row 1 of worksheet 1. That row stated there were 3 seasons. Is there a way to automatically insert 3 rows into worksheet 2?
I would like to prevent the user from inserting rows. I know that ater versions of Excel have built in "Protection" features which would easily accomplish this. Unfortunately, our company uses Excel 2000.
I have keyed in a constant value in cell B100 ("bottom" of the practical range). I have tried ...
Private Sub Worksheet _Change (ByVal Target As Excel.Range)
MsgBox "Range" & Target.Address & "Was Changed."
If Range ("B100").Value = "" Then
Application.Undo
MsgBox ("Do Not Insert or Delete Rows")
EndIf
End Sub.
The problem with the above is: The "Change" macro does not seem to detect the insertion of rows. It does, however, detect the deletion of rows.
Is it possible to make it so that a user cannot delete or add sheets to a work book, except through buttons assigned to macros? I've tried simply locking them, but these sheets need to be accessed to input and manipulate data. I have the macros necessary to delete/add sheets, but right now they are redundant since the user can simply do it manually.
View 3 Replies View RelatedI need to insert an arrow in a sheet, in a location and a direction which can be controlled by me (I don't mind hard coding the location and direction), based upon a cell value being positive, negitive or 0. I realise that it will require some code but can't find any infomation on doing anything like this through google or in the various VBA texts I own.
View 2 Replies View RelatedI have a command button that runs a macro to insert two new rows between row 15 and 16 no real problem (see code & sheet below), but I would like the Total Hours and Total Cost rows to include the newly added rows i.e.
Rows 16 & 17
Cell B18 now contains =B2+B4+B6+B8+B10+B12+B14+B16
and
Cell B19 now contains =B3+B5+B7+B9+B11+B13+B15+B17
If another two rows are added then Total Hours and Total Cost Cells 20 & 21 will now be
Cell B20 now contains =B2+B4+B6+B8+B10+B12+B14+B16+B18
and
Cell B21 now contains =B3+B5+B7+B9+B11+B13+B15+B17+B19
Command button Code to insert row
Code:
Private Sub CommandButton1_Click()
Const fWhat As String = "EXTERNAL"
Dim sR As Range, fR As Range, fAdr As String, nRw As Long
Set sR = Range("A1:C187")
[Code] ..........
Worksheet Below
1
Hours
170
Cost
387.99
[Code] ...........
I'd like to insert a picture background for multiple s/ sheets I've developed, however, even though the images I'm using are only 30kb's in size, it bumps my s/sheet file size from 900kb's to over 4mb's ! Is there a way I can stop this from happening, or, any way to restrict the background image to only a certain part of the worksheet as opposed to the entire thing?
View 3 Replies View RelatedI have a large spreadsheet converted from pdf whose data still appears in A4 reading format.
I need to move part columns of data from 6 columns to form 1 large column in column A.
For example, move range B8 to B76 beneath range A8 to A76 and range C8 to C76 beneath that etc, page by page working through all 270 pages !
Also need to delete unnecessary 'page headers' throughout as in rows 2-6
When a user clicks a cell in Column A, VBA will enter the date in the cell (in the form MMDDYY) and will position the insertion pointer at the end of the date. The user can then enter a four digit number following the MMDDYY. In this worksheet, Column A holds a transaction number, which is always in the form MMDDYYXXXX. I am trying to save the user the trouble of entering the MMDDYY, since it will always be today's date.
View 3 Replies View RelatedI have a Workbook with about 20 Worksheets, for each sheet I have a Selection Change Event that simply performs the Following:
MainSheet1. Range(“Z1”) = Now
1 Cell on every Sheet then is pointed to this cell via =Main!Z1. Problem is that when this is preformed Each Row from the Other In-Active Sheets and sometimes a near-by Button will Appear on my Active Sheet. I cannot select the parts of the sheets that appear and when I scroll down till they are out of view and then scroll back up they are gone until I select another cell and it happens again. I have tried in the Z1 cell to just put =NOW() and Make Calculations Manual and then with the selection change to call MainSheet1.Calculate or Workbook.Calculate but this has the very same effect and the other sheets bleed onto or ghost onto my Active Sheet. So far the only way I’ve been able to get around this is:
If Application. ScreenUpdating Then
Application.ScreenUpdating = False
MainSheet1.Range(“Z1”) = Now
Application.ScreenUpdating = True
Else
MainSheet1.Range(“Z1”) = Now
End If...............
I need to insert text at the curson position in a text box on a VBA form. I know how to make the button not take the focus; I just need to know how to pass the cursor a timestamp.
View 7 Replies View Relatedhow do you freeze the top row (header row) so that it stays at the top of the worksheet as you scroll down?
View 3 Replies View RelatedIf I had =A1 in B1, could I "freeze" B1 such that its value updates ONLY upon workbook open?
View 10 Replies View RelatedI am looking to freeze row 18 in an excel sheet, however in doing so this really limits the amount of rows below row 18 that can be viewed and scrolled through.
I would like to know if there is any way that you could freeze row 18 and when you scroll down on the worksheet, you can scroll down past rows 1-17 and when row 18 reaches the top row it would lock there? And you would then be able to continue scrolling down the worksheet beyond row 18..
I am having problems with the VBA Editor freezing at the most inconvenient of times when writing code and then clicking to the Sheet I'm working on, it just freezes up the whole of Excel and it has to be shut down and opened up again.
It seems like a memory problem at first, but there is sufficient memory
( 768Mgs, OS is XP Pro, Excel 2000, 9.02).
The work is not lost becuase I'm able to Save even at PC Re-boot, fortunately.
I've tested on 2 different PC's and the same happens. The only way to avoid the freeze it seems, is before I switch to view a worksheet, is to Save then click off VBA Editor and re-open.
When doing so, another symptom is it seems a huge amount of memory is freed up, ( pardon the terminology)
There is also a message that pops up when re-opening that same workbook that there are Formulas Linked to another workbook, but unable to find any after tediously searching every worksheet by using "Find, Look in Formula" there is nothing found, yet.
This freezing may happen twice a minute whilst pointing the mouse cursor from any VBA Edit window or worksheet and it's getting to a point of repetitiously having to Save, click off VBA Editor, and Click back on again each time I need to look at a respective Worksheet.
I would like to have the column titles stay so I have frozen row 1. But I would also like to freeze row 200 so I can keep up with the running totals at the bottom of the form as I fill in information. Is it possible to freeze a pain at the top and the bottom?
View 4 Replies View RelatedI need is for a block of cells (A1:I4) to be visible at all times. I've frozen the sheet so that the top four rows are always visible, but unfortunately the sheet is quite large, horizontally-speaking, so that when users scroll horizontally, important stuff scrolls off the screen.
View 9 Replies View RelatedI have a table that is frozen in b5 meaning that the first column ("A") and rows 1-4 are frozen. The table is large with ever expanding rows. What I want to do is, a good few rows down (say row 300), freeze the row at the bottom of my view and use that row as a mean average row. Therefore allowing me to enter much more data above, but constantly be able to see the averages of the data I am adding.
View 3 Replies View RelatedHow can I lock the contents of an array so that they won't disappear when using auto-filter. "Freeze Panel" doesn't seem to do the job properly.
View 9 Replies View RelatedCan we freeze particular cell and sheet means no editing is possible for those particular cell or sheet.
View 1 Replies View RelatedI am using Row 3 as a my template to insert a row for every new line of data i enter. I have a macro that copies the format and formula on this template row to insert at the end every time. The problem is on ROW 3 Cell F i am using a Today() function and everytime i insert a row the date is populated. But the next day the date automatically update.
In my search on this forum, i found this macro to supposedly lock in the dates, but i doesn't work.
VBA:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Range("F2:F10000"), Target) Is Nothing Then
storage = Target.Value
Target.Value = Format(storage, "dd/mm/yy")
End If
Application.EnableEvents = True
End Sub
How can I freeze a sheet's tab location so it doesn't roll off of the screen? I have a workbook with 30 worksheets all hyperlinked off of the 1st sheet which is the INDEX sheet. The INDEX sheet only exists to make it easier for users to go directly to their desired sheet without tabbing horizontally through many sheet names. However when users use the bottom left arrow buttons to tab through the worksheets, the leftmost INDEX worksheet tab scrolls off of their screen. I realize that they can right click on the left/right tabbing arrows to see a list of all worksheet names, but it would be nice if the INDEX worksheet tab never rolled off of the horizontal tab scroll bar.
View 2 Replies View Related