Posts by gers1978

    Ok, this code works fine:

    Selection.PasteSpecial Paste:=xlPasteValues

    But conscious that using Selection can be slower, I tried:

    detailsSheet.PasteSpecial Paste:=xlPasteValues

    This gives me a compile error, "named argument not found".
    (details.Sheet is set elsewhere, on a user form, as a worksheet)

    Also posted here:…nd-error.html#post4548758

    I call a function right at the start of my code that looks like this:

    Sub OptimizeCode_Begin()
    Application.ScreenUpdating = False
    EventState = Application.EnableEvents
    Application.EnableEvents = False
    CalcState = Application.Calculation
    Application.Calculation = xlCalculationManual
    PageBreakState = ActiveSheet.DisplayPageBreaks
    ActiveSheet.DisplayPageBreaks = False
    End Sub

    At the end of my code I call:

    Sub OptimizeCode_End()
    ActiveSheet.DisplayPageBreaks = PageBreakState
    Application.Calculation = CalcState
    Application.EnableEvents = EventState
    Application.ScreenUpdating = True
    End Sub

    In one of the subs that runs in between, I have the following code:

    If .Worksheets(intCount).Name = "Invoice" Then
                With ActiveSheet
                    .Name = "Invoice"
                End With
                    booFound = True
                    Exit For
                End If

    Yet for some reason, at the line:


    I still get the warning about deleting a sheet.

    If I change that block above to:

    It works fine. But my question is why do I need the "Application.DisplayAlerts = False" immediately before the .Delete statement? Surely alerts should already be set to False due to the initial sub setting it that way?

    Also posted here:…properly.html#post4548740

    Everywhere I look, advice is that declaring of variables is a good thing and that use of Option Explicit is highly recommended.

    What I can't fathom out is why is it optional? Is there any down side to using it?

    I used the macro recorder to get the initial code and incorporated it into the following:

    I'm thinking the line beginning "ActiveCell.FormulaR1C1" is not the best way to do this?
    I'm trying to tidy up the code as best I can

    Also posted here:…ications.html#post4507123

    Ok, I have a table with 10 rows. Each row is pulled through from the bottom row of another workbook. I have 10 such workbooks, and this table pulls through the bottom row from each to make a sort of "summary" table.

    It used INDEX MATCH to get the data, and pulls it through just fine. In all 11 workbooks (10 + summary) the tables are in A4:Z11.

    I have a totals row in my summary table, and I noticed that in column R, the total is 0. All other column's total row is correct.

    R4:R9 are indeed 0, which is correct, but R10 is pulling through £12,531 and R11 £112,137, again both correct. So surely the total row for R should be £124,668?

    Now what's weird is when I select R10 and R11, the little SUM you get at the bottom status bar of Excel says 0. The COUNT is correct, saying 2, but SUM says 0. If I copy and paste values on R10 and R11, that little SUM in status bar still says 0.

    However if I manually type 12531 and 112137 into R10 and R11, the SUM at the bottom (and the totals row of the table) add up correctly.

    What's even weirder is if I select Q10 and Q11 or P10 and P11 (which all have non zero numbers in), the SUM at the bottom status still says 0 (again, the COUNT is correct) but in these columns the totals row is adding just fine anyway!

    The formula for every cell in my summary table is:


    (obviously the lookup reference changes in each cell)

    Also posted here:…lls-only.html#post4504307

    I have a workbook with a sheet that makes use of INDEX and MATCH to look up values in another workbook.

    Problem is I always get #REF errors when I open it. I assume this is because the external workbook the formulas refer to is closed, cos when I open said workbook, it works fine.

    I tried using VLOOKUP instead, same problem.

    Is there any way at all round this?

    I'm using Excel 2010.

    Many thanks.

    Also posted here:…workbook.html#post4500452

    I have a worksheet (attached) with 3 tables. I want a button that adds a new row to each table (or 3 buttons, one for each table, doesn't matter). But when the new row is inserted, I want it to inherit the formulas, etc, from the row above.

    Also, everything else on the worksheet needs to be adjusted accordingly, so other cell references in formulas, etc, are not screwed up.


    Password for protected sheet is password

    Question also asked here:…ious-row.html#post4456866

    I have some code and one of the subs contains this line:

    Do Until Sheet1.Range("A" & Row).Value = vbNullString

    It works flawlessly.

    However I now want to run this code on a different sheet, namely one called "Invoice". So I changed the code to:

    Do Until Invoice.Range("A" & Row).Value = vbNullString

    However now when I run it, I get a runtime 424 error, "Object Required".

    If I change it to:

    Do Until Worksheets("Invoice").Range("A" & Row).Value = vbNullString

    again, it works fine.

    Can anyone tell me what's going on, and why sometimes you need to use "Worksheets("<name>")" format and sometimes not?


    Also posted here:…ed-error.html#post4324572

    I have a column with lots of names, and I want to format them nicely using VBA. I've been trying to use formulas in a spare cell using LEFT, RIGHT, MID, FIND, ISERROR, etc, to get the desired result, then copy/paste the resulting formula into my VBA code, but it's getting very unwieldy, long and hard to follow.

    Basically the names as it stands can be anything from:

    SMYTH, EDWARD E. (Ted)

    I want to format them so that they become First Initial. Surname, unless there's only one word, then I want it left as-is. And I need the whole thing converted to Proper case.

    So in the example above, the finished result should be:

    J Smith
    C Taylor
    M Baker
    E Smyth
    M Miller
    P Parker


    Also posted here:…matting-names-nicely.html

    I have the attached spreadsheet and I've created some code to create a PivotTable, then format some names, then copy the data to a new sheet.

    What I'd like to do is instead of copying all of the data to a single new sheet is create a new sheet per company name, and copy just that companies data to that sheet.

    I can obviously record a macro of me doing this for each company by using the filter in the PivotTable, but the resulting code from the macro is not dynamic, i.e. each time I do this the company name is "hard coded" into the code. This is not use if, next month for example, the data changed and there are more/less/different companies to work with.

    Is there any way of making this "dynamic"? So that each month, regardless of the data set, I can just click a button to execute the "RunAll" macro and it does what I'm looking for?

    Many thanks!

    Re: Formula doesn't work when run as code in a macro

    Quote from S O;757403

    Can you show the whole code?

    I spent a while creating a formula to format some names in column A the way I want them, and it worked fine. The formula is:

    =IF(ISERROR(UPPER(LEFT(RIGHT(A1,LEN(A1)-(FIND(", ",A1)+1)),1)) & " " & PROPER(LEFT(A1,(FIND(", ",A1)-1)))),MID(A1,IF(ISERROR(FIND(",",A1)),FIND(" ",A1),0)+1,1) & " " & LEFT(A1,IF(ISERROR(FIND(",",A1)),FIND(" ",A1),0)-1),UPPER(LEFT(RIGHT(A1,LEN(A1)-(FIND(", ",A1)+1)),1)) & " " & PROPER(LEFT(A1,(FIND(", ",A1)-1))))

    When I enter it, then fill/copy it down my column, it works a treat.
    However I then recorded a macro of me doing it, and when I look at the code afterwards, it's changed to:

    "=IF(ISERROR(UPPER(LEFT(RIGHT(RC,LEN(RC)-(FIND("", "",RC)+1)),1)) & "" "" & PROPER(LEFT(RC,(FIND("", "",RC)-1)))),MID(RC,IF(ISERROR(FIND("","",RC)),FIND("" "",RC),0)+1,1) & "" "" & LEFT(RC,IF(ISERROR(FIND("","",RC)),FIND("" "",RC),0)-1),UPPER(LEFT(RIGHT(RC,LEN(RC)-(FIND("", "",RC)+1)),1)) & "" "" & PROPER(LEFT(RC,(FIND("", "",RC)-1))))"

    and when I run said macro, the whole column has #VALUE errors.
    Any ideas? Thanks!