PDA

View Full Version : Copy Paste sheet Range to another worksheet



jammer6_9
01-12-2012, 05:34 AM
I was trying to copy a sheet range to another worksheet with the below code. It seems to work if there is more than 1 visible row to be copied but when its just only one visible row, it copy to the entire target range as duplicates.


Sub Create_Supplier()

Dim RngSup As Range
Dim FmlaSup As Range
Set FmlaSup = ActiveSheet.Range("A10:bc505")
Set RngSup = Sheets("Supplier_Distribution").Range("b10")
Application.GoTo Reference:="Supplier_Clear"
Selection.ClearContents

FmlaSup.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Sheets("Supplier_Distribution").Select
Range("a1").Select
End Sub

Sir Babydum GBE
01-12-2012, 11:19 AM
Hi Jammer6_9

Could you attach an example of your worksheet?

jammer6_9
01-12-2012, 01:06 PM
Hi, thanks for the reply. Let me see how can I get a sample sheet as the file is huge and sheets are link to each other.


Hi Jammer6_9

Could you attach an example of your worksheet?

mdmackillop
01-12-2012, 03:52 PM
I don't see anything to limit the copying of less than the whole range specified.

p45cal
01-12-2012, 04:32 PM
I don't see anything to limit the copying of less than the whole range specified.
In my exploration, if the source range is autofiltered, only visible cells seem to be copied (xl2003).

jammer6_9
01-12-2012, 11:45 PM
Hi, find attached sample file. Thanks a lot.

p45cal
01-13-2012, 02:09 AM
try:
Sub Create_Supplier()
Dim RngSup As Range
Dim FmlaSup As Range

Set FmlaSup = ActiveSheet.Range("A10:bc505")
Set RngSup = Sheets("Supplier_Distribution").Range("b10")
Range("Supplier_Clear").ClearContents
Sheets("Supplier_Distribution").Select
FmlaSup.Copy
RngSup.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("a1").Select
End Sub

jammer6_9
01-13-2012, 02:13 AM
Thanks that works! Seems that I missed to put the source range that I have declared.


try:
Sub Create_Supplier()
Dim RngSup As Range
Dim FmlaSup As Range

Set FmlaSup = ActiveSheet.Range("A10:bc505")
Set RngSup = Sheets("Supplier_Distribution").Range("b10")
Range("Supplier_Clear").ClearContents
FmlaSup.Copy
RngSup.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Supplier_Distribution").Select
Range("a1").Select
End Sub