variable problem

C

chad1222

Board Regular
Joined
Jul 14, 2003
Messages
133
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Not that responding to this guarantees a satisfactory response to your problem, but you might want to format your code for readability. Click the CODE button at the top of the posting form, then paste your code, then click the CODe button again.
 
Upvote 0
Sorry here is the code now


Rich (BB 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
    
   ' 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
        **BUNCH OF RANGES** EDITED FOR CLARITY
                   
        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


Again, say i have 3 sheets, one has data, one has the template, and one is a command page called global. I insert a button with the code above embedded onto the global page. The code runs fine except for the fact that apparently the
Rich (BB code): 
Worksheets("2950data").Select
piece of code does not work because it selects all the rows from that global page and not the Data page. But if i move the button and its code to the Data page and try to run it it halts on the code here and gives me a Variable not defined error. Why would it work on one sheet and not another?

Rich (BB code): 
finalrow = Range("A100").End(xlUp).Row
[/code]
 
Upvote 0
Much easier to read, isn't it?

Don't know why you are getting strange results, but in general, one doesn't need to select/activate worksheets / ranges to make code work.

As far as the 'variable not defined' error goes, you probably have 'Option Explicit' in one module and not the other.

Try the simplified version -- without any 'select's that is below. It compiles OK but is otherwise untested. Note the periods preceeding the various range references. For more on how to remove select/activates produced by XL's macro recorder, see the 'Beyond the Recorder' page of my web site.

Code: 
Option Explicit

Private Sub CommandButton1_Click()
    Dim appWD As Word.Application, i As Long
    
    Set appWD = New Word.Application
    appWD.Visible = False

    With Worksheets("2950data")
    For i = 2 To .Range("A100").End(xlUp).Row
        .Range("B" & i).Copy Destination:=Sheets("2950config").Range("A12")
        'Hostname
        '**BUNCH OF RANGES** EDITED FOR CLARITY
                    
        With Sheets("2950config")
        .Range("A1:A250").Copy
        appWD.Documents.Add
        appWD.Selection.Paste
        appWD.ActiveDocument.SaveAs Filename:="Config" & i, FileFormat:=wdFormatText
        appWD.ActiveDocument.Close
            End With
        Next i
        End With
    appWD.Quit
    End Sub
 
Upvote 0
Tushar thanks so much for your help. It now does what i want it to. The only problem is that now it is not putting the right data in some of the cells
correctly. Some cells are being put in fine but others are coming up with #ref in them. Apparently after it copies and pastes the value into the destination excel increments the formulas that it is pasting instead of just pasteing the formula. Is there a way to make this code work better for my application.

Code: 
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")

btw this is an example of the cell from the copy range in C2
C2
Code: 
="description" &" " &IDF1!B3& " "&IDF1!C3& " " &IDF1!D3
D2
Code: 
="description" &" " &IDF1!B4& " "&IDF1!C4& " " &IDF1!D4
E2
Code: 
="description" &" " &IDF1!B5& " "&IDF1!C5& " " &IDF1!D5
 
Upvote 0
For one you are using Range().Copy rather than .Range().Copy; that will almost inevitably cause errors. Also, if the copied cells contain formulas, you are copying the formula, not the current value. A safer, and potentially quicker, way would be
Code: 
Sheets("2950config").Range("A24").Value=.Range("C" & i).Value
Sheets("2950config").Range("A29").Value=.Range("D" & i).Value
'etc.
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Question
Information overwriting when transferring
Replies
7
Views
308
UTB
U
U
  • Solved
Copy/Paste Rows on select criteria
Replies
4
Views
941
UTB
U
C
  • Question
How to get my macro to stop at the last recognized line
Replies
6
Views
293
Coco1
C
H
  • Question
VBA Code to Copy a defined range from a defined list of Worksheets and paste into another sheet
Replies
3
Views
321
Georgiboy
Georgiboy
E
  • Question
Combining named worksheets with Macro
Replies
4
Views
529
Exl_in_Training
E

Forum statistics

Threads
1,216,028
Messages
6,128,392
Members
449,445
Latest member
JJFabEngineering

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top