Posts by Justin Doward

    Hi Victor,

    Try this code in the code for the sheet:

    And this code in a module:

    Change your drop down in G9 to reflect the sheet names not their references.

    Let me know how it goes.

    Okay, I think we got there. It makes sense now.

    If this has solved it, can you mark it as the answer on stack exchange as well. Cheers Justin

    Hi Victor,

    Again, I think this is what you are after however I am confused by having the entry cell in H now and the changed cell in G since this means the loop to look for hidden columns is redundant?

    Alternatively you could try a loop like:

    1. Sub copyRWs()
    2. Application.ScreenUpdating = False
    3. Dim a
    4. a = Application.Transpose(Sheet1.Range("O2:O" & Sheet1.Cells(Rows.Count, "O").End(xlUp).Row)) 'creates array from O2:last row
    5. For i = LBound(a) To UBound(a)
    6. Sheet1.Rows(i + 1).EntireRow.copy (Sheets(a(i)).Cells(Sheets(a(i)).Cells(Rows.Count, 14).End(xlUp).Row + 1, 1)) 'copies each row
    7. Next i
    8. Application.ScreenUpdating = True
    9. End Sub

    This assumes you have a table starting on row 1, change sheet1 to your sheetname , if jolivanes nor my response achieve what you are after an example worksheet is probably needed.

    Hi M1,

    You can try this:

    The macro record function is very useful to identify the code required to complete a task but it does not do it efficiently, it records everything including when you scroll down the page or switch sheets etc... It is generally not necessary for these functions to occur when using code so you can go through and delete the scroll, select and activate portions of the code however you then will need to slightly modify the syntax of the code to allow for what you have changed.

    You can often replace 10 lines of recorded macro with a single line once you know the appropriate syntax.

    If you are still after a macro solution, I think this does what you are after - try the button on the sheet.

    SampleTables w macro.xlsb

    Hi Alhagag, you can try this:

    It works on the example you sent.

    Hi HE,

    Your example sheet does not appear to make sense with reference to your question, for example "Date Completed" appears as a field in sheet 3 as column B but column Q from sheet 1 is not one of the columns you are trying to copy from sheet1 (according to your question). Additionally your complaint numbers on sheet2 in no way align with the complaint numbers on sheet1.

    I think I know what you are trying to do, i.e. copy the relevant information for a particular complaint from sheet1 to sheet 3 if there is a line Y in sheet 2 column Z but this is not really clear from the your post.

    This code in the sheet should work to some extent if modified to suit.

    As in the attachment it assumes a unique complaint ID, which may or may not be correct?

    Hi HO,

    You could try something like the attached, the macro is in module 1 of the attachment (as below).

    There is a small button on the summary sheet in A1 to activate the macro.

    forum sample with macro.xlsm

    Hi PO,

    It is a little difficult to workout what you are trying to do exactly, I think from what you have described that you have all of your pictures in a single row? However it is not clear. If this is the case then you would wind up overwriting each file as the code you provided takes the name from the row of the picture and column A. It is not however clear how you do intend to name your files, where is the file name located in reference to the picture?

    An example sheet would of course help.

    Hello, thank you for reading:

    I have a workbook with timed events using Application.Ontime referencing a global variable (GlobalTimer) that either opens a form (called TimeOut), or closes the workbook (to prevent users leaving a workbook open on a networked drive preventing others from using it). The form that opens is a warning that the sheet will close in x minutes unless a response is obtained, the form itself then closes after a few seconds.

    This all works well except if windows is locked in which case the Application.WindowState = xlMaximized and Userform.Show commands do not run until the computer is reopened and if minimised the icon in the command bar clicked on.

    Is there a way to detect if lines have failed/is failing to display the form and subsequently simply close the workbook?

    NB: If I simply want to close the workbook, without giving warning, this works fine even if the computer is locked. it is just the loading of the form while the computer is locked that is causing an issue.

    the code in the workbook is like this:

    The code in the module:

    The code linked to the form:

    1. Private Sub UserForm_Activate()
    2. Dim MyTime As Date
    3. MyTime = Now()
    4. Application.OnTime MyTime + TimeValue("00:00:05"), "ClsTIMEOUT"
    5. End Sub

    There are a couple of buttons on the form that either change the globaltimer or does nothing.

    HI O.

    I have added some basic functionality to your form to give some ideas, note that the paste offset is out sometimes due to the merged cells (I think) so unmerge them to fix it.

    Not really sure if it is what you are after, but there is not much there to work with at this point.

    Book31 edit.xlsm


    HI Carim,

    Your macro was returning 1920 for the year, not sure how to fix it formula wise but the cDate solves the problem once you feed it a format it recognises. I modified your code as follows:

    1. Sub DateFixerV3()
    2. Dim MyStr As String
    3. Dim r As Range
    4. For Each r In Range("A2:A4")
    5. MyStr = Replace(r.Text, ".", "/", 1)
    6. r.Offset(0, 6) = CDate(MyStr)
    7. Next r
    8. End Sub