I'm quite new to Excel and completely new to this forum.
我对Excel很新,对这个论坛来说还是全新的。
I have taken the code from the below forum and modified it to my need.
我从下面的论坛中获取了代码并根据我的需要对其进行了修改。
http://pressf1.pcworld.co.nz/showthread.php?90122-Creating-Macro-to-copy-and-paste-data-into-the-next-empty-column。
Sub copyTotals()
Dim TargetSht As Worksheet, SourceSht As Worksheet, SourceRow As Integer, SourceCells As Range
Set SourceSht = ThisWorkbook.Sheets("DUN - Jan")
Set TargetSht = ThisWorkbook.Sheets("DUN Jan - Jan,Feb,Mar,Apr")
Set SourceCells = SourceSht.Range("L36,N36")
If TargetSht.Range("C11").Value = "" Then
SourceRow = 1
ElseIf TargetSht.Range("C41") <> "" Then
MsgBox ("The sheet is full, you need to create a new sheet")
Else
SourceRow = TargetSht.Range("C41").End(xlUp).Row + 1
End If
SourceCells.Copy TargetSht.Cells(SourceRow, 3)
End Sub
The problem is that the values pasted have the formating of the source and i only want to paste the values.
问题是粘贴的值具有源的格式,我只想粘贴值。
Can someone please help me with this.
有人可以帮我这个。
4 个解决方案
#1
4
Use .Copy together with .PasteSpecial. Instead of:
与.PasteSpecial一起使用.Copy。代替:
SourceCells.Copy TargetSht.Cells(2, SourceCol)
Do this:
做这个:
SourceCells.Copy
TargetSht.Cells(2, SourceCol).PasteSpecial xlPasteValues
#2
2
Using the macro recorder yields this kind of thing. I use it a lot when stuck.
使用宏录制器会产生这种情况。卡住后我经常使用它。
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
#3
2
You don't need to copy and paste to do this (at least in Excel 2010 and above, I think). You just set the .Value of the range to equal itself. eg:
您不需要复制和粘贴来执行此操作(至少在Excel 2010及更高版本中,我认为)。您只需将范围的.Value设置为相等。例如:
rng.Value = rng.Value
or
要么
Sheet1.Range("A1:A10").Value = Sheet1.Range("A1:A10").Value
since .Value "Returns or sets a Variant value that represents the value of the specified range."
因为.Value“返回或设置一个表示指定范围值的Variant值。”
Note that this just works for values, not formats.
请注意,这仅适用于值,而不适用于格式。
http://msdn.microsoft.com/en-us/library/office/ff195193(v=office.15).aspx
http://msdn.microsoft.com/en-us/library/office/ff195193(v=office.15).aspx
#4
0
right click in the cell and select paste special then you can choose values only
右键单击单元格并选择paste special,然后您只能选择值
edit: for macros its the same principle use .PasteSpecial xlPasteValues like here
编辑:对于宏它的原理使用相同.PasteSpecial xlPasteValues就像这里一样
Set rng6 = .Range("A3").End(xlDown).Offset(0, 41)
rng6.Copy
ActiveSheet.Paste Destination:=Worksheets("Positions").Range("W2")
#1
4
Use .Copy together with .PasteSpecial. Instead of:
与.PasteSpecial一起使用.Copy。代替:
SourceCells.Copy TargetSht.Cells(2, SourceCol)
Do this:
做这个:
SourceCells.Copy
TargetSht.Cells(2, SourceCol).PasteSpecial xlPasteValues
#2
2
Using the macro recorder yields this kind of thing. I use it a lot when stuck.
使用宏录制器会产生这种情况。卡住后我经常使用它。
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
#3
2
You don't need to copy and paste to do this (at least in Excel 2010 and above, I think). You just set the .Value of the range to equal itself. eg:
您不需要复制和粘贴来执行此操作(至少在Excel 2010及更高版本中,我认为)。您只需将范围的.Value设置为相等。例如:
rng.Value = rng.Value
or
要么
Sheet1.Range("A1:A10").Value = Sheet1.Range("A1:A10").Value
since .Value "Returns or sets a Variant value that represents the value of the specified range."
因为.Value“返回或设置一个表示指定范围值的Variant值。”
Note that this just works for values, not formats.
请注意,这仅适用于值,而不适用于格式。
http://msdn.microsoft.com/en-us/library/office/ff195193(v=office.15).aspx
http://msdn.microsoft.com/en-us/library/office/ff195193(v=office.15).aspx
#4
0
right click in the cell and select paste special then you can choose values only
右键单击单元格并选择paste special,然后您只能选择值
edit: for macros its the same principle use .PasteSpecial xlPasteValues like here
编辑:对于宏它的原理使用相同.PasteSpecial xlPasteValues就像这里一样
Set rng6 = .Range("A3").End(xlDown).Offset(0, 41)
rng6.Copy
ActiveSheet.Paste Destination:=Worksheets("Positions").Range("W2")