Posts by daren.beaney

    Hi,I'm having trouble with the code below, it moves through the first worksheet without any trouble but it doesn't move to the next ws after executing the Next ws line. Any ideas on what could be going wrong or am i missing something?

    For Each ws In WorksheetsSelect Case UCase(ws.Name)Case "Index", "Master", "Data", "Summary", "Charts"'Do nothingCase Else        ActiveSheet.Range("A1").Select                    Do While Not IsEmpty(ActiveCell)                        ActiveCell.Offset(0, 1).Select                    Loop                    ActiveCell.Formula = "=SUBTOTAL(103, F2:F12000)"                    ActiveCell.Offset(0, 1).Formula = "=COUNTIF(G:G,""Met"")"                    ActiveSheet.Range("A1").Select                    End SelectNext ws

    Thanks, it's driving me mad!!

    Re: Finding a value and updating the monthly figures from another spreadsheet

    Sorry about that, had problems trying to get the file size low enough. I'm specifically trying to get the results for each location. The problem is that the data that is in the APR Call Wrap spreadsheet changes each month so the order of calls changes. When you say use a lookup, can provide an example of how you think this may work?Thanks

    Hi,I'm struggling with trying to make a complex and timely issue easier. Basically i have 2 spreadsheets, book 1 = APR Call Wraps Master.xls and book 2 = Overall Comparison Summary.xls. Both spreadsheets have the same sheet names however book 1 is updated monthly from a report and i need book 2 to be updated with the each sheet results. I've included examples of the 2 workbooks that contain the layout as I currently have them. I need help with being able to go to the starting sheet first which in the attached book 1 the example is London, and then find the first value in column S6, use that as the search criteria, copy the value in T6 and then find the same (S6) value in the same sheet name in book 2 and then paste the value (T6) in the first blank cell.This would need to then repeat all the way through column S in book 1 until the last cell with a value has been updated. I will then need to go through all sheets in both books and carry out the same task.Hope that makes sense!


    I'm struggling with an issue I have deleting cells with specific criteria. The criteria is "Payslips", "Salary", "No pay / reduced pay" which may or may not be listed in column "S". Column "S" contains unique values.
    If a cell within column "S" contains one of the above criteria, I need it to then delete that cell and offset(0'1)

    I've tried various options but majority of what's out there deletes the entire row.



    I'm having a problem with the code below when only 1 row of data exists in a worksheet. Basically the code runs through all worksheets and needs to find the unique values which it does if more than 1 row of data is found. D1 is the heading row.

    LastRow = .Cells(Rows.Count, "D").End(xlUp).Row
    Range("D2:D" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("S3"), Unique:=True

    Re: Use a Form to select a defined month and sort the data accordingly.


    Month to display: the final month i.e. the start of the next year is not a main factor it be based upon the current year only. It was inserted to compare Jan against December. The reason for using a control box to list the months is so that you can summarise a particular month on request.

    Do we sort using month only: it will need to sort based upon the month only value in the cell. Each year we will update the cells with the current year.


    I'm having problems with trying to figure out how i can incorporate a form on the attached spreadsheet to show a combobox with a list of values found in the range i've called Month (B15:N15). I want a user to be able to select the relevant month in the combobox and click OK, on executing I want the data from A16:N24 to be sorted descending based on the selected month value. I then want the selected month values (first 5 rows) and the relevant categories (column A) to be copied and populated in the "Top 5" cells.

    Any help would be appreciated.


    Re: Using paste special when using Copy Destination


    I sorted it, see below:

    For Each ws In Worksheets
    Select Case UCase(ws.Name)
    Case "Index", "Data", "Summary", "Charts"
    Case Else
    Sheets("Summary").Range("B" & Rows.Count).End(xlUp).Offset(2, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    x = x + 1
    End Select
    Next ws

    Thanks for your help!


    I'm creating new worksheets using VBA and I need to populate a summary page. I'm using the following code which copies the content populated in the newly created worksheet to the summary sheet, however the content contains formulas and I need to only paste the values and the cell formatting.

    [FONT=Arial][size=10][COLOR=#17365d]For Each ws In Worksheets[/COLOR][/SIZE][/FONT]
    [FONT=Arial][size=10][COLOR=#17365d]Select Case UCase(ws.Name)[/COLOR][/SIZE][/FONT]
    [FONT=Arial][size=10][COLOR=#17365d]Case "Index", "Data", "Summary", "Charts"[/COLOR][/SIZE][/FONT]
    [FONT=Arial][size=10][COLOR=#17365d]'Do nothing[/COLOR][/SIZE][/FONT]
    [FONT=Arial][size=10][COLOR=#17365d]Case Else[/COLOR][/SIZE][/FONT]
    [FONT=Arial][size=10][COLOR=#17365d]ws.Range("Z6:AB11").Copy [/COLOR][/SIZE][/FONT][FONT=Arial][size=10][COLOR=#17365d]Destination:=Sheets("Summary").Range("B65536").End(xlUp).Offset(2, 0)[/COLOR][/SIZE][/FONT]
    [FONT=Arial][size=10][COLOR=#17365d]x = x + 1 [/COLOR][/SIZE][/FONT]
    [FONT=Arial][size=10][COLOR=#17365d]End Select[/COLOR][/SIZE][/FONT]
    [FONT=Arial][size=10][COLOR=#17365d]Next ws[/COLOR][/SIZE][/FONT]


    All i see once the content has been pasted is #REF!.


    Re: Use defined criteria in worksheet to find rows in another worksheet & add new wks

    Hi, The idea is that we need to report to different business units which are structured by a defined list of cost centres. hence having a sheet that contains the list that relates to each business unit. The data is extracted as a large list and we need to be able to split the content as per the criteria in the 4 columns. The lable for each worksheet will be what is at the top of the 4 columns in the 'Master' sheet, i.e. Unit A etc. If there is an easier way of searching based upon the list of cost centres (which are static) then we could get rid of the 'Master' sheet altogether.

    Hope that helps


    I'm fairly new to vba and have looked high and low for something that fits my query however nothing I've viewed and played around with actually fits my requirements. Basically I want to use a defined list of values as my criteria which is in the 'Master' sheet, to then go and search all values in 'column B' in the sheet 'Data' and find all rows that meet the criteria. If values are found they then need to be copied and pasted into a new worksheet with the name labelled in A1, A2, A3... in the 'Master'.

    Hope that makes sense. I've attached an example.