Macros & Input Boxes

[Champers]

Hi All,

I am trying to create an input box or some sort of mechanism so that I do not have to create 24 different Macro's.

I have created a macro that picks up the word report out of a column in one sheet and then copy's and pastes that row in to another sheet.


Sub Reportingtest()
Application.ScreenUpdating = False
With ActiveSheet

Range(Selection, Cells(ActiveCell.Row, 1)).Copy
Sheets("Jan (2)").Select
Range("A6:A150").Select

Do While ActiveCell > 0

lineno = lineno + 1
ActiveCell.Offset(1, 0).Select
Loop

ActiveCell.Offset(-1, 0).Copy
ActiveCell.PasteSpecial

With ActiveWorkbook.Sheets("Jan (2)")

For Each cll In Intersect(.UsedRange, .Columns("K"))

If InStr(UCase(cll.Value), "REPORT") > 0 Then

.Cells(cll.Row, "A").Resize(, 8).Copy ActiveWorkbook.Sheets("Jan (3)").Cells(Rows.Count, 1).End(xlUp).Offset(1)
End If
Next cll
End With 'activesheet

Do While ActiveCell.Offset(1, 0) > 0

With ActiveCell

.Offset(1, 1).Copy
.Offset(0, 1).PasteSpecial
.Offset(1, 1).ClearContents
.Offset(1, -11).Select
Selection.EntireRow.Delete
Selection.EntireRow.Insert
End With
Loop

Range("A2:A3").Value = ""
End With

End Sub


Therefore the information that I need to change is:

E.g. 12 for Jan -Dec report and another 12 for Jan (2) - Dec (2)

I need to change the red high lighted information for the macro to work.

I am not sure how to go about this. I have tried recording a macro recording information in cell A3 and A4 which pick up the sheet name and then the column which report is in however it doesn't seem to like the copy and past function of it all.

I have tried using the following code:

Sheetvalue = Application.InputBox
Then Sheets (sheetvalue).select

The sheet names that I need to pull the row information from are:

Jan, Feb , March, April, May, June, July, August, September, October, November, December

And

Jan (2), Feb (2), March (2), April (2), May (2), June (2), July (2), August (2), September (2), October (2), November (2), December (2).

Columns I & K pick up the Report which tells the macro to copy the entire row and paste it into - e.g. Jan (3).



Any ideas?:banghead:

[GTO]

Hi Champers,

If you have a bunch of private info in your wb, I realize it's a bit of work - but I think it would be a good idea for you , to supply (attach) an example wb with your current code and some similarly fashioned fake data.

In short - as you can imagine, while one can tell the code is logical, it's hard to see what exactly is going on.

I am sure we can come up with an inputbox, or maybe a userform, where the user just picks a sheet and/or range to copy.

Mark

[Champers]

Hi Mark,

I have attached an excel workbook which contains the basic info. It contains both the Jan and Jan (2) workbooks which should feed Jan (3) when report is selected in columns K and I.

Thanks for your help with this.


Kind Regards

Champers

[p45cal]

Where is this file?

[Champers]

Sorry guys, didn't realise it had to be below 1mb.

The file should appear now.

I have just provided jan -jun rather than jan -dec.


Thanks

Champers

[p45cal]

could you also post it as a pre XL2007 type?

[Champers]

Version 97-03

[mikerickson]

You say that you need to avoid 24 different macros, I see 576 FromSheet/ToSheet combinations (24^2). Once the user has chosen a FromSheet, is the ToSheet automaticaly determined?

In any case this UDF might help.
Function uiChooseSheet() As Worksheet
Dim uiChoice As Range
On Error Resume Next
Set uiChooseSheet = Application.InputBox("Use the mouse to select a sheet", Type:=8).Parent
On Error GoTo 0
End Function
which can be used like
Sub test()
Dim chosenSheet As Worksheet

Set chosenSheet = uiChooseSheet

If chosenSheet Is Nothing Then
MsgBox "Cancel Pressed"
Else
MsgBox "The user choose sheet " & chosenSheet.Name
End If
End Sub

[Champers]

Hi,

The from sheet will always be Jan or Jan (2) and it will always be copied to Jan (3) depending on whether they select the report option on Jan or Jan(2).

The Macro above when selected then finds anything with report in Jan or Jan (2), selects the information for the row its on and then dumps it in Jan (3).

The jan (3) sheet is basically a the important extracts taken from jan and Jan (2) so that I do not have to go through each row of information in each workbook.

I have about 8 master workbooks to go through and I could do the Filter on each then copy and paste, but I thought this may be an easier option.

I am not sure I quite understand the option you have provided above.