PDA

View Full Version : Expected End of Statement



rboothe
11-07-2010, 11:11 PM
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 !!???



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

Bob Phillips
11-08-2010, 01:43 AM
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

mdmackillop
11-09-2010, 06:30 AM
As In XLD's code the PasteSpecial line must be on a separate line from the copy

rboothe
11-09-2010, 02:15 PM
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
11-09-2010, 02:37 PM
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
Workbooks(pathName).Sheets(sheetName).Range(rangeName)(1).PasteSpecial xlPasteFormulas, Transpose:=True

rboothe
11-09-2010, 04:03 PM
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
11-09-2010, 04:33 PM
You can copy and paste all in one statement, pastespecial need separate statements.

rboothe
11-09-2010, 09:17 PM
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
11-10-2010, 12:57 AM
Can you post an example workbook?

rboothe
11-10-2010, 06:38 AM
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
11-10-2010, 06:39 AM
Here's the other file...just realized i can only upload one at a time...

Bob Phillips
11-10-2010, 10:31 AM
I wouldn't use Activecell myself



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").PasteSpecial 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

rboothe
11-10-2010, 11:30 PM
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
11-11-2010, 12:53 AM
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
11-11-2010, 06:26 AM
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)

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



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
11-11-2010, 07:21 AM
MDs code showed what I was suggesting, but I would be even more cautious



c.Offset(0, - c.Column + 1).Offset(0, 6).Resize(,5).Copy