Posts by daren.beaney

  • For Each Worksheet in... not moving to next worksheet.

    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?

    Code
    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!!

  • Finding a value and updating the monthly figures from another spreadsheet

    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

  • Finding a value and updating the monthly figures from another spreadsheet

    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!

  • Find multiple values then delete cell and the next cell to the right

    Hi,


    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.


    Thanks,
    Daren

  • Autofilter macro error when only 1 row of data exists

    Hi,

    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.

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

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

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


    Hi,

    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.

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

    Hi,

    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.

    Regards,
    Daren

  • Using paste special when using Copy Destination

    Re: Using paste special when using Copy Destination


    Ped,

    I sorted it, see below:

    Code
    For Each ws In Worksheets
Select Case UCase(ws.Name)
Case "Index", "Data", "Summary", "Charts"
Case Else
ws.Range("Z6:AB11").Copy
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!

  • Using paste special when using Copy Destination

    Hi,

    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.

    Code
    [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]
[FONT=Arial][size=10][COLOR=#17365d]

    [/COLOR][/SIZE][/FONT]

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

    Thanks
    Daren

  • Use defined criteria in worksheet to find rows in another worksheet & add new wksheet

    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
    Daren

  • Use defined criteria in worksheet to find rows in another worksheet & add new wksheet

    Hi,

    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.

    Thanks,
    Daren