Free Microsoft Excel 2013 Quick Reference

VBA to Prevent Copying one Cell to another /w Data Validation

Hello,

I am trying to lock down a column of cells that have data validation to an extent. I want them to be allowed to select from the drop down list, but I do not want them to copy from another cell with the same data validation and paste to another. The problem is that it is a dynamic list data validation by using this as the data validation...

=IF(A58="M",ManagerError,IF(A58="A",AssociateError))

So if they had selected M in A58, they could still copy from say.... A57, where they had selected A in A57 and paste to A58 with an item that is not in the drop down list since it was M not A. These are dynamic lists. For example Associate Error =OFFSET(Lists!$V$15,0,0,COUNTA(Lists!$V$15:$V$30),1).

I had checked info from http://www.j-walk.com/ss/excel/tips/tip98.htm however, this only prevents pasting non data validation over a cell with validation. It does not work copying from a cell with data validation to another cell with validation also.

Any idea's on how to handle this? We all know, if someone can break it, they will And they have lol which is why I am forced to do this.


I would like to copy one cell formula to another but i would like the constant to remain intact. When i pull it over to another cell there is an incremental effect.

from:
=SUM(LEN(B9:B300)-LEN(SUBSTITUTE(B9:B300,B4&","&D8,"")))/(LEN(B4&","&D8))

to
=SUM(LEN(B9:B300)-LEN(SUBSTITUTE(B9:B300,B5&","&D8,"")))/(LEN(B5&","&D8))

But i want it to be either

=SUM(LEN(B9:B300)-LEN(SUBSTITUTE(B9:B300,B4&","&D8,"")))/(LEN(B4&","&D8))

or better still simple increment just value
=SUM(LEN(B9:B300)-LEN(SUBSTITUTE(B9:B300,B4&","&D9,"")))/(LEN(B4&","&D9))

thanks

how to vlookup of one cell by starting 2 digits of number in another cell?

I am new to using macros, and having trouble with the copy function. I need
to copy one cell's contents to a range of cells above it to cell location A1.
The range is determined by how many cells contain data in column B. I use
end-down to determine the number of cells in column B, and this can vary each
time I run the report. How can I move to left one cell to column A when I
find the bottom of column B, enter a value, and then copy this value up to
cell A1?

Any help is greatly appreciated. Thanks.

Apologies in advance, probably a very simple problem, but can't work it out.

I have one cell with a data validation list of three text values. I want a calculation made upon the result of this cell made and displayed in a second cell.

For example, the three values are "1 Month", "3 Months" and "12 Months" and would like if 1 Month was chosen to display in cell 2 "1200" , if 3 Months was choosen "2000" and if 12 Months "10000"

I've tried putting formulas - but the IF statement only gives me True and False statements (i think). I'm guessing I might need to use VBA but am very lost.

Can you help me please ?

David

Hi!

When I paste copied data from a PDF file into excel it all goes into the same cell.

How do I either:

A) When first pasting get it to go into one cell for each data item

or

B) Once data is all in one cell separate it out.

For example: I recently copied this: 97 117 18 20 74 82 18 24 450

And it all went into one cell. I want a cell with 97 in it, a cell with 117 in it, a cell with 18 in it etc....

Please help and save my Friday afternoon!

I'm trying to create a comment that relates to more than one cell. (One
comment has two different arrows pointing to two different cells.) Does
anyone know if this is possible?

I have a range of cells that uses an in cell drop down data validation
list for data entry. i would like to move the cell selection to a
different part of the wks after a user changes one of these cells. I
tried the worksheet change event - but it doesn't seem to monitor
changes made by a drop down data validation list.

Anybody have any ideas?

Richard

--
rgarber50
------------------------------------------------------------------------
rgarber50's Profile: http://www.excelforum.com/member.php...o&userid=11350
View this thread: http://www.excelforum.com/showthread...hreadid=391680

I have a workbook that I've just added data validation to one cell. FWIW,
the data validation is based on a named range. Here's the (perceived)
problem. When I've set up data validation in the past, the selection is the
same width as the cell. In this case, the list starts almost a full cell
width to the left. What am I missing?

Thanks,

Barb Reinhardt

Is it possible to get Sheet Names List in a Data Validation List in a particular cell

Dear Forum,

I know by using the formulas we can get the Sheet Names, however I want to get the List of Sheet Names in a particular cell D1 directly when the Sheet is Activated..

I got this code while googling

http://www.ozgrid.com/forum/showthre...t=61138&page=1

Sub SheetNames() 
    Columns(1).Insert 
    For i = 1 To Sheets.Count 
        Cells(i, 1) = Sheets(i).Name 
    Next i 
End Sub
However, this code inserts a Column before the First column when I call this Function wfor the Worksheet_Activate Event..

I want to have the same list appear as a Data Validation List in the cell D1 directly so I dont reserve the space for storing the Sheet Names anywhere..

Warm Regards
e4excel

I have a range of cells that uses an in cell drop down data validation list for data entry. i would like to move the cell selection to a different part of the wks after a user changes one of these cells. I tried the worksheet change event - but it doesn't seem to monitor changes made by a drop down data validation list.

