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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 02:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com