Copy Paste sheet Range to another worksheet

Printable View

01-12-2012, 05:34 AM
jammer6_9

Copy Paste sheet Range to another worksheet

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.

[VBA]
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

[/VBA]
01-12-2012, 11:19 AM
Sir Babydum GBE

Hi Jammer6_9

Could you attach an example of your worksheet?
01-12-2012, 01:06 PM
jammer6_9

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.

Quote:

Originally Posted by Sir Babydum GBE

Hi Jammer6_9

Could you attach an example of your worksheet?
01-12-2012, 03:52 PM
mdmackillop

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

Quote:

Originally Posted by mdmackillop

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).
01-12-2012, 11:45 PM
jammer6_9

1 Attachment(s)

Hi, find attached sample file. Thanks a lot.
01-13-2012, 02:09 AM
p45cal

try:
[vba]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
[/vba]
01-13-2012, 02:13 AM
jammer6_9

Thanks that works! Seems that I missed to put the source range that I have declared.

Quote:

Originally Posted by p45cal

try:
[VBA]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
[/VBA]