Anybody have any ideas?

Richard

Hi,

I'm decently competent at VBA but no real experience with userforms. I have a simple table (sample attached):

Column A- Product Number
Column B- $ amount for each product

What I would like to do is move money between the products using a userform, ie subtract a dollar amount from a cell's current value and add that amount to another cell's current value. The userform would have a blank where you type the amount you want to move, as well as a dropdown to select which product to take away from and another dropdown for which product to add the dollar amount to.

The value of the userform would not be the value of any destination cell, instead it would be the amount to subtract from one cell and add to another. Hope tha is clear.

thanks!

Onetimesten

I have a template workbook from which I copy a worksheet into a new workbook then change some of the values in the new worksheet. The template worksheet has many locked cells and is protected such that the user cannot select them, so my VBA code unprotects the new worksheet specifying the password, makes the relevant changes then protects the worksheet once more, again specifying the password.

If I look at the worksheet whilst the code is running, after the protect statement, all is as expected - I can't select the locked cells but I can select the unlocked ones and enter data into them. However once the code has closed & saved the new workbook, it's possible to select the locked cells although not enter any data into them. Any ideas what's wrong ?

This is the relevant code:


	VB:
	
ActiveSheet.Protect Password:="xxxxxx" 
ActiveSheet.EnableSelection = xlUnlockedCells 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello i want to copy the following function via vba to a number of cells

=countif(i10:ah10,"does not comply")

i basically want row to do a a comparison from i10 to ah10, row 11 to do a comparison from i11 to ah11 and so on.

i tried this as a first step
[vbcode]
Workbooks(WorkBookFileName).Worksheets(TabID).Cells(m, fc + 8 + 1).formula = COUNTIF(I97:AH97,"does not comply")
[/vbcode]

but i got the error

"compile error: expected: list seperator or)

and i tried this one

[vbcode]
Workbooks(WorkBookFileName).Worksheets(TabID).Cells(m, fc + 8 + 1).text = "=COUNTIF(I97:AH97,"does not comply")"
[/vbcode]

and got the error compile error: expected : end of statement

i read some place to ensure that the reference is copied, one can use the following
[vbcode]
wks.Cells(i + 13, 7).FormulaR1C1 = wks.Cells(i, 3).FormulaR1C1
[/vbcode]
but cant get through the first step, where i actually use vba to make the first entry

I have three sheets "Budget", "Cklog" and "Disbursements".

Budget contains several line items annualized in one column and /26 (pay
periods) in another.
________B________________C_______________D________ _____
18.| CATEGORY ANNUALIZED PER PAY PERIOD
19.| Food $4500.00 $173.07

"Cklog" has a column (G8) labled "Net Income". Net is calculated for each
pay check

_____E________________F_______________G___________ __
DATE GROSS NET

8.| 17FEB06 $191.05 $167.52
9.| 03MAR06

"Disbursements" has a column "J" labeled FOOD which contains =BUDGET!$D$19
copied to all cells (26) in column "J". This causes $173.07 to appear in all
cells in column "J". Column "J" is totaled at the bottom causing a
annualized total instead of an accumulated to total.

