PASTESPECIAL THE COPIED DATA IN " With Sheet " STATEMENT

K

K

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
 
N

Norman Jones

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
'<<==========
 
J

Joel

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

K

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 tobe
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






- Show quoted text -
Click to expand...

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

Norman Jones

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


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






- Show quoted text -
Click to expand...

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

K

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








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 -
Click to expand...

Thanks Norman Jones , Thats the code i wanted
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Having difficulty copying and pasting on VBA 3
A simple if <> nn then Msgbox "No Match" 5
Copy columns and paste with blank column between 5
Should I be content with the time code takes? 2
Name the range AFTER copied to new sheet 6
Help with condensing a lot of copying/pasting... 4
Eliminate blanks while copy row and transpose 11
Formatting and copy or copy and formatting ?? 2

Top