Glaswegian
07-08-2007, 02:18 PM
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?
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?