Copy and paste variables using a macro

**Nigel** · 06-10-2008, 11:03 AM

Hi ...I would like to use a macro to do the following...after selecting a cell eg C12 the macro would copy its contents and paste them into S12. However I would like it to be able to do this with any row I select and column "S" contains links to another worksheet, for example... C12 contains 123456, S12 contains =654321!C41. I need the numbers to be the same in both cells. So after the macro has run C12 would still be 123456 and S12 would now be =123456!C41. The numbers in the cells and the rows are variable (columns are always the same, "C" & "S"). I hope somebody can decipher what I am trying to do and point me in the right direction.

Many thanks

**royUK** · 06-10-2008, 11:33 AM

If you use the WorkSheet_Change event I think this will do what you want when you enter a numeric value in C

Please Login or Register  to view this content.

**Nigel** · 06-10-2008, 11:54 AM

Hi Roy...thanks for the reply, I am not in anyway an expert on VBA and any macros I have managed are always recorded so please excuse me if I have done this wrong. First of all I need to clarify that the number already exists in column C, I just need to copy it into Column S. When I read your reply I recorded a brief macro to give me the keyboard shortcut (thats the only way I know how to do it) and pasted your code into it. When I selected in this case C13 and activated the macro it came up with "invalid inside procedure". Can you let me know how to get round this?

Many thanks

**royUK** · 06-10-2008, 12:33 PM

The code i gave you works when you change a cell in Column C, it's triggered by that change.

If you want a macro to run from a button or something then the code needs changing, this is probably better

Please Login or Register  to view this content.

**Nigel** · 06-10-2008, 02:41 PM

Sorry Roy...still comes up with the "invalid inside procedure" and highlights the "option explicit" line of the code. I have copied it exactly as it appears in my workbook module in the hope that perhaps you can see what I have done wrong.

Thanks and regards

Please Login or Register  to view this content.

**royUK** · 06-10-2008, 02:51 PM

Nigel, please use Code tags.

Option Explicit should be at the top of your code module before any subs

**SOS** · 06-10-2008, 02:52 PM

Hi Nigel,

As far as I can see the code needs to be amended by taking out a couple of lines. You have TWO Sub lines - Sub Macro8() and Sub amendS().

The code is complaining beacuse Option Explicit is coming after a Sub line. The code should read

Please Login or Register  to view this content.

and as roy said place that code attached to button your sheet

**Nigel** · 06-10-2008, 03:14 PM

Many thanks for your interest Roy and Seamus....I have tried both variations of the code you have supplied and it just beeps at me when I press control and t. Just to clarify I am pasting in the code in a module exactly as it was written ...I then go to my worksheet highlight a cell ...eg C14... Currently C14 has 127343 showing and S14 has =O74024!C$41 ...I need the macro to change S14 to =127343!C$41 when I press control and t. If this can be done it will be a lifesaver as otherwise I will have to do it "manually" and there are over 300!

Many thanks and regards

**SOS** · 06-10-2008, 03:23 PM

Nigel,

Is the O74024 in cell S14 a sheet name?

If so then I think this variation of code might just work:

Please Login or Register  to view this content.

Let me know

**Nigel** · 06-10-2008, 03:31 PM

Yes it is....The number refers to a vehicle chassis number, of which there are 300 + different ones listed on a main "overview" sheet. Each vehicle then has its own individual sheet with more details of that particular car on it.

Thanks again for your interest and regards

**SOS** · 06-10-2008, 03:34 PM

Nigel,

have a look at the edited code

**Nigel** · 06-10-2008, 03:47 PM

Seamus....Its still just beeping when I press control + t. Its late here so perhaps it will become clear to me in the morning. I really appreciate your help and will give you feedback of how I get on tomorrow.

Thanks and regards

**SOS** · 06-10-2008, 03:48 PM

I'll check in again tomorrow as well - it's only 8.50pm here

**Nigel** · 06-11-2008, 11:08 AM

Hi Seamus

Well try as I might I couldnt get that to work. Should I be able to just copy and paste the "edited code" in its entirety onto my Macro8? Also Im not sure I understand the comments in red...I assume they are instructions for me to "get formula from S1" etc...as for the "change the tempval to whatever is after the !" ....that value will change depending on which row I select so Im not sure what you mean. Sadly my experience with VBA is very limited and even the simplest of instructions can tie me up in knots!

Thanks and regards

**SOS** · 06-11-2008, 11:56 AM

Hi Nigel,

Yes you should just be able to paste the code (as it stands currently) into a module in your workbook.

I'll try and explain the code

Sub amendS()

Please Login or Register  to view this content.

If the active cell is NOT in Column C then Exit the Sub

Please Login or Register  to view this content.

As long as there is something in the active cell set Temp Val to be whatever the formula 16 cells to the right of the active cell (ie Col S)

Please Login or Register  to view this content.

Change TempVal to the last 4 characters of that formula (ie !A41 or whatever)

Please Login or Register  to view this content.

Change TempVal to "=" PLUS Active cell's value PLUS TempVal (ie 123456!A41)

Please Login or Register  to view this content.

Place the new TempVal into the cell 16 cells to the right of the active cell

End If
End Sub

This now means that if Cell S1 contains "987654!A41" and you put "123456" into Cell C1 and then make sure you reselect C1 then press alt+t (or whatever combination you have chosen), it should make Cell S1 contain "123456!A41"

If you want to you could post a cutdown version of your workbook and I could have a look at it (minus any confidential info of course)

Sorry - just realised that TempVal was being reset to only catch the last 3 chars of the formula in S1 but it should have been 4 characters.

**Nigel** · 06-11-2008, 12:25 PM

Hi Seamus

Thanks for the explanation of your code, it was just what I needed and have learnt a lot from it. I shall give it a go and if I still manage to mess it up I will post a section of my workbook for you to look/laugh at!

Thanks for taking so much time to help.

Regards

**SOS** · 06-11-2008, 12:40 PM

Nigel,

OK - I'd be interested to have a look anyway.

**Nigel** · 06-11-2008, 01:48 PM

Hi Seamus

When the macro is run it comes up with "Compile error" "expected end sub". So I have tried to upload a very cut down version of my workbook for you to look at. Hope it works.

Many thanks and regards

**royUK** · 06-11-2008, 05:15 PM

You have an unfinished macro

Please Login or Register  to view this content.

**SOS** · 06-11-2008, 05:33 PM

Nigel,

roy is right in that you have some extra lines in one of the modules. I followed his suggestion and deleted the offending lines and then noticed that the macro re-evaluates TempVal by taking the last 4 characters of the formula (which I had thought was what was needed as you said the formula ended eg !D41. But in fact the formula ends !$D$41 which is 6 characters.)

I amended that macro and it now appears to work (but you have to be sure to be IN the cell as opposed to entering something into it and then pressing Enter which will make the activecell one cell down (or across)

Attached is the updated project file

**Nigel** · 06-11-2008, 11:13 PM

Hi Seamus/Roy....What can I say? The help you have given me is really appreciated not just because of this issue but the explanations etc will help me tremendously with any future mods I need to do to the Workbook.

Many thanks for your time, effort and patience.

Nigel

Copy and paste variables using a macro

LinkBack

Thread Tools

Rate This Thread

Display

Copy and paste variables using a macro

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions