Expected End of Statement

[rboothe]

Can't figure out what's wrong with following code: (I keep getting compile error : expected end of statement..and it points to xlPasteFormulas
I've been at it for HOURS now !!???


[VBA]
Sub GenerateQuote()


Dim wbName As String
Dim pathName As String
Dim sheetName As String
Dim rangeName As String
Dim wbook As Workbook


wbName = "pnvtest.xls"
pathName = "C:\New Drive C\Education\VBA\R & D\" & wbName
sheetName = "Details"
rangeName = "addressLine1"

'Check if pnv is open (and ignore error if it isn't)
On Error Resume Next
Set wbook = Workbooks(wbName)
If wbook Is Nothing Then 'Not open
Workbooks.Open (pathName)
Else 'It is open

End If
'Copy over info

With ActiveCell
Range(.Offset(0, 6), .Offset(0, 10)).Copy Workbooks(pathName).Sheets(sheetName).Range(rangeName).PasteSpecial xlPasteFormulas, Transpose:=True
End With



End Sub
[/VBA]

[Bob Phillips]

[vba]

Sub GenerateQuote()
Dim wbName As String
Dim pathName As String
Dim sheetName As String
Dim rangeName As String
Dim wbook As Workbook


wbName = "pnvtest.xls"
pathName = "C:\New Drive C\Education\VBA\R & D\" & wbName
sheetName = "Details"
rangeName = "addressLine1"

'Check if pnv is open (and ignore error if it isn't)
On Error Resume Next
Set wbook = Workbooks(wbName)
If wbook Is Nothing Then 'Not open
Workbooks.Open (pathName)
Else 'It is open

End If
'Copy over info

With ActiveCell
Range(.Offset(0, 6), .Offset(0, 10)).Copy
Workbooks(pathName).Sheets(sheetName).Range(rangeName).PasteSpecial xlPasteFormulas, Transpose:=True
End With
End Sub
[/vba]

[mdmackillop]

As In XLD's code the PasteSpecial line must be on a separate line from the copy

[rboothe]

The error is finally gone ...but nothing happens. The range to be copied is selected but its not pasted in the target file. Does that have anything to do with my use of variables instead of the actual filenames in the proceedure (ie. pathName instead of C:\New Drive C\Education\VBA\R & D\pnvtest.xls) ??

[mdmackillop]

The target workbook would need to be open for this code, and so the workbook name would not comprise the whole path, just the name.
Also, do the range sizes match or is rangename a single cell? If not, try
[vba]Workbooks(pathName).Sheets(sheetName).Range(rangeName)(1).PasteSpecial xlPasteFormulas, Transpose:=True [/vba]

[rboothe]

I tried adding (1) but still nothing happens.

And can you explain why when the destination is put on the next line (in the Copy method) the error goes away? The book i'm reading has the source and destination on the same line (and they use the continuation symbol " _" when the wording is too long for one line)

[Bob Phillips]

You can copy and paste all in one statement, pastespecial need separate statements.

[rboothe]

Still nothing happens after the source range is selected for copy. It apparently works up until copy (i.e. the range is highlighted with the blinking box) but it doesn't paste after that - nothing happens.

[Bob Phillips]

Can you post an example workbook?

[rboothe]

Ok, i've attached the 2 files i'm working with:

1. "email dbase.xls (this is the source file with the customer info to be copied)
2. "pnvtest.xls" (this is the file to which the customer info should be copied - to create a quote)

The code is in "email dbase.xls"

[rboothe]

Here's the other file...just realized i can only upload one at a time...

[Bob Phillips]

I wouldn't use Activecell myself

[vba]

Sub GenerateQuote()

'Test Code:
' Range(ActiveCell.Offset(0,6):ActiveCell.Offset(0,11)).Copy Range("G45")
' Range("ActiveCell.Offset(0, 6)").Copy Range("G45")
' Range(Cells(ActiveCell.Row, 6), Cells(ActiveCell.Row, 11).Copy Range("G45")
' With ActiveCell
' Range(.Offset(0, 6), .Offset(0, 12)).Copy
' Workbooks("pnvtest.xls").Sheets("Details").Range("addressLine1").PasteSpeci al xlPasteFormulas, Transpose:=True
' End With
'On Error GoTo OpenPnv
' Range(.Offset(0, 6), .Offset(0, 10)).Copy
' Workbooks(pathName).Sheets(sheetName).Range(rangeName).PasteSpecial xlPasteValues, Transpose:=True

Dim wbName As String
Dim pathName As String
Dim sheetName As String
Dim rangeName As String
Dim wbook As Workbook


wbName = "pnvtest.xls"
pathName = "C:\New Drive C\Education\VBA\R & D\" & wbName
sheetName = "Details"
rangeName = "addressLine1"

'Check if pnv is open (and ignore error if it isn't)
On Error Resume Next
Set wbook = Workbooks(wbName)
On Error GoTo 0
If wbook Is Nothing Then 'Not open

Set wbook = Workbooks.Open(pathName)
End If

'Copy over info
Workbooks("email dbase.xls").Activate
With ActiveCell

Range(.Offset(0, 6), .Offset(0, 10)).Copy
wbook.Worksheets(1).Range(rangeName)(1).PasteSpecial xlPasteFormulas, Transpose:=True
End With
End Sub
[/vba]

[rboothe]

Well it now works...finally. Would love to know why..what does "On Error Goto 0" do ?

And if not ActiveCell what would you use ? (I want to place the cursor by the invoice# in the source sheet to choose which record to copy over)

[Bob Phillips]

The On Error Goto 0 is used to reset error handling. Just prior to that I had set On Error Resume Next so that the Set wbook = Workbooks(wbName) line doesn't fail.

If you can be sure the Invoice # is selected I guess Activecell is okay, but I would probably have an Inputbox in the code and ask the user to select the row to process.

[mdmackillop]

And if not ActiveCell what would you use ? (I want to place the cursor by the invoice# in the source sheet to choose which record to copy over)

[vba]
Dim c As Range
Workbooks("email dbase.xls").Activate
Set c = Application.InputBox("Select cell", "My Selector", , , , , , 8)
Range(c.Offset(0, 6), c.Offset(0, 10)).Copy
wbook.Worksheets(1).Range(rangeName)(1).PasteSpecial xlPasteFormulas, Transpose:=True

[/vba]

BTW, If you have the invoice number in the original workbook, you could locate C using Find to avoid the need to manually select a cell.

[Bob Phillips]

MDs code showed what I was suggesting, but I would be even more cautious

[vba]

c.Offset(0, - c.Column + 1).Offset(0, 6).Resize(,5).Copy
[/vba]