Error with macro-it worked now it doesn't

I have a macro that when you click a button the cells that are gray will turn
white and the text that is blue will turn white, then I copy the cells and
paste them into word.

The cells can turn white if I delete the copy and paste code, but it will
not work with this code. Can someone help?



Private Sub CommandButton1_Click()
Worksheets("Report").Unprotect
Dim rng As Range, c As Range
Set rng = Selection
For Each c In rng
If c.Interior.ColorIndex = 15 Then
c.Interior.ColorIndex = 2
End If
Next c
For Each c In rng
If c.Font.ColorIndex = 5 Then
c.Font.ColorIndex = 2
End If
Next c

Range("A1:J9769").Select
Selection.Copy
Dim WDApp As Object
Dim WDDoc As Object
Dim myDocName As String

myDocName = "Survey Report.doc"

'Open Word and add a new document
Set WDApp = CreateObject("Word.Application")
WDApp.Visible = True
WDApp.Activate
Set WDDoc = WDApp.Documents.Add

WDDoc.Range.Paste
With WDDoc.Tables(1)
WDDoc.Range.Tables(1).Rows.Alignment = wdAlignRowCenter
End With
With WDDoc.PageSetup
.LineNumbering.Active = False
.Orientation = wdOrientPortrait
.TopMargin = InchesToPoints(0.5)
.BottomMargin = InchesToPoints(0.5)
.LeftMargin = InchesToPoints(0.75)
.RightMargin = InchesToPoints(0.75)
.Gutter = InchesToPoints(0)
.HeaderDistance = InchesToPoints(0.5)
.FooterDistance = InchesToPoints(0.5)
.PageWidth = InchesToPoints(8.5)
.PageHeight = InchesToPoints(11)

End With

Worksheets("Report").Protect
ws_exit:
Application.EnableEvents = True
End Sub

Thank you,

Daniel Young

Your code is working against the current selection on that activesheet. Are you
sure you have the correct range selected when you click the button.

And what do you mean "will not work"--do you get an error message???

Daniel R. Young wrote:
>
> I have a macro that when you click a button the cells that are gray will turn
> white and the text that is blue will turn white, then I copy the cells and
> paste them into word.
>
> The cells can turn white if I delete the copy and paste code, but it will
> not work with this code. Can someone help?
>
>
>
> Private Sub CommandButton1_Click()
> Worksheets("Report").Unprotect
> Dim rng As Range, c As Range
> Set rng = Selection
> For Each c In rng
> If c.Interior.ColorIndex = 15 Then
> c.Interior.ColorIndex = 2
> End If
> Next c
> For Each c In rng
> If c.Font.ColorIndex = 5 Then
> c.Font.ColorIndex = 2
> End If
> Next c
>
> Range("A1:J9769").Select
> Selection.Copy
> Dim WDApp As Object
> Dim WDDoc As Object
> Dim myDocName As String
>
> myDocName = "Survey Report.doc"
>
> 'Open Word and add a new document
> Set WDApp = CreateObject("Word.Application")
> WDApp.Visible = True
> WDApp.Activate
> Set WDDoc = WDApp.Documents.Add
>
> WDDoc.Range.Paste
> With WDDoc.Tables(1)
> WDDoc.Range.Tables(1).Rows.Alignment = wdAlignRowCenter
> End With
> With WDDoc.PageSetup
> .LineNumbering.Active = False
> .Orientation = wdOrientPortrait
> .TopMargin = InchesToPoints(0.5)
> .BottomMargin = InchesToPoints(0.5)
> .LeftMargin = InchesToPoints(0.75)
> .RightMargin = InchesToPoints(0.75)
> .Gutter = InchesToPoints(0)
> .HeaderDistance = InchesToPoints(0.5)
> .FooterDistance = InchesToPoints(0.5)
> .PageWidth = InchesToPoints(8.5)
> .PageHeight = InchesToPoints(11)
>
> End With
>
> Worksheets("Report").Protect
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
> Thank you,
>
> Daniel Young

--

Dave Peterson