Copy Paste sheet Range to another worksheet

[jammer6_9]

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]

[Sir Babydum GBE]

Hi Jammer6_9

Could you attach an example of your worksheet?

[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.

Hi Jammer6_9

Could you attach an example of your worksheet?

[mdmackillop]

I don't see anything to limit the copying of less than the whole range specified.

[p45cal]

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]

Hi, find attached sample file. Thanks a lot.

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

[jammer6_9]

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

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]