Posts by petehenson

Re: paset to worksheet whose name is selected in a cell

I've hit upon another error now I've built the workbook. I'm using a vlookup table to populate the sheet name value in cell F2. Does Excel read the value of the cell as the formula within it rather than the displayed value? If so is there a modification I can make to the code to make it use the displayed value?

Re: paset to worksheet whose name is selected in a cell

Brillant, that's done it. Thanks

Re: paset to worksheet whose name is selected in a cell

I have found a bit of code on google to select the worksheet based on the value in cell F2, but now get an error. Could someone help me sort the code please, it's hopefully quite easy if you know what you're doing. The error message I get is 'code 9 - subscript out of range'

Sub Button1_Click()
strWsName = Sheet1.Range("F2")
Worksheets(strWsName).Select
    NextRow = Worksheets("F2").Range("A65536").End(xlUp).Row + 1
    Worksheets(strWsName).Cells(NextRow, 1).Resize(1, 2).Value = Array( _
        Worksheets("Sheet1").Range("C10").Value, _
        Worksheets("Sheet1").Range("E12").Value)
End Sub

Hi,

I'm trying to copy inputted data to a worksheet that the person selects from a drop-down list. I have a worksheet for each training session; people will select the session they want to book on and their details will be added to that worksheet.

The code I have so far is as follows:

