Excel Macro and Transposing Data

Hello,

Is this code working as expected?

No, it's not working as expected. I'm getting data for other rows mixed in. It was not doing that before. My entire macro is:

Sub ControlWord()
Dim appWD As Word.Application
Set appWD = CreateObject("Word.Application.8")
appWD.Visible = True

Sheets("Data").Select
For i = 2 To Range("A65536").End(xlUp).Row
Sheets("Data").Select
Range("A" & i).Copy Destination:=Sheets("Template").Range("b6")
Range("B" & i).Copy Destination:=Sheets("Template").Range("b7")
Range("C" & i).Copy Destination:=Sheets("Template").Range("b8")
Range("D" & i).Copy Destination:=Sheets("Template").Range("b10")
Range("E" & i).Copy Destination:=Sheets("Template").Range("b11")
Range("F" & i).Copy Destination:=Sheets("Template").Range("b12")
Range("G" & i).Copy Destination:=Sheets("Template").Range("b5")

FIRST_CELL = Range("H" & i).Address
SECOND_CELL = Range("A" & i).Offset(0, 6).End(xlToRight).Address
Range(FIRST_CELL, SECOND_CELL).Copy

Sheets("Template").Select
Range("B20").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True

Range("A1:B34").Copy
appWD.Documents.Add
appWD.Selection.Paste

With appWD.ActiveDocument.PageSetup
.LeftMargin = Application.InchesToPoints(1)
.RightMargin = Application.InchesToPoints(1.5)
.TopMargin = Application.InchesToPoints(0.3)
.BottomMargin = Application.InchesToPoints(0.3)
End With
' Save the new document with a sequential file name
appWD.ActiveDocument.SaveAs Filename:="C:\temp\misc\Monitor Alert Site " & Range("B5") & " Report " & i
appWD.ActiveDocument.Close
Next i
' Close the Word application
appWD.Quit
End Sub

Hello,

Is it because you are not clearing COlumn B?

If so, before the Next i at the end of the macro you should add



Has this solved it?

It worked! Thank you very much!!!!!!!!!!

onlyadrafter said:

Hello,

Is it because you are not clearing COlumn B?

If so, before the Next i at the end of the macro you should add

Code:

Sheets("Template").Columns(2).ClearContents

Has this solved it?

Click to expand...