Sleeper: Logging of search results

[everytime]

hi there , i have a log spreadsheet which keeps track of the searches that i make using the form .. the only thing is i cant make it add to the next row with a new search .. 1 more thing how do i get only the current year to be displayed in the form ..

here is the file so as to get a better picture .. i have remove other sheets so as the file is smaller ..

*unlocked and made it smaller*

[johnske]

Your zip file is protected. To get the next row use

Range("C65536").End(xlUp).Offset(1, 0)

[everytime]

opps sry .. the pass for the project is "soul"

[johnske]

Hi everytime,

At the bottom of your form1 code, replace the procedure "logging" with this

Sub logging()
DoesLogSheetExist
Logheading
With Sheets("Log").Range("C65536").End(xlUp)
.Offset(1, 0) = nowtiming.Caption
.Offset(1, 1) = sheeting.Text
.Offset(1, 2) = pnl.Text
.Offset(1, 3) = pf.Text
.Offset(1, 4) = Product.Text
.Offset(1, 5) = ytd.Text
.Offset(1, 6) = mtd.Text
.Offset(1, 7) = mbudget.Text
.Offset(1, 8) = fullyear.Text
End With
End Sub

this will give the search results on the next empty row.

I'm not sure what you mean about the current year (whereabouts on the form?)


Your userform code can be greatly shortened, this makes it easier to read and debug. The first and most obvious thing to do is to get rid of all the 'selecting' as this only slows things down...

shorten all parts similar to this

Range("D4").Select
ActiveCell.FormulaR1C1 = "YTD A2005 (FC '000)"

to

Range("D4").Value = "YTD A2005 (FC '000)

shorten all parts similar to this

Columns("AP:AV").Select
Selection.EntireColumn.Hidden = True

to

Columns("AP:AV").EntireColumn.Hidden = True

shorten all parts similar to this

If mbudget.Text = "Jan 2005" Then
Columns("AX:BH").Select
Selection.EntireColumn.Hidden = True
Columns("AK:AV").Select
Selection.EntireColumn.Hidden = True
ElseIf mbudget.Text = "Feb 2005" Then
Columns("AW:AW").Select
Selection.EntireColumn.Hidden = True
Columns("AY:BH").Select
Selection.EntireColumn.Hidden = True
Columns("AK:AV").Select
Selection.EntireColumn.Hidden = True
(etc)

to

Columns("AK:BH").EntireColumn.Hidden = True
If mbudget.Text = "Jan 2005" Then
Columns(AW:AW").EntireColumn.Hidden = False
ElseIf mbudget.Text = "Feb 2005" Then
Columns(AX:AX").EntireColumn.Hidden = False

Then, directly after this above statement you have another lengthy statement that be similarly changed to

Columns("AK:BH").EntireColumn.Hidden = True
If mbudget.Text = "Jan 2005 FC" Then
Columns("AK:AK").EntireColumn.Hidden = False
ElseIf mbudget.Text = "Feb 2005 FC" Then
Columns("AL:AL").EntireColumn.Hidden = False
(etc)

HTH,
John

[mdmackillop]

For your hidden column code, you could also use Case and an array with a function to trim your code

eg

Option Compare Text

Sub TestHide()
Dim List
Select Case InputBox("Date")
    Case "Jan 2004"
        List = Array("D:E", "G", "Y:AJ", "H:S", "BK:BV")
    Case "Feb 2004"
        List = Array("BY:BY", "CA:CJ")
End Select
DoHide List
End Sub

Sub DoHide(List)
    For Each Cols In List
        Columns(Cols).EntireColumn.Hidden = True
    Next
End Sub

[everytime]

hi there thanks for all the replies .. it helped alot ..

may i ask just a few more questions that just occured to me ...

can i add the new rows above instead of below the old ones ...

is there a way to copy all/selected few the sheets from an open workbook to another one ?

*sorry for my poor english i think this would better explain my question
for example i open i excel file .. and wanted to copy all / part of another excel file's spreadsheets over to the existing opened one ..

[royUK]

Cross post

http://www.ozgrid.com/forum/showthre...hlight=logging

[johnske]

Hi everytime,

No-one here begrudges a poster cross-posting in other forums when they are in urgent need of an answer.

However common courtesy dictates that all parties involved always be informed/aware of the cross posting so that volunteers working to help you with your problem can first check that they're not wasting their time by possibly duplicating something that's already been done elsewhere.

This can be quite easily done by taking a little time to write something along these lines...

"Sorry for Cross posting, but I know a lot of you dont always get the time to look at other forums . . . this has already been posted here > (link to other forum) but has not yet been answered. My question is this....(describe problem)".

John