Input Box coding

[cheoke]

I need a little help figuring out how I can get a certain data range using an input box command. Here is the code I have so far

Function Getdatarange()
Getdatarange = InputBox("Enter type of data for export")
    Dim SS As Range
    Dim XFMR As Range
    Dim CT As Range
    Dim LC As Range
    Dim wordApp As Object
    Dim fNameAndPath As String
   Set SS = Worksheets("Sheet3").Range("A1:S16")
   Set XFMR = Worksheets("Sheet3").Range("B25:H36")
   Set CT = Worksheets("Sheet3").Range("M21:R24")
   Set LC = Worksheets("Sheet3").Range("M31:R34")
    fNameAndPath = "C:\Documents and Settings\Gerard\My Documents\BOM.doc"
    Set wordApp = CreateObject("Word.Application")
    wordApp.Documents.Open (fNameAndPath)
    wordApp.Visible = True
End Function

When I run the program now I can input a phrase, but nothing is returned. I would like to be able to input Safety Switch(SS) or Transformer(XFMR) into the body of the input box and have the returned range copied and pasted onto the new word file. Can this be accomplished? Thank You in advance

 

[Andrew Poulsom]

To paste into your document, try:

wordApp.Selection.Paste

 

[cheoke]

This is the new code I've entered

Function Getdatarange()
Getdatarange = InputBox("Enter type of data for export")
    Dim SS As Range
    Dim XFMR As Range
    Dim CT As Range
    Dim LC As Range
    Dim wordApp As Object
    Dim fNameAndPath As String
   Set SS = Worksheets("Sheet3").Range("A1:S16").Copy
   Set XFMR = Worksheets("Sheet3").Range("B25:H36").Copy
   Set CT = Worksheets("Sheet3").Range("M21:R24").Copy
   Set LC = Worksheets("Sheet3").Range("M31:R34").Copy
    fNameAndPath = "C:\Documents and Settings\Gerard\My Documents\BOM.doc"
    Set wordApp = CreateObject("Word.Application")
    wordApp.Documents.Open (fNameAndPath)
    wordApp.Visible = True
    wordApp.Selection.Paste
End Function

but I keep getting an error after I input what I want copied into the Input Box. Its telling me that an object is required. What are they referring to?

 

[Andrew Poulsom]

You can't do this:

Set SS = Worksheets("Sheet3").Range("A1:S16").Copy

It can be:

Set SS = Worksheets("Sheet3").Range("A1:S16")
SS.Copy

or:

Worksheets("Sheet3").Range("A1:S16").Copy

 

[cheoke]

Ok I was able to get the macro to copy from the excel sheet and paste to my document, the only problem is that its pasting only the data range I set LC to which is fine, but I need to be able to copy the data and paste the data in the other ranges. Can I use the input box function to be able to type in SS or XFMR and have the data in either of those ranges pasted?

 

[Andrew Poulsom]

Like this?

Select Case Getdatarange
    Case "SS"
        Worksheets("Sheet3").Range("A1:S16").Copy
    Case "XFMR"
        Worksheets("Sheet3").Range("B25:H36").Copy
End Select

 

[cheoke]

Yes, thank you that's what I was looking for.

 

[cheoke]

Ok another question has arisen. Right now my code is written so that a new word document is created when something is being pasted to it. How can I make it so that I can copy mutiple selections into the same word document? For example I already copied and pasted "SS" into this new word document but I decide I want to add "CT" after.

 

[Andrew Poulsom]

Once your word document is open it would be something like this:

Select Case Getdatarange
    Case "SS"
        Worksheets("Sheet3").Range("A1:S16").Copy
    Case "XFMR"
        Worksheets("Sheet3").Range("B25:H36").Copy
End Select
wordApp.Selection.Paste 
Worksheets("Sheet3").Range("M21:R24").Copy
wordApp.Selection.Paste

In Word pasting moves the Selection.

 

[cheoke]

Ok so right now this is the code I have from the start of the Select Case (bare with me please I greatly appreciate all the help so far)

    Select Case Datarange
    Case "SS"
        SS.Copy
    Case "XFMR"
        XFMR.Copy
    Case "CT"
        CT.Copy
    Case "LC"
        LC.Copy
    End Select
    wordApp.Selection.Paste
    yesno = MsgBox("Do you have more than one range you wish to place on the BOM Sheet?", vbYesNo)
    Select Case yesno
    Case vbYes
    Datarange = InputBox("Enter type of data for export")
    If Datarange = "SS" Then
    SS.Copy
    ElseIf Datarange = "CT" Then
    CT.Copy
    ElseIf Datarange = "XFMR" Then
    XFMR.Copy
    ElseIf Datarange = "LC" Then
    LC.Copy
    End If
    Case vbNo
    End Select
    wordApp.Selection.Paste
End Function

I'd like to create a loop so that after everytime a selection is pasted a message box appears like the one shown and asks if I would like to enter more data. How can I do this?

 

[cheoke]

I'm not familiar with the whole loop function