Champers
06-26-2009, 03:46 AM
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:
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: