Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special
Hi all, I have some code below that another user kindly provided bu would ask if anybody could help me with a slight modification - th code will copy and paste special a range from one worksheet to anothe worksheet and works great - but I have had some items that start wit zero's in the origin and after the paste special ( because I only wan values and not formula's to copy ) will convert say '012345 to 1234 but I do need the '012345 to be displayed as it is - any suggestion will be kindly received. Dim sourceRange As Range Dim destrange As Range Dim Lr As Long Lr = LastRow(Sheets("Worksheet1")) + 1 Set sourceRange = Sheets("Worksheet2").Range("BO7:CZ21") With sourceRange Set destrange = Sheets("Worksheet1").Range("A" & Lr). _ Resize(.Rows.Count, .Columns.Count) End With destrange.Value = sourceRange.Value End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Function Lastcol(sh As Worksheet) On Error Resume Next Lastcol = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 End Functio -- spar ----------------------------------------------------------------------- sparx's Profile: http://www.excelforum.com/member.php...fo&userid=1678 View this thread: http://www.excelforum.com/showthread.php?threadid=55165 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special
Add this line...
destrange.NumberFormat = "@" just before this line... destrange.Value = sourceRange.Value -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "sparx" wrote in message Hi all, I have some code below that another user kindly provided but would ask if anybody could help me with a slight modification - the code will copy and paste special a range from one worksheet to another worksheet and works great - but I have had some items that start with zero's in the origin and after the paste special ( because I only want values and not formula's to copy ) will convert say '012345 to 12345 but I do need the '012345 to be displayed as it is - any suggestions will be kindly received. Dim sourceRange As Range Dim destrange As Range Dim Lr As Long Lr = LastRow(Sheets("Worksheet1")) + 1 Set sourceRange = Sheets("Worksheet2").Range("BO7:CZ21") With sourceRange Set destrange = Sheets("Worksheet1").Range("A" & Lr). _ Resize(.Rows.Count, .Columns.Count) End With destrange.Value = sourceRange.Value End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Function Lastcol(sh As Worksheet) On Error Resume Next Lastcol = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special
Instead of
destrange.Value = sourceRange.Value try sourceRange.Copy destrange Does that help? "sparx" wrote: Hi all, I have some code below that another user kindly provided but would ask if anybody could help me with a slight modification - the code will copy and paste special a range from one worksheet to another worksheet and works great - but I have had some items that start with zero's in the origin and after the paste special ( because I only want values and not formula's to copy ) will convert say '012345 to 12345 but I do need the '012345 to be displayed as it is - any suggestions will be kindly received. Dim sourceRange As Range Dim destrange As Range Dim Lr As Long Lr = LastRow(Sheets("Worksheet1")) + 1 Set sourceRange = Sheets("Worksheet2").Range("BO7:CZ21") With sourceRange Set destrange = Sheets("Worksheet1").Range("A" & Lr). _ Resize(.Rows.Count, .Columns.Count) End With destrange.Value = sourceRange.Value End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Function Lastcol(sh As Worksheet) On Error Resume Next Lastcol = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 End Function -- sparx ------------------------------------------------------------------------ sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787 View this thread: http://www.excelforum.com/showthread...hreadid=551652 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special
I will try both methods you describe. -- sparx ------------------------------------------------------------------------ sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787 View this thread: http://www.excelforum.com/showthread...hreadid=551652 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste and Paste Special No Longer Working - Excel 2003 | Excel Discussion (Misc queries) | |||
'paste special', 'paste link' formatting transfer | Excel Discussion (Misc queries) | |||
In Excel: add a Paste-Special Option to paste IN REVERSE ORDER. | Excel Worksheet Functions | |||
How do I capture user paste action and convert to Paste Special | Excel Programming | |||
Dynamic Copy/Paste Special Formulas/Paste Special Values | Excel Programming |