Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pasting to an address specified in a cell
I am trying to sort out a macro that will copy a range of cells, go t an address that is specified in another cell and paste the data. Th address changes relative to the current date. I have been give a solution that worked with my simplified example a shown in the attached Word file. The problem is that the data in cell B27:B33 on the worksheet "Entry Sheet" in my actual working workbook ar the result of calculations from another sheet and not simply numerica data. The solution I have been given is pasting the cells data, i.e the formulae, rather than the values. Does anyone have any suggestion how this macro may be changed to work as a "Paste Special + Value rather than just "Paste"? The original solution ftrom Rowan Drummond: 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 Su +------------------------------------------------------------------- |Filename: DataCopyExample.doc |Download: http://www.excelforum.com/attachment.php?postid=3940 +------------------------------------------------------------------- -- WightRo ----------------------------------------------------------------------- WightRob's Profile: http://www.excelforum.com/member.php...fo&userid=1379 View this thread: http://www.excelforum.com/showthread.php?threadid=47862 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pasting to an address specified in a cell
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 PS Replied in original thread about half an hour ago. WightRob wrote: I am trying to sort out a macro that will copy a range of cells, go to an address that is specified in another cell and paste the data. The address changes relative to the current date. I have been give a solution that worked with my simplified example as shown in the attached Word file. The problem is that the data in cells B27:B33 on the worksheet "Entry Sheet" in my actual working workbook are the result of calculations from another sheet and not simply numerical data. The solution I have been given is pasting the cells data, i.e. the formulae, rather than the values. Does anyone have any suggestions how this macro may be changed to work as a "Paste Special + Value" rather than just "Paste"? The original solution ftrom Rowan Drummond: 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 +-------------------------------------------------------------------+ |Filename: DataCopyExample.doc | |Download: http://www.excelforum.com/attachment.php?postid=3940 | +-------------------------------------------------------------------+ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pasting to an address specified in a cell
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 xlPasteValues End Sub -- Regards, Tom Ogilvy "WightRob" wrote in message ... I am trying to sort out a macro that will copy a range of cells, go to an address that is specified in another cell and paste the data. The address changes relative to the current date. I have been give a solution that worked with my simplified example as shown in the attached Word file. The problem is that the data in cells B27:B33 on the worksheet "Entry Sheet" in my actual working workbook are the result of calculations from another sheet and not simply numerical data. The solution I have been given is pasting the cells data, i.e. the formulae, rather than the values. Does anyone have any suggestions how this macro may be changed to work as a "Paste Special + Value" rather than just "Paste"? The original solution ftrom Rowan Drummond: 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 +-------------------------------------------------------------------+ |Filename: DataCopyExample.doc | |Download: http://www.excelforum.com/attachment.php?postid=3940 | +-------------------------------------------------------------------+ -- WightRob ------------------------------------------------------------------------ WightRob's Profile: http://www.excelforum.com/member.php...o&userid=13799 View this thread: http://www.excelforum.com/showthread...hreadid=478624 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pasting to an address specified in a cell
Thanks Rowan & thanks Tom, Thats' sorted it now and helped me to understand VBA programming a little more. Can anyone suggest any good sites for VBA tutorials? Regards Rob :) :) :) -- WightRob ------------------------------------------------------------------------ WightRob's Profile: http://www.excelforum.com/member.php...o&userid=13799 View this thread: http://www.excelforum.com/showthread...hreadid=478624 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pasting to an address specified in a cell
You're welcome Rob.
Try these links from David McRitchie for getting started with VBA. http://www.mvps.org/dmcritchie/excel/getstarted.htm http://www.mvps.org/dmcritchie/excel...m#vbatutorials Regards Rowan WightRob wrote: Thanks Rowan & thanks Tom, Thats' sorted it now and helped me to understand VBA programming a little more. Can anyone suggest any good sites for VBA tutorials? Regards Rob :) :) :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pasting to calculated address | Excel Discussion (Misc queries) | |||
=HYPERLINK("#"&CELL("address", ADDRESS(MATCH(VALUE(B3),Range ... ? | Excel Worksheet Functions | |||
Return cell address of a cell based on contents of cell. | Excel Worksheet Functions | |||
Copying cell contents from many cells and pasting into one cell | Excel Discussion (Misc queries) | |||
Pasting on Filtered Data Sheets without pasting onto hidden cells | Excel Discussion (Misc queries) |