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

Error with macro-it worked now it doesn't
 

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

Error with macro-it worked now it doesn't
 

I believe that the range is correct. I tried to do the whole sheet. It will
work when I don't know have the latter part to the code. However, when I
placed the second portion of the code in, the cells will not turn white. It
is strange because I do not get an error. It just won't work.

Thank you,
Dan

"Dave Peterson" wrote:

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

Error with macro-it worked now it doesn't
 

I'd try stepping through the code to see what happens.

Click anywhere in the code.
Hit F8 to step through each line.

Maybe something will become apparent????

(make sure you're on the correct worksheet and you selected the correct range
first.)

Daniel R. Young wrote:

I believe that the range is correct. I tried to do the whole sheet. It will
work when I don't know have the latter part to the code. However, when I
placed the second portion of the code in, the cells will not turn white. It
is strange because I do not get an error. It just won't work.

Thank you,
Dan

"Dave Peterson" wrote:

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


--

Dave Peterson