Cannot copy a range....

[Glaswegian]

Hi

Basically, I'm going demented....:banghead:

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]

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

[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

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