How do i keep $173.07 form appearing in "J" until a new entry appears in
Cklog[s NET Column

Well, Duke, I tend to use Excel as a 'pipe' in which to transfer/convey the
data from one source to another OR to provide some calculations on various
spreadsheets that start out as an idea and end up being an application of
vast significance (at least to some people!!)
Thanks for your input. I'm already part of several newsgroups/communities
so it's hard to 'add' time into an already crowded schedule. I do, however,
peruse the various Excel, Word, Outlook, Exchange, Windows XP, Windows 2003,
SQL, communities from time to time.
Tom

"Duke Carey" wrote:

> Blush!!
>
> Well, Tom, for one thing, it's not vast, at least not compared to the MVPs &
> near-MVPs who post here and who frequently and gently correct my public
> errors. For another thing, there's a staggering amount of knowledge, tips, &
> tricks available here in these newsgroups, free but for the cost of the time
> it takes to scroll through some messages each day.
>
>
> "Tom" wrote:
>
> > Thanks, Duke!
> > I never would have figured this one out!
> > Where do you get your vast knowledge of little tricks in Excel?
> > Tom
> >
> > "Duke Carey" wrote:
> >
> > > Assuming that column F has now blanks in it:
> > >
> > > Copy cell G2
> > > Select cell F2
> > > Press the End key followed by the down arrow
> > > Move one cell to the right
> > > Hold down the Shift key and press the End key followed by the up arrow
> > > Paste
> > >
> > >
> > > "Tom" wrote:
> > >
> > > > This seemingly very simply question has me baffled. I have a column of data
> > > > that I need to perform an operation, something similar to '=f2-36161'.
> > > > Column F has the data in numeric format. I add a column, say Column G. Then
> > > > I put into G2 the '=f2-36161' and then convert this new number to a date
> > > > formatted 'mm/dd/ccyy'. I'd like to replicate (copy/paste) this G2
> > > > calculation and operation down through the entire Column G. [Column G is
> > > > about 15000 rows deep.]
> > > > Is there an easy way to do the copy/paste or replicate without copying G2
> > > > and then copy/paste MANUALLY down the column until I reach the end of the
> > > > data?
> > > > Seems like a simply question but I can't find the 'key'.
> > > > Thanks,
> > > > Tom

I am tryiing to copy one cell to multiple worksheets without having to do
each one separately. Is there a way to do this?

So, I'm trying to track Wins, Losses, and Draws.

Is there a function or macro that can make one cell from one sheet affect another cell in another sheet?

Example: If one cell is RED, it will add +1 to another cell in another sheet. So 2 red cells = 2, 3 red cells = 3, etc.

If color cannot affect another cell,

If one cell says "W," it will add +1 to another cell in another sheet. So W's(different cells) = 2, 3 L's = 3, 3 Draws = 3

Any chance? Thanks!

Hi,

I have not been able to find this or to get this to work on my own, so if someone can please answer this, it would be appreciated!

I have a workbook that on sheet one, I want whatever data gets put into B2 to be put into B2 on sheet 2. I have not been able to find info just for one cell, always a row or column. I want this to be a macro/VB in the background.

I also will need whatever data is entered into worksheet one, cell B26, put copied into worksheet two, cell B6.

Thank you very much in advanced!

Brian

I have three sheets "Budget", "Cklog" and "Disbursements".

Budget contains several line items annualized in one column and /26 (pay
periods) in another.
________B________________C_______________D_____________
18.| CATEGORY ANNUALIZED PER PAY PERIOD
19.| Food $4500.00 $173.07

"Cklog" has a column (G8) labled "Net Income". Net is calculated for each
pay check

_____E________________F_______________G_____________
DATE GROSS NET

8.| 17FEB06 $191.05 $167.52
9.| 03MAR06

"Disbursements" has a column "J" labeled FOOD which contains =BUDGET!$D$19
copied to all cells (26) in column "J". This causes $173.07 to appear in all
cells in column "J". Column "J" is totaled at the bottom causing a
annualized total instead of an accumulated to total.

How do i keep $173.07 form appearing in "J" until a new entry appears in
Cklog[s NET Column

I am struggling to build a simple copy statement that uses multiple variables. I could copy in my current code but it would only highlight the struggle.

A single worksheet holds all the values I need to move to various worksheets in the destination workbook. The destination workbook is MASTER.XLS and is already open. The source workbook has various names.

I have the macro walking through each value in column E of the source worksheet. When a match occurs, the corresponding cell in column F has the destination worksheet name, the corresponding cell in column G has the destination cell address and column H has the destination value (string value).

I have dim statements for SheetName, CellAddr and CellVal ; all set for String. I have been playing with "Offset" as well as "Select"ing through the worksheet hierarchy to drive to the desired destination cell. All seem to be more work than necessary and none work properly.

What I am looking for is a set of macro statements that I can use as a "template" within the balance of the macro I have written. I would also happily accept recommendations about books that provide a step-by-step approach to learning the capabilities of Excel's VBA functions. I know from my limited programming background that there are many ways to do the same thing. I'd rather start with the most efficient rather than burn lots of hours experimenting.

Thanks for any and all contributions.

Hi

I'm Trying to copy one cell into many Cells on a different Sheet.
But i'm Having troubles using the Range (Cells Command.....

This Works:
While Shanding in Sheet 1 it Copys
Cell B2 into Cells C4 to Cell C9 on sheet Xref


	VB:
	
ActiveSheet.Cells(2, 2).Copy ActiveWorkbook.Sheets("Xref").Range("C4:C9") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This Should do the same but nothings happening not even an error message


	VB:
	
ActiveSheet.Cells(2, 2).Copy ActiveWorkbook.Sheets("Xref").Range(Cells(4, 3), Cells(9, 3)) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Need The Cells Command Because it much easier to use in the reast of my script....

Is There a diffrent way of doing this without having to jump to the xref sheet

Sorry for my bad english


I want to jump from one cell position (on picture, marked with numbers from 1 - 6)
But i want to jump to them respectly(first to 2, then on 3....)
I have used these numbers just to describe space between cells(in rows and columns),
and actually those are blank cells!
Starting blank cell can be everywhere(e.g. A2), it doesnt matter, it is only important how to jump from one cell to another in mentioned manner!!!!!!!

Thanks in advance!!!!

Hi all, here is my problem as best I can describe.

I have two sheets outling menu trees however one sheet was created long ago and there have since been additions to the trees.

My problem is that something that only had one choice originally now has three and I want to be able to copy that from the new to the old, however obviously with a ctrl +c ctrl+v the data populates three fields overwriting values underneath.

Can I copy three cells worth of data and have excel realize its new data and add rows for me automatically. Inserting 10 rows so it will all fit manually before each copy paste is getting tedious...

Thanks,
Chris

I am looking to copy one cell all the way down the page and can do it manually but I do not know how to do it with just click of the button. can anyone help??

Thanks