Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paste Special
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=551639 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paste Special
Looking at:
destrange.Value = sourceRange.Value you can test for the single quote and insure that it gets copied over: Sub gsnu() Dim r1, r2 As Range Set r1 = Range("A1") Set r2 = Range("A2") If r1.PrefixCharacter = "'" Then r2.Value = Chr(39) & r1.Value Else r2.Value = r1.Value End If End Sub You need to take this special step to preserve the single quote because something.value=something.value is a standard way of removing single quotes. -- Gary's Student "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=551639 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paste Special
Hi there, being dum as I am, where would I place your code in the code I already have? -- sparx ------------------------------------------------------------------------ sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787 View this thread: http://www.excelforum.com/showthread...hreadid=551639 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paste Special
replace:
destrange.Value = sourceRange.Value with: If sourceRange.PrefixCharacter = "'" Then destrange.Value = Chr(39) & sourceRange.Value Else destrange.Value = sourceRange.Value End If -- Gary's Student "sparx" wrote: Hi there, being dum as I am, where would I place your code in the code I already have? -- sparx ------------------------------------------------------------------------ sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787 View this thread: http://www.excelforum.com/showthread...hreadid=551639 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste Special and Linking | Excel Discussion (Misc queries) | |||
Paste Special Problem | Excel Discussion (Misc queries) | |||
strange Paste special | Excel Discussion (Misc queries) | |||
paste special - formulas | Excel Worksheet Functions | |||
Paste special treats cells as a picture | Excel Discussion (Misc queries) |