[FONT=Arial Unicode MS][size=10][COLOR=#1e2463]Sub CopyThem()[/COLOR][/SIZE][/FONT]
[size=10][COLOR=#1e2463][FONT=Arial Unicode MS]   NextRow = Worksheets("F3").Range("A65536").end(xlup).Row +1[/FONT][/COLOR][/SIZE][size=10][COLOR=#1e2463][FONT=Arial Unicode MS]    [/FONT][/COLOR][/SIZE]
[size=10][COLOR=#1e2463][FONT=Arial Unicode MS]Worksheets("Sheet2").Cells(NextRow, 1).Resize(1, 2).value = Array( _[/FONT][/COLOR][/SIZE][size=10][COLOR=#1e2463][FONT=Arial Unicode MS]        Worksheets("Sheet1").Range("C10").value, _[/FONT][/COLOR][/SIZE][size=10][COLOR=#1e2463][FONT=Arial Unicode MS]        Worksheets("Sheet1").Range("E12").value)[/FONT][/COLOR][/SIZE][FONT=Arial Unicode MS][size=10][COLOR=#1e2463]End Sub[/COLOR][/SIZE][/FONT]

Instead of sheet2, I need the formula to use the value that the person has selected in sheet1 cell C1.

Re: Value Of Last Date In Range

Thanks Bob - so simple when you know what you're doing!!

Out of interest, what does the 99^99 bit in the formula actually mean?

Cheers,
Pete

Re: Value Of Last Date In Range

Thanks Dave.

I'd given the wrong range in my example - it should have been a row not a column, e.g B2:G2.
I've tried changing the formula as follows:
=INDEX($B$2:$G$2,MATCH(-E9+306,$B$2:$G$2,-1),1)

It works if there is only one date entered, but if I add any more dates it gives an Invalid Cell Reference error.

I am creating a simple record of when a list of people have had meetings. They are supposed to have a meeting every 6 weeks. I want to automatically highlight anyone who has not had a meeting for more than six weeks.

I was thinking of doing that by subtracting the last meeting date entered in a row from today's date. Could you advise me how to get the value of the last date entered in a row, please? This would be the value of the last non-blank cell in e.g. B2:B20.

Thanks

Re: Copy & Paste Data Based On Current Month

It pasted this month's data under July, but I got it to work by changing "tmp + 5" to "tmp + 4". How does that work? (I don't know vba). Could you explain it to me please. How does it work out where to paste the data?

Re: Use Macro To Copy And Paste Data Based On Current Month

Thanks for trying, I see what you are saying but unfortunately I have to have a single 'compliance' column rather than one for every month, so can't use a pivot table.

I've thought of a better explanation of what I need... I need a macro that does the following when I click a button:
1. Find the column heading from e.g. H1 to H12 that has the same value as the value in D75.
2. Copy the values from D72-D74 and paste them underneath that heading value.

In this example,
H1 to H12 are the months of the year.
D75 is a user defined month.
D72 is the number of Yes values
D73 is the number of No values
D74 is the number of partial values.

If anyone could just write me the above macro that would be brilliant. The result will be that a person will update the action plan each month, then click on a button to add the latest month to the chart.

Re: Use Macro To Copy And Paste Data Based On Current Month

Sorry, here's the attachment...

Re: Use Macro To Copy And Paste Data Based On Current Month

See attached. The table I need to populate with data is shaded in blue. The data topopulate a given month is shaded in yellow. The way it works is that a person will update the 'Compliance' column as they make progress, possibly once a month. I then need hat data to be transfered into the relevant month of the blue table, to make a chart with it.

I have created an action plan in Excel, with a column containing the values Yes, No and Partial to show prgress against the actions.

I have used CountIF (in cells D72 to D74) to count the number of each Yes, No and Partial. I want to be able to put these figures in a chart each month. I am using columns F to Q for the chart data. the headings of the chart columns are month values, e.g, F1 is "6" (for June).

I have used Month(Now()) to generate the current month value in cell B1.

I now need a macro that will check the value in B1 against the chart table headings in F1 to Q1 and if it finds a match, will copy the values D72 to D74 into the chart table below the relevant heading.

Please help!

I'm trying to use a button and macro to automatically email part of a worksheet to the recipients in a list. I've managed to get it to send the whole sheet, using information from the help forum. However I need to only send a range, which would be Column A to Column E (so that my command button isn't sent). Please help. My macro is below. (Sorry if this is a bit basic, but I don't know VB at all, so it's been mostly trial and error to get this far).

Sub Email()
    Dim wb As Workbook
    Dim strdate As String
    strdate = Format(Now, "dd-mm-yy h-mm-ss")
    Application.ScreenUpdating = False
    ActiveSheet.Copy
    Set wb = ActiveWorkbook
    With wb
        .SaveAs "H:\Minutes of Meeting\Part of " & ThisWorkbook.Name _
              & " " & strdate & ".xls"
    Dim MyArr As Variant
    MyArr = Sheets("minutes").Range("H2:H100")
    .SendMail MyArr, "Notes of the [Add Name] Meeting "
 


 


        .Close False
    End With
    Application.ScreenUpdating = True
End Sub

Morning. Is there a way to format a whole workbook?

Firstly I need to print out the workbook in draft format. However, when I select 'print', 'entire workbook', 'properties', 'draft' and print the document, one of the pages comes out in best quality.

Secondly, I need to add page numbers to the footer, but I can only find a way to add them to a worksheet at a time, which is obviously very time consuming over 56 workbooks.

I can get round both problems by formatting one worksheet at a time, but I would hope there is a much quicker method.

Grateful for any advice,
Pete

Re: Conditionally Unhide Columns

I wouldn't have got that in a million years! I really must persuade my boss to buy visual basic (I've had a self-teach book for a few years, but no VB to use it with - Visual Basic 6.0 Professional Step By Step).

Regarding the second problem, I've managed to sort it - for some resaon when I updated the source it added it as another source rather than updating the original. I managed to fix it by going into 'edit links' and editing the original to the correct source.

Thanks again,
Pete

Re: Conditionally Unhide Columns

I'm nowhere near guru status yet I'm afraid. I have a couple more problems that have come to light as I'm working.
Firstly, when I run the macro - see code below - it sometimes hides the columns in sheet2 as well as sheet one. Sheet two has a graph on it so the graph gets concertina-d. The sheets are not named 'sheet1', 'sheet2' etc. - I have renamed them depending on what the data relates to. Could that be the problem? Do I need to rename all the first sheets as sheet1? Or is there a cleverer way?

Sub ShowHideCols()
Dim wSheet As Worksheet
Dim strMonth As String
Dim rRange As Range
Dim rCell As Range
Dim lMonth As Long


strMonth = Format(Date, "MMM")




    With Sheet1
      For Each rCell In Range("G5:R5")
      
         Select Case UCase(rCell)
            Case "APR"
                lMonth = 1
            Case "MAY"
                lMonth = 2
            Case "JUN"
                lMonth = 3
            Case "JUL"
                lMonth = 4
            Case "AUG"
                lMonth = 5
            Case "SEP"
                lMonth = 6
            Case "OCT"
                lMonth = 7
            Case "NOV"
                lMonth = 8
            Case "DEC"
                lMonth = 9
            Case "JAN"
                lMonth = 10
            Case "FEB"
                lMonth = 11
            Case "MAR"
                lMonth = 12
            Case Else
                lMonth = 13
         End Select
                rCell.EntireColumn.Hidden = Not (Month(Date) >= lMonth + 4)
      Next rCell
    End With
 
End Sub

Secondly, all my workbooks get their data from a single source, so that I only have to update that one workbook each month rather than all 56. However, each time I open a workbook it cannot locate the source data. I locate the source again, save the workbook and close it, but when I re-open it, it can't find the source data again. Can you think why that is?

Re: Conditionally Unhide Columns

Managed to find the answer elsewhere on the forum and sorted it using "With Sheet1... End With". :thanx:

Re: Conditionally Unhide Columns

Just come across another problem - some of the spreadsheets only have the first page with data and the rest of the pages are graphs. I only want to run the macro on the first worksheet, otherwise it squashes the graphs. How do I do that, please?

Re: Conditionally Unhide Columns

Don't worry - I fixed it. I don't know how it works, but by putting rCell.EntireColumn.Hidden = Not (Month(Date) >= lMonth+3) it's worked...

Thanks for doing all this, it'll save me loads of work once I've added the code to all the spreadsheets.

Re: Conditionally Unhide Columns

To explain, the worksheets are performance data for the financial year. Each month we have to produce reports for the teams based on their activity to date each month, so for example team A's data might be April - 23, May - 27, June - 33, July - 21 etc. As the team has not done anything yet in November, December, January, February or March of this financial year, I want to hide those columns.

At the moment, the months October 05 to March 06 have been manually hidden and so this month I will have to go through all 56 spreadsheets manually unhiding October, then next month do the same for November, etc. etc.

I thought it might be possible to automate the hiding and unhiding of columns, which is why I posted my question. And we're so close to a solution!

Would a possible solution be to have a cell, e.g. AA1, on the master spreadsheet, in which I type the last month I want to display? The VB code could then look at that cell and hide all the month columns following that month? If so, how do I do that?