I have a another problem now. I have modified code from
http://www.mrexcel.com/tip014.shtml to make my code work. The problem is that i put a button on another separate sheet called global but when i hit the button that calls the following code it starts selecting the rows from the global sheet and not the 2950data sheet. So i figured i would try to put the button on the 2950data sheet and try it. When i hit the button there the code halts on the line
finalrow = Range("A100").End(xlUp).Row
so i cant figure out what i can do to make this work.
CODE
==========
Private Sub CommandButton1_Click()
' You must pick Microsoft Word 8.0 from Tools>References
' in the VB editor to execute Word commands.
' See VB Help topic "Controlling One Microsoft Office Application from Another"
' for more information.
' Originally published by www.MrExcel.com 2/28/1999
Dim appWD As Word.Application
Dim finalrow As Number
i = 1
' Create a new instance of Word & make it visible
Set appWD = CreateObject("Word.Application.9")
appWD.Visible = False
Worksheets("2950data").Select
'Find the last row with data in the database
finalrow = Range("A100").End(xlUp).Row
For i = 2 To finalrow
Worksheets("2950Data").Select
' Copy the descriptions into the template
Range("B" & i).Copy Destination:=Sheets("2950config").Range("A12")
'Hostname
Range("C" & i).Copy Destination:=Sheets("2950config").Range("A24")
Range("D" & i).Copy Destination:=Sheets("2950config").Range("A29")
Range("E" & i).Copy Destination:=Sheets("2950config").Range("A34")
Range("F" & i).Copy Destination:=Sheets("2950config").Range("A39")
Range("G" & i).Copy Destination:=Sheets("2950config").Range("A44")
Range("H" & i).Copy Destination:=Sheets("2950config").Range("A49")
Range("I" & i).Copy Destination:=Sheets("2950config").Range("A54")
Range("J" & i).Copy Destination:=Sheets("2950config").Range("A59")
Range("K" & i).Copy Destination:=Sheets("2950config").Range("A64")
Range("L" & i).Copy Destination:=Sheets("2950config").Range("A69")
Range("M" & i).Copy Destination:=Sheets("2950config").Range("A74")
Range("N" & i).Copy Destination:=Sheets("2950config").Range("A79")
Range("O" & i).Copy Destination:=Sheets("2950config").Range("A84")
Range("P" & i).Copy Destination:=Sheets("2950config").Range("A89")
Range("Q" & i).Copy Destination:=Sheets("2950config").Range("A94")
Range("R" & i).Copy Destination:=Sheets("2950config").Range("A99")
Range("S" & i).Copy Destination:=Sheets("2950config").Range("A104")
Range("T" & i).Copy Destination:=Sheets("2950config").Range("A109")
Range("U" & i).Copy Destination:=Sheets("2950config").Range("A114")
Range("V" & i).Copy Destination:=Sheets("2950config").Range("A119")
Range("W" & i).Copy Destination:=Sheets("2950config").Range("A124")
Range("X" & i).Copy Destination:=Sheets("2950config").Range("A129")
Range("Y" & i).Copy Destination:=Sheets("2950config").Range("A134")
Range("Z" & i).Copy Destination:=Sheets("2950config").Range("A139")
Range("AA" & i).Copy Destination:=Sheets("2950config").Range("A144")
Range("AB" & i).Copy Destination:=Sheets("2950config").Range("A149")
'port descriptions
Range("AC" & i).Copy Destination:=Sheets("2950config").Range("A14")
'enable password
Range("AD" & i).Copy Destination:=Sheets("2950config").Range("A165")
'SNMP RO
Range("AE" & i).Copy Destination:=Sheets("2950config").Range("A166")
'SNMP RW
Range("AF" & i).Copy Destination:=Sheets("2950config").Range("A16")
'clock timezone
Range("AG" & i).Copy Destination:=Sheets("2950config").Range("A17")
'clock summertime
Range("AH" & i).Copy Destination:=Sheets("2950config").Range("A155")
'vlan ip
Range("AI" & i).Copy Destination:=Sheets("2950config").Range("A162")
'default gw
Range("AJ" & i).Copy Destination:=Sheets("2950config").Range("A167")
'snmp location
Range("AK" & i).Copy Destination:=Sheets("2950config").Range("A169")
'chassis ID
Range("AL" & i).Copy Destination:=Sheets("2950config").Range("A168")
'contact
Range("AM" & i).Copy Destination:=Sheets("2950config").Range("A185")
Range("AM" & i).Copy Destination:=Sheets("2950config").Range("A188")
Range("AM" & i).Copy Destination:=Sheets("2950config").Range("A191")
Range("AM" & i).Copy Destination:=Sheets("2950config").Range("A194")
'line password
Range("AN" & i).Copy Destination:=Sheets("2950config").Range("A197")
'ntp server1
Range("AO" & i).Copy Destination:=Sheets("2950config").Range("A198")
'ntp server 2
Sheets("2950config").Select
'select other sheet
Range("A1:A250").Copy
' copy column A1 - A250
appWD.Documents.Add
' Tell Word to open new document
appWD.Selection.Paste
' paste the contents of the clipboard into the new document
appWD.ActiveDocument.SaveAs Filename:="Config" & i, FileFormat:=wdFormatText
' Close this new word document
appWD.ActiveDocument.Close
Next i
' Close the Word application
appWD.Quit
End Sub
http://www.mrexcel.com/tip014.shtml to make my code work. The problem is that i put a button on another separate sheet called global but when i hit the button that calls the following code it starts selecting the rows from the global sheet and not the 2950data sheet. So i figured i would try to put the button on the 2950data sheet and try it. When i hit the button there the code halts on the line
finalrow = Range("A100").End(xlUp).Row
so i cant figure out what i can do to make this work.
CODE
==========
Private Sub CommandButton1_Click()
' You must pick Microsoft Word 8.0 from Tools>References
' in the VB editor to execute Word commands.
' See VB Help topic "Controlling One Microsoft Office Application from Another"
' for more information.
' Originally published by www.MrExcel.com 2/28/1999
Dim appWD As Word.Application
Dim finalrow As Number
i = 1
' Create a new instance of Word & make it visible
Set appWD = CreateObject("Word.Application.9")
appWD.Visible = False
Worksheets("2950data").Select
'Find the last row with data in the database
finalrow = Range("A100").End(xlUp).Row
For i = 2 To finalrow
Worksheets("2950Data").Select
' Copy the descriptions into the template
Range("B" & i).Copy Destination:=Sheets("2950config").Range("A12")
'Hostname
Range("C" & i).Copy Destination:=Sheets("2950config").Range("A24")
Range("D" & i).Copy Destination:=Sheets("2950config").Range("A29")
Range("E" & i).Copy Destination:=Sheets("2950config").Range("A34")
Range("F" & i).Copy Destination:=Sheets("2950config").Range("A39")
Range("G" & i).Copy Destination:=Sheets("2950config").Range("A44")
Range("H" & i).Copy Destination:=Sheets("2950config").Range("A49")
Range("I" & i).Copy Destination:=Sheets("2950config").Range("A54")
Range("J" & i).Copy Destination:=Sheets("2950config").Range("A59")
Range("K" & i).Copy Destination:=Sheets("2950config").Range("A64")
Range("L" & i).Copy Destination:=Sheets("2950config").Range("A69")
Range("M" & i).Copy Destination:=Sheets("2950config").Range("A74")
Range("N" & i).Copy Destination:=Sheets("2950config").Range("A79")
Range("O" & i).Copy Destination:=Sheets("2950config").Range("A84")
Range("P" & i).Copy Destination:=Sheets("2950config").Range("A89")
Range("Q" & i).Copy Destination:=Sheets("2950config").Range("A94")
Range("R" & i).Copy Destination:=Sheets("2950config").Range("A99")
Range("S" & i).Copy Destination:=Sheets("2950config").Range("A104")
Range("T" & i).Copy Destination:=Sheets("2950config").Range("A109")
Range("U" & i).Copy Destination:=Sheets("2950config").Range("A114")
Range("V" & i).Copy Destination:=Sheets("2950config").Range("A119")
Range("W" & i).Copy Destination:=Sheets("2950config").Range("A124")
Range("X" & i).Copy Destination:=Sheets("2950config").Range("A129")
Range("Y" & i).Copy Destination:=Sheets("2950config").Range("A134")
Range("Z" & i).Copy Destination:=Sheets("2950config").Range("A139")
Range("AA" & i).Copy Destination:=Sheets("2950config").Range("A144")
Range("AB" & i).Copy Destination:=Sheets("2950config").Range("A149")
'port descriptions
Range("AC" & i).Copy Destination:=Sheets("2950config").Range("A14")
'enable password
Range("AD" & i).Copy Destination:=Sheets("2950config").Range("A165")
'SNMP RO
Range("AE" & i).Copy Destination:=Sheets("2950config").Range("A166")
'SNMP RW
Range("AF" & i).Copy Destination:=Sheets("2950config").Range("A16")
'clock timezone
Range("AG" & i).Copy Destination:=Sheets("2950config").Range("A17")
'clock summertime
Range("AH" & i).Copy Destination:=Sheets("2950config").Range("A155")
'vlan ip
Range("AI" & i).Copy Destination:=Sheets("2950config").Range("A162")
'default gw
Range("AJ" & i).Copy Destination:=Sheets("2950config").Range("A167")
'snmp location
Range("AK" & i).Copy Destination:=Sheets("2950config").Range("A169")
'chassis ID
Range("AL" & i).Copy Destination:=Sheets("2950config").Range("A168")
'contact
Range("AM" & i).Copy Destination:=Sheets("2950config").Range("A185")
Range("AM" & i).Copy Destination:=Sheets("2950config").Range("A188")
Range("AM" & i).Copy Destination:=Sheets("2950config").Range("A191")
Range("AM" & i).Copy Destination:=Sheets("2950config").Range("A194")
'line password
Range("AN" & i).Copy Destination:=Sheets("2950config").Range("A197")
'ntp server1
Range("AO" & i).Copy Destination:=Sheets("2950config").Range("A198")
'ntp server 2
Sheets("2950config").Select
'select other sheet
Range("A1:A250").Copy
' copy column A1 - A250
appWD.Documents.Add
' Tell Word to open new document
appWD.Selection.Paste
' paste the contents of the clipboard into the new document
appWD.ActiveDocument.SaveAs Filename:="Config" & i, FileFormat:=wdFormatText
' Close this new word document
appWD.ActiveDocument.Close
Next i
' Close the Word application
appWD.Quit
End Sub