Cannot copy a range....

[Glaswegian]

Hi

Basically, I'm going demented....

I have a simple sheet from which I need to copy out some data. This goes to a sheet called "Report" - click a command button and the required data is copied. No problem. I'm then creating a Word template and pasting in the data. Everything works fine, except the copy from the Report sheet. No matter which way I try this, I continually get a "1004 - Application Error" message. This seems to me to be a referencing issue but I've tried every variation I can and I still get the same error. Obviously, I'm doing something daft here, but I've stared at this for so long that I can't see what it is. I've highlighted in RED both recent variations that crash. I've also checked the References dialog and have all the required Object Libraries checked.

Private Sub CommandButton1_Click()
Dim myRng As Range
Dim c As Range
Dim repRng As Range
Dim wdApp As Word.Application
Dim wdDoc As Word.Document


Set myRng = Range("C6:C35") 'the range on the question sheet that contains Yes/No etc

For Each c In myRng
    If c.Value = "No" Then 'if the answer is No
        With Sheets("Report")
            .Range("B65536").End(xlUp).Offset(1, 0).Value = c.Value 'write the answer to column B
            .Range("A65536").End(xlUp).Offset(1, 0).Value = c.Offset(0, -1).Value 'write the question to column A
        End With
    End If
Next c

Sheets("Report").Columns("A:B").AutoFit 'adjust the cell widths

'Worksheets("Report").Range("A1", Range("B65536").End(xlUp)).Copy

'Worksheets("Report").Range("A1").Select
'ActiveCell.CurrentRegion.Copy

Set wdApp = New Word.Application
'create a new instance of Word
wdApp.Visible = True

Set wdDoc = wdApp.Documents.Add(newtemplate:=True) 'add a new template
wdDoc.Activate

'paste the data
wdDoc.Application.Selection.Paste

Application.CutCopyMode = False

'do the rest of the stuff etc etc.....

Set wdApp = Nothing
End Sub

Could someone put me out of my misery?

[Bob Phillips]

[vba]

With Worksheets("Report")
.Range("A1", .Range("B65536").End(xlUp)).Copy
End With
[/vba]

[RichardSchollar]

Hi Glaswegian

Just my thoughts but:

'Worksheets("Report").Range("A1", Range("B65536").End(xlUp)).Copy

...isn't a fully qualified reference - ie if the activesheet isn't Report then this will fail due to Range("B65536") pointing at the Activesheet instead of Report.

Likewise:

'Worksheets("Report").Range("A1").Select

this will fail if Report isn't active as you can't select a range which isn't on the active sheet.

Hope this helps!

Richard

[Glaswegian]

Thank you.


I need a drink....

[Dave]

I don't think you can copy a range from XL to Word but of course of been wrong many times before. Here's some example code that converts your "A" & "B" columns to strings which are then added together into one bigstring which is then inserted into the Word doc. HTH. Dave
[VBA]
Sub XLColtoWord()
Dim Wapp As Object, Astr As String, Bstr As String
Dim BigString As String, FileName As String
BigString = vbNullString
'change data to small strings...
'make big string from small strings
For cnt = 1 To 6
Astr = Sheets("sheet1").Range("A" & cnt) & " "
Bstr = Sheets("sheet1").Range("B" & cnt)
BigString = BigString + Astr + Bstr + Chr(13)
Next cnt
'insert BigString in Word doc
On Error GoTo Erfix
FileName = "C:\summary.doc"
Set Wapp = CreateObject("Word.Application")
Wapp.documents.Open FileName:=FileName, ReadOnly:=False
Wapp.activedocument.Select
With Wapp.activedocument
.Range(0, .Characters.Count).Delete 'clear doc
.content.insertafter BigString
End With
Wapp.Visible = True
'Wapp.activedocument.Close savechanges:=True
'Wapp.Quit
Set Wapp = Nothing
Exit Sub
Erfix:
Wapp.Quit
Set Wapp = Nothing
End Sub

[/VBA]