Solved: VBA Copy 'n Paste w/o formats?

[RonMcK]

How do I modify this line of 'copy & paste' code so it does paste special values?
wbSource.Range(Cells(i, 1), Cells(i, 6)).Copy wsDest4.Cells(i, 1)
When I manually copy and paste, Excel pops a 'paste option' icon and opens a menu, where I can:
Keep Source Formatting
Match destination Formatting
Values and Number Formatting
Keep Source Column Widths
Formatting Only
Link CellsSo, my second question is how can I also accomplish these which are other variations on edit paste special?

TIA,

Thanks,

Ron
Orlando, FL

[Simon Lloyd]

Try variations of this!

Sub Copy_n_PasteSpecial()
wbSource.Range(Cells(i, 1), Cells(i, 6)).Copy
With wsDest4.Cells(i, 1)
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End With
End Sub

[RonMcK]

Thanks, Simon!

Ron
Orlando, FL

[RonMcK]

Simon,

I tripping myself up somewhere in the process. Perhaps a fresh pair of eyes (yours) can see what is eluding me. Here is my code with some notes:

Sub Copy_n_PasteSpecial()
Dim i As Long
Dim wbSource As Worksheet
Dim wsDest As Worksheet
Set wbSource = Workbooks("Sample-rjm.xls").Sheets("Source")
Set wsDest = Workbooks("Sample-rjm.xls").Sheets("Target")
i = 2
With Workbooks("Sample-rjm.xls")
With .Sheets("Source")
.Range("A2", "G7").Copy ' this works
' .Range(Cells(i, 1), Cells(i, 6)).Copy ' gives Error 1004, Method _Default of object Range failed.
With wsDest.Cells("E2") ' gives Error 13, Type mismatch
' With wsDest.Cells(Cells(i, 5)) ' gives Error 1004, Method _Default of object Range failed.
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End With
End With
End With
End Sub

I'm running Excel 2004 under Mac Office. I've uploaded a copy of my file for your edification and amusement.

Thanks,

Ron
Orlando, FL

[mdmackillop]

As an alternative to Copy/PasteSpecial Values you can use

wsDest4.Cells(i, 1).Resize(, 6).Value = wbSource.Cells(i, 1).Resize(, 6).Value

[mdmackillop]

Move the qualifier to precede Cells


Range(.Cells(i, 1), .Cells(i, 6)).Copy

[mdmackillop]

Here's a more "general" example, using your basic code

Sub Copy_n_PasteSpecial()
Dim i As Long
Dim wbSource As Worksheet
Dim wsDest As Worksheet
Set wbSource = Workbooks(2).Sheets(1)
Set wsDest = Workbooks(3).Sheets(2)
i = 2
With wbSource
Range(.Cells(i, 1), .Cells(i, 6)).Copy
wsDest.Range("E4").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End With
Application.CutCopyMode = False
End Sub

[RonMcK]

Malcolm,

Thank you. I'll give this a spin when I get home from work. (3-4 hr from now.)


Related query: Can one combine the code behind:
Select Range > Edit > Goto > Special > Visible Cells Only > OK > Copy > Paste
with the above code for Edit > Paste special (values, in my case)?


Thanks,

Ron
Orlando, FL

[Simon Lloyd]

something like: range("A:A").specialcells(xlvisible).copyadjust range to suit!

[RonMcK]

Simon,

What you show looks like the 'swifter' way to code:

Range("A1:F12").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
The really cool thing is that I can combine this with Malcolm's code for Edit Paste Special, so, after hiding column D on my Sources w/s, when I run the following, Source columns A-C and E-G for lines 2-7 end up in E1:J6 without any of the formatting in the Source (i.e. no borders, no centered data, no bold headings)

Sub Copy_n_PasteSpecial3()
Dim i As Long
Dim wbSource As Worksheet
Dim wsDest As Worksheet
Set wbSource = Workbooks("Sample-rjm.xls").Sheets("Source")
Set wsDest = Workbooks("Sample-rjm.xls").Sheets("Target")
i = 2
With wbSource
Range(Cells(i, 1), Cells(7, 7)).SpecialCells(xlVisible).Copy
wsDest.Range("E1").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End With
Application.CutCopyMode = False
End Sub
A very big THANK YOU! Simon and Malcolm.

Thanks,

Ron
Orlando, FL

[RonMcK]

Malcolm and Simon,

Well, I brought the code home (from Mac Excel 2004) and am trying to run it on my PC Excel 2002. Strangeness!

Sub Copy_n_PasteSpecial3()
Dim i As Long
Dim wbSource As Worksheet
Dim wsDest As Worksheet
Set wbSource = Workbooks("Sample-rjm.xls").Sheets("Source")
Set wsDest = Workbooks("Sample-rjm.xls").Sheets("Target")
i = 2
With wbSource
Range(Cells(i, 1), Cells(7, 7)).SpecialCells(xlVisible).Copy
wsDest.Range("E1").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End With
Application.CutCopyMode = False
End Sub


For some reason, the code is ignoring wbSource and choosing to read the data from range(cells(i, 2), cells(i,7) on wsDest and, then, writing it back to range("E1") on wsDest.

Any ideas why this might happen?

Thanks,

Ron
Orlando, FL

[RonMcK]

All y'all can stand down.

Solved problem by inserting 'wbSource.Activate' immediately above 'With wbSource'. Things now behave as expected.

Thanks for letting me thrash that one out by myself. :doh:

Ron
Orlando, FL
:yes

[mdmackillop]

You're missing the essential "." qualifiers in the With statement
Range(.Cells(i, 1), .Cells(7, 7)).SpecialCells(xlVisible).Copy

[RonMcK]

Malcolm,

Arrgghh! (repeating to myself 1000 times: don't forget the full-stops before the Cells.)

Thanks,

Ron
Orlando, FL