Sub TAKEDATA()
With Sheets("F")
FULRNG = .Range("A" & Rows.Count).End(xlUp).Row
Range("A2:A" & FULRNG).Copy SHEET("RESULT").RANGE("B11").PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
End Sub

I want to copy but I want copied data to be pastespecial on
destination cell. I have tried above code but I am getting error. I
want to do it by using "With Sheets("F")" statement. Is there way I
can copy the way I am doing in above code but data should be
pastespecial. any help will be very useful. Thanks

Hi Kamranr

Try:

'==========
Public Sub TAKEDATA()
Dim FULRNG As Long

With Sheets("F")
FULRNG = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A2:A" & FULRNG).Copy
Sheets("RESULT").Range("B11").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End With
End Sub
'<<==========



---
Regards.
Norman


"K" wrote in message
...
Sub TAKEDATA()
With Sheets("F")
FULRNG = .Range("A" & Rows.Count).End(xlUp).Row
Range("A2:A" & FULRNG).Copy SHEET("RESULT").RANGE("B11").PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
End Sub

I want to copy but I want copied data to be pastespecial on
destination cell. I have tried above code but I am getting error. I
want to do it by using "With Sheets("F")" statement. Is there way I
can copy the way I am doing in above code but data should be
pastespecial. any help will be very useful. Thanks

Here are the thing I corrected

1) sheets was missing an S
2) You can't have copy and pastespecial in the same instruction. Has to be
two instructions
3) .Range was missing a period in the front
4) In the Pastespecial you don't need to include the options that are false

Sub TAKEDATA()
With Sheets("F")
FULRNG = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A2:A" & FULRNG).Copy
Sheets("RESULT").Range("B11").PasteSpecial _
Paste:=xlPasteValues
End With
End Sub

"K" wrote:

Sub TAKEDATA()
With Sheets("F")
FULRNG = .Range("A" & Rows.Count).End(xlUp).Row
Range("A2:A" & FULRNG).Copy SHEET("RESULT").RANGE("B11").PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
End Sub

I want to copy but I want copied data to be pastespecial on
destination cell. I have tried above code but I am getting error. I
want to do it by using "With Sheets("F")" statement. Is there way I
can copy the way I am doing in above code but data should be
pastespecial. any help will be very useful. Thanks

On 13 May, 10:16, Joel wrote:
Here are the thing I corrected

1) sheets was missing an S
2) You can't have copy and pastespecial in the same instruction. *Has to be
two instructions
3) .Range was missing a period in the front
4) In the Pastespecial you don't need to include the options that are false

Sub TAKEDATA()
With Sheets("F")
* *FULRNG = .Range("A" & Rows.Count).End(xlUp).Row
* *.Range("A2:A" & FULRNG).Copy
* *Sheets("RESULT").Range("B11").PasteSpecial _
* * * Paste:=xlPasteValues
End With
End Sub



"K" wrote:
Sub TAKEDATA()
With Sheets("F")
FULRNG = .Range("A" & Rows.Count).End(xlUp).Row
Range("A2:A" & FULRNG).Copy SHEET("RESULT").RANGE("B11").PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
End Sub

I want to copy but I want copied data to be pastespecial on
destination cell. *I have tried above code but I am getting error. I
want to do it by using "With Sheets("F")" statement. *Is there way I
can copy the way I am doing in above code but data should be
pastespecial. *any help will be very useful. *Thanks- Hide quoted text -

- Show quoted text -

Thanks for replying. I already know the way you told me as i tried it
before posting my question. The problem is that when i run this macro
the one you showed in your answer i can see macro going to
Sheet("RESULT") and pasting data and come back to orginal sheet of
where i click the button to run the macro. I thought if i use " With
Sheets("F") " statement instead of " Sheets("F").select " then i'll
not see macro runing around the sheets to do its job. I do want macro
to do job but it should not show that it doing it's job. For example
if i put this line in my macro
.Range("A2:A" & FULRNG).Copy SHEETS("RESULT").RANGE("B11")
then i will not see that macro gone to SHEETS("RESULT") to paste the
data and came back but if i put this line
.Range("A2:A" & FULRNG).Copy
Sheets("RESULT").Range("B11").PasteSpecial _
Paste:=xlPasteValues
then i can see macro gone to SHEETS("RESULT") to paste the data and
then orginal sheet been activated all though it happes very quick but
i dont want macro to do that way. all i am trying to do is copy data
from sheet F to sheet RESULT but macro should not show visibly that it
has done its job. i hope that i was able to explain what i am trying
to do. Please if you have any suggestions.

Hi K,

Try the following version:

'==========
Public Sub TAKEDATA()
Dim srcRng As Range
Dim destRng As Range
Dim irow As Long

With Sheets("F")
irow = .Range("A" & Rows.Count).End(xlUp).Row
Set srcRng = .Range("A2:A" & irow)
With srcRng
Set destRng = Sheets("RESULT").Range("B11"). _
Resize(.Rows.Count, .Columns.Count)
End With
End With

destRng.Value = srcRng.Value

End Sub
'<<==========



---
Regards.
Norman


"K" wrote in message
...
On 13 May, 10:16, Joel wrote:
Here are the thing I corrected

