Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy cells and paste to an address given in another cell (VBA Newbie)
Can anyone help with this problem. I am trying write a macro to copy range of cells on one worksheet, then goto an address which i specified in a cell. (This address is dynamic and changes according t the date) The data can then be copied to the new location. I a attaching a bitmap image of my example. Here are my required actions: Go to cell B27-B33 on worksheet "Entry Sheet" Copy cells B27-B33 Go To the address in cell B20 (in this case Data!J29) Paste the copied cell Thanks in anticipation Ro +------------------------------------------------------------------- |Filename: DataCopyExample.doc |Download: http://www.excelforum.com/attachment.php?postid=3928 +------------------------------------------------------------------- -- WightRo ----------------------------------------------------------------------- WightRob's Profile: http://www.excelforum.com/member.php...fo&userid=1379 View this thread: http://www.excelforum.com/showthread.php?threadid=47801 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy cells and paste to an address given in another cell (VBANewbie)
Maybe like this:
Sub CopyIt() Dim CTo As Range Dim CRng As String CRng = Sheets("Entry Sheet").Range("B20").Value Set CTo = Sheets(Left(CRng, InStr(1, CRng, "!") - 1)). _ Range(Right(CRng, Len(CRng) - InStr(1, CRng, "!"))) Sheets("Entry Sheet").Range("B27:B33").Copy CTo End Sub Hope this helps Rowan WightRob wrote: Can anyone help with this problem. I am trying write a macro to copy a range of cells on one worksheet, then goto an address which is specified in a cell. (This address is dynamic and changes according to the date) The data can then be copied to the new location. I am attaching a bitmap image of my example. Here are my required actions: Go to cell B27-B33 on worksheet "Entry Sheet" Copy cells B27-B33 Go To the address in cell B20 (in this case Data!J29) Paste the copied cell Thanks in anticipation Rob +-------------------------------------------------------------------+ |Filename: DataCopyExample.doc | |Download: http://www.excelforum.com/attachment.php?postid=3928 | +-------------------------------------------------------------------+ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy cells and paste to an address given in another cell (VBA Newbie)
Thanks Rowan, That did the trick. Rob : -- WightRo ----------------------------------------------------------------------- WightRob's Profile: http://www.excelforum.com/member.php...fo&userid=1379 View this thread: http://www.excelforum.com/showthread.php?threadid=47801 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy cells and paste to an address given in another cell (VBA Newbie)
As I said in my last post, the problem was solved. Unfortunately that was only with my simplfied example. In my actual workbook, the values in the range B27:B33 are fed in from a calculator sheet. Rowan's code is copying the actual cell data, which is now setting up a link to the calculator sheet. What I need is a "Paste Special" = Value, rather that a simple "Paste", is this possible? I am new to VBA coding, so all help is appreciated. Rob -- WightRob ------------------------------------------------------------------------ WightRob's Profile: http://www.excelforum.com/member.php...o&userid=13799 View this thread: http://www.excelforum.com/showthread...hreadid=478016 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy cells and paste to an address given in another cell (VBANewbie)
Hi Rob
Try: Sub CopyIt() Dim CTo As Range Dim CRng As String CRng = Sheets("Entry Sheet").Range("B20").Value Set CTo = Sheets(Left(CRng, InStr(1, CRng, "!") - 1)). _ Range(Right(CRng, Len(CRng) - InStr(1, CRng, "!"))) Sheets("Entry Sheet").Range("B27:B33").Copy CTo.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False End Sub Regards Rowan WightRob wrote: As I said in my last post, the problem was solved. Unfortunately that was only with my simplfied example. In my actual workbook, the values in the range B27:B33 are fed in from a calculator sheet. Rowan's code is copying the actual cell data, which is now setting up a link to the calculator sheet. What I need is a "Paste Special" = Value, rather that a simple "Paste", is this possible? I am new to VBA coding, so all help is appreciated. Rob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy/Paste Formula-dropping 1st cell address | Excel Discussion (Misc queries) | |||
Need code to copy and paste based on cell address. | Excel Discussion (Misc queries) | |||
Copy/Paste Hyperlink Address | Excel Discussion (Misc queries) | |||
How can I copy/paste a hyperlink address without the link? | Excel Discussion (Misc queries) | |||
Copy & Paste Hyperlink address into cell | Excel Programming |