Solved: xlPasteSpecial method

[YellowLabPro]

Receiving Syntax error on PasteSpecial line:


Option Explicit
Sub CopyColumn()
Dim LastRow As Long
With Workbooks("MasterImportSheetWebStore.xls").Sheets("PCCombined_FF")
LastRow = .Range("B65536").End(xlUp).Row + 1

.Range("B4:B" & LastRow).Copy _
Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("B4")

.Range("D4:D" & LastRow).Copy _
Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("C4")

.Range("D4:D" & LastRow).Copy _
Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("C4")

.Range("J4:J" & LastRow).Copy _
Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("D4"). _
PasteSpecial xlPasteValues
Cells.Columns.AutoFit
End With
End Sub



thanks for having a look,

YLP

Edited 29-Jun-06 by geekgirlau. Reason: insert line breaks

[compariniaa]

try changing the pastespecial line to

.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

or
.PasteSpecial Paste:=xlPasteValues

it's just a guess, so it may not make a difference

[ALe]

or
.PasteSpecial(xlPasteValues)

[YellowLabPro]

ALe,
That provided a different error message box. The first error type was syntax, the current one w/ your suggesstion yielded Run-time error '1004':
Unable to get the PasteSpecial property of the Range class
Here is my entire code now: One line is commented out and the other is left in to test.

Option Explicit
Sub CopyColumn()
Dim LastRow As Long
With Workbooks("MasterImportSheetWebStore.xls").Sheets("PCCombined_FF")
LastRow = .Range("B65536").End(xlUp).Row + 1

.Range("B4:B" & LastRow).Copy _
Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("B4") 'Item Record#
.Range("D4:D" & LastRow).Copy _
Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("C4") 'Item Description
'.Range("J4:J" & LastRow).Copy _
Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("D4"). _
PasteSpecial(xlPasteValues)
.Range("H4:H" & LastRow).Copy _
Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("H4") 'Price
.Range("Q4:Q" & LastRow).Copy _
Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("J4") 'PARENT COLOR
.Range("T4:T" & LastRow).Copy _
Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("J4") 'CHILD COLOR
.Range("V4:V" & LastRow).Copy _
Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("K4") 'SIZE
.Range("AE4:AE" & LastRow).Copy _
Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("O4"). _
PasteSpecial(xlPasteValues) 'S/H Weight
.Range("AD4:AD" & LastRow).Copy _
Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("P4") 'Actual Weight
.Range("E4:E" & LastRow).Copy _
Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("S4") 'Qty.

Cells.Columns.AutoFit
End With
End Sub


Edited 29-Jun-06 by geekgirlau. Reason: insert line breaks

[mdmackillop]

Hi YLP
You don't need to include the name of the workbook containing the ActiveSheet, which must be the book where the code is run from. Regarding the Paste Special, the destination has to be on a separate line.


Option Explicit
Sub CopyColumn()
Dim LastRow As Long
With Workbooks("MasterImportSheetWebStore.xls").Sheets("PCCombined_FF")
LastRow = .Range("B65536").End(xlUp).Row + 1
.Range("B4:B" & LastRow).Copy ActiveSheet.Range("B4")
.Range("D4:D" & LastRow).Copy ActiveSheet.Range("C4")
.Range("D4:D" & LastRow).Copy ActiveSheet.Range("C4")
.Range("J4:J" & LastRow).Copy
ActiveSheet.Range("D4").PasteSpecial Paste:=xlValues
Cells.Columns.AutoFit
End With
End Sub

[lucas]

Not sure why your using pastespecial but here is an example of copying from a closed workbook. Can probably be adapted to your need:

Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
Application.ScreenUpdating = False ' turn off the screen updating

'Make path selections below
' Set wb = Workbooks.Open("f:\Temp\Social Club.xls", True, True)
Set wb = Workbooks.Open(ActiveWorkbook.Path & "\Social Club.xls")
' the sheet in this workbook to copy to
With ThisWorkbook.Worksheets("Final Results")
' read data from the source workbook
'the range to copy to in this workbook-name of sheet to copy FROM-range in closed workbook to copy
.Range("B7", "E36").Formula = wb.Worksheets("RESULTS").Range("B7", "E36").Formula
.Range("R7", "U36").Formula = wb.Worksheets("RESULTS").Range("R7", "U36").Formula

End With
wb.Close False ' close the source workbook without saving any changes
Set wb = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
End Sub

[YellowLabPro]

MD,
You are a life saver. Why does the code need to go on the line below, as opposed to the same line? I am sure this is a VBA requirement, but have not found any information regarding this language requirement.
As I am sure you are aware, I am very new to VBA. I was looking inside the Object Browser for information on the PasteSpecial function and found very little. Is there a book that would assist me in learning this from the ground up. I have Excel 2003 Power Programming and VBA and Macros for Excel, which I am reading as I go along, but these do not seem to offer some of the initial fundamental info I am missing. Or I am missing it, which could very well be the case.

thxs,

YLP.

[YellowLabPro]

Hi Lucas,
Because the original data being copied is in formula form, not value form and I need it converted to values in its end state.

cheers,

YLP

[compariniaa]

MD,
Is there a book that would assist me in learning this from the ground up. I have Excel 2003 Power Programming and VBA and Macros for Excel, which I am reading as I go along, but these do not seem to offer some of the initial fundamental info I am missing. Or I am missing it, which could very well be the case.
I started on Excel VBA Programming for Dummies by John Walkenbach and I loved it. I highly recommend it

[mdmackillop]

Hi YLP
The "same line" is a default destination, there is no paste command. So I guess when you are giving a new command, you need the new line.
Try adding ".paste" after one of the other lines. Let us know if it fails.
Regards
MD