User needs to choose workbook to copy to

[sizemorem]

How can I change this routine to allow the user to choose the workbook and worksheet in which to copy items to.

Sub CopyItems()
   Dim intRowS As Integer, intRowT As Integer
   Dim strComprehend As String
   strComprehend = InputBox("KeyWord:", , "")
   If strComprehend = "" Then Exit Sub
   intRowS = 10
   intRowT = 10
   Do Until IsEmpty(Cells(intRowS, 1))
      If Cells(intRowS, 6) = strComprehend Then
         Rows(intRowS).Copy Worksheets("Sheet2").Rows(intRowT)
         intRowT = intRowT + 1
      End If
      intRowS = intRowS + 1
   Loop
   Worksheets("Sheet2").Columns.AutoFit
   Worksheets("Sheet2").Select
End Sub

 

[ajetrumpet]

I just want to say that this seems like a procedure that you got off the internet and now looking to manipulate it without even trying it yourself. I hope not, but if so you'll never learn anything about coding doing it this way...

 

[sizemorem]

It is a procedure that I copied from the cd that is sold on this website, and I have been trying for days to change it. Every change that I make does not work. I have 10 different study guides including John Walkenbach's Power Programming and VBA and Macros for Microsoft Excel by "Mr Excel" himself and have not found sufficient information. When I try to use the recorder to start and used advanced filter, I receive the message that the range must be recorded to the active sheet. I want to send it to an entirely different workbook with 31 different sheets that has a different name each month. Believe me, I am trying.

 

[ajetrumpet]

wouldn't it just be this:

      If Cells(intRowS, 6) = strComprehend Then
         Rows(intRowS).Copy Worksheets( _
            input box("Workbook Name").Rows(intRowT)
         intRowT = intRowT + 1
      End If

Of course you would have to have the workbook open to do this.
</pre>

 

[xenou]

Its not really such a simple task in my opinion (you don't really want the user typing in so much as that's bound to fall to human error sooner or later).

If possible, determine the destination programmatically (can the destination worksheet at least be determined by context? -- i.e, as a certain month that can be parsed from the source data?).

If not, I guess you could create a userform with dropdown for:
1) the workbooks to choose from
2) the worksheets to choose from in the chosen workbook

When designing a "database" in the loose sense as an organized store of data, its always easier to separate data storage and maintenance (in one or only a few files) and reporting of the data (as varied as the needs of your users).

 

[sizemorem]

Your suggestion is really more what I had in mind. I have one "master" workbook where the data is entered and 3 "child" workbook where the data is only of one type. The user has the master workbook open already, and I have a macro in place where the appropriate 'child' wb has been opened. I have the filter in place to determine the lines of data to copy. What I can't figure out is how to tell the program to copy it to the 'child' wb since the name changes with the month. The wbs are always named yyyymm_Control & yyyymm_sub.

Am I making any sense?

This is what I have so far:

Sub CopyToRMS()
'Choose RMS Items
    Dim wb As Workbook
    Dim ws As Worksheet
    
    wb = InputBox("Choose workbook:", "")
    ws = InputBox("Choose sheet:", "")
    
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="RMS"
    Selection.SpecialCells(xlCellTypeVisible).Copy Destination = Workbooks("wb").Sheets("ws")
     
End Sub

 

[xenou]

The input box method will return a string:

    wb = InputBox("Choose workbook:", "")
    ws = InputBox("Choose sheet:", "")

So the above variables are basically of the wrong type (also , notice that variables don't require quotes around them - only string literals). Instead:

[COLOR="blue"]Dim wbName as String
Dim wsName as String[/COLOR]
wbName = InputBox("Choose a workbook:","")
wsName = InputBox("Choose Sheet:","")
'~~
'~~
Selection.SpecialCells(xlCellTypeVisible).Copy _
    Destination = [COLOR="blue"]Workbooks(wbName).Worksheets(wsName)[/COLOR]

--------------------------------------------------------------------------------------------
Or to use object variables instead you can set a reference to the workbook and worksheet:

Dim wbName as String
Dim wsName as String
[COLOR="Blue"]Dim wb as Workbook
Dim ws as Worksheet[/COLOR]

wbName = InputBox("Choose a workbook:","")
wsName = InputBox("Choose Sheet:","")
[COLOR="blue"]Set wb = Workbooks(wbName)
Set ws = wb.Worksheets(wsName)[/COLOR]
'~~
'~~
Selection.SpecialCells(xlCellTypeVisible).Copy Destination = [COLOR="blue"]ws[/COLOR]

The Input box is more or less free form (as opposed to a drop down list) but perhaps we can take things one step at a time. Are we getting references to workbooks and worksheets going?

Also of use at some point more info on worksheets:
http://www.mrexcel.com/forum/showthread.php?t=253546&highlight=worksheets

And remember that the OPEN method takes a full path:
Workbooks.Open ("C:\MyFolder\MyBook.xls")
Workbooks that are already opened are referenced by name:
Workbooks("MyBook.xls").Activate


EDIT:
Duh, and to answer your question, if you know the workbook name you can parse out the one you need perhaps:

Dim strYear as String
dim strMonth as String
dim wbMasterName as String
dim wbSubName as String
strYear = "2009"
strMonth = "01"
wbMasterName = strYear & strMonth & "_Control"
wbSubName =  strYear & strMonth & "_sub"

 

[sizemorem]

There is so much to learn and I am learning by hit and miss. Mostly miss. I will print this and study your comments. I thank you very much for your time and consideration. Please accept my heartfelt thanks.
marcia

 

[xenou]

No problem. One day at a time - there is a lot to learn for sure. Keep on Excelling.