1) sheets was missing an S
2) You can't have copy and pastespecial in the same instruction. Has to be
two instructions
3) .Range was missing a period in the front
4) In the Pastespecial you don't need to include the options that are
false

Sub TAKEDATA()
With Sheets("F")
FULRNG = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A2:A" & FULRNG).Copy
Sheets("RESULT").Range("B11").PasteSpecial _
Paste:=xlPasteValues
End With
End Sub



"K" wrote:
Sub TAKEDATA()
With Sheets("F")
FULRNG = .Range("A" & Rows.Count).End(xlUp).Row
Range("A2:A" & FULRNG).Copy SHEET("RESULT").RANGE("B11").PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
End Sub

I want to copy but I want copied data to be pastespecial on
destination cell. I have tried above code but I am getting error. I
want to do it by using "With Sheets("F")" statement. Is there way I
can copy the way I am doing in above code but data should be
pastespecial. any help will be very useful. Thanks- Hide quoted text -

- Show quoted text -

Thanks for replying. I already know the way you told me as i tried it
before posting my question. The problem is that when i run this macro
the one you showed in your answer i can see macro going to
Sheet("RESULT") and pasting data and come back to orginal sheet of
where i click the button to run the macro. I thought if i use " With
Sheets("F") " statement instead of " Sheets("F").select " then i'll
not see macro runing around the sheets to do its job. I do want macro
to do job but it should not show that it doing it's job. For example
if i put this line in my macro
..Range("A2:A" & FULRNG).Copy SHEETS("RESULT").RANGE("B11")
then i will not see that macro gone to SHEETS("RESULT") to paste the
data and came back but if i put this line
..Range("A2:A" & FULRNG).Copy
Sheets("RESULT").Range("B11").PasteSpecial _
Paste:=xlPasteValues
then i can see macro gone to SHEETS("RESULT") to paste the data and
then orginal sheet been activated all though it happes very quick but
i dont want macro to do that way. all i am trying to do is copy data
from sheet F to sheet RESULT but macro should not show visibly that it
has done its job. i hope that i was able to explain what i am trying
to do. Please if you have any suggestions.

On 13 May, 23:56, "Norman Jones"
wrote:
Hi K,

Try the following version:

'==========
Public Sub TAKEDATA()
* * Dim srcRng As Range
* * Dim destRng As Range
* * Dim irow As Long

* * With Sheets("F")
* * * * irow = .Range("A" & Rows.Count).End(xlUp).Row
* * * * Set srcRng = .Range("A2:A" & irow)
* * * * With srcRng
* * * * * * Set destRng = Sheets("RESULT").Range("B11"). _
* * * * * * * * * * * * * Resize(.Rows.Count, .Columns.Count)
* * * * End With
* * End With

* * * *destRng.Value = srcRng.Value

End Sub
'<<==========

---
Regards.
Norman

"K" wrote in message

...
On 13 May, 10:16, Joel wrote:





Here are the thing I corrected

1) sheets was missing an S
2) You can't have copy and pastespecial in the same instruction. Has to be
two instructions
3) .Range was missing a period in the front
4) In the Pastespecial you don't need to include the options that are
false

Sub TAKEDATA()
With Sheets("F")
FULRNG = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A2:A" & FULRNG).Copy
Sheets("RESULT").Range("B11").PasteSpecial _
Paste:=xlPasteValues
End With
End Sub

"K" wrote:
Sub TAKEDATA()
With Sheets("F")
FULRNG = .Range("A" & Rows.Count).End(xlUp).Row
Range("A2:A" & FULRNG).Copy SHEET("RESULT").RANGE("B11").PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
End Sub

I want to copy but I want copied data to be pastespecial on
destination cell. I have tried above code but I am getting error. I
want to do it by using "With Sheets("F")" statement. Is there way I
can copy the way I am doing in above code but data should be
pastespecial. any help will be very useful. Thanks- Hide quoted text -

- Show quoted text -

Thanks for replying. *I already know the way you told me as i tried it
before posting my question. *The problem is that when i run this macro
the one you showed in your answer i can see macro going to
Sheet("RESULT") and pasting data and come back to orginal sheet of
where i click the button to run the macro. *I thought if i use " With
Sheets("F") " statement instead of " Sheets("F").select " then i'll
not see macro runing around the sheets to do its job. *I do want macro
to do job but it should not show that it doing it's job. *For example
if i put this line in my macro
.Range("A2:A" & FULRNG).Copy SHEETS("RESULT").RANGE("B11")
then i will not see that macro gone to SHEETS("RESULT") to paste the
data and came back but *if i put this line
.Range("A2:A" & FULRNG).Copy
Sheets("RESULT").Range("B11").PasteSpecial _
Paste:=xlPasteValues
then i can see macro gone to SHEETS("RESULT") to paste the data and
then orginal sheet been activated all though it happes very quick but
i dont want macro to do that way. *all i am trying to do is copy data
from sheet F to sheet RESULT but macro should not show visibly that it
has done its job. *i hope that i was able to explain what i am trying
to do. *Please if you have any suggestions.- Hide quoted text -

- Show quoted text -

Thanks Norman Jones , Thats the code i wanted