Posts by ambquinn

    I would really appreciate help as this has been driving me mad for over a week. Basically I have a raw data table where a customer can choose up to 3 different courses, data is in 3 columns. But I need to be able to have a complete table where if a customer selects 3 courses, her details appear in 3 different rows with 1 course each. The paste destination is always the same, but the copy range is slightly different. I have got the code to duplicate rows for more than 1 selection, but I can't get it to copy the selected columns as opposed to the whole row. I have tried defining selection ranges with each if statement and arrays but I am at a complete loss so any advice would save me sanity. Code so far (such that it is, is below) - I had to use paste special values as there are formulae in the original table picking up from another spreadsheet, which I dont want in the final version.

    VBA Code:

    Many thanks.

    Found a workaround in that pdf file doesn’t try to automatically save to drive, giving users the option to change location and save the extract report to their desktop before emailing.
    For some reason the group drive just didn’t like the pdf file!
    So many thanks for the suggestions.

    Many thanks Max1616, I tried both suggestions but didn’t work. On the off chance I moved another workbook to the group drive, (that exports a sheet to excel format and then emails).

    The excel Extract vba appears to work fine, could the pdf side be the problem? Again, advice would be appreciated as PDF is def the preferred option.

    I have a statistical workbook, within which is a particular sheet, that I need to be able to copy and email in pdf format only. The workbook has been in use for some time and works perfectly from my desktop. However, it has to now be moved to a shared drive, as others in the office need access.

    Subsequently I revised the code to include definition and path "Mypath" - group drive is B and the workbook is with a folder called "Stat" - I have bolded the changes in code.
    Now, it wont work - every time coming up with "Run-time error ' -2147024773 (8007007b)': Document not saved.
    When I debug - it goes to
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=Mypath & PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

    As stated, works perfectly from desktop but I cant get the new path working at all & its driving me mad. So any help would be greatly appreciated :)
    Many thanks.

    Code used

    Private Sub CommandButton3_Click()
    Dim IsCreated As Boolean
    Dim i As Long
    Dim PdfFile As String, Title As String, Mypath As String
    Dim OutlApp As Object

    Title = "Stat Report"

    PdfFile = ActiveWorkbook.FullName
    i = InStrRev(PdfFile, ".")
    If i > 1 Then PdfFile = Left(PdfFile, i - 1)
    PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf"
    Mypath = "B:\Stat"

    With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=Mypath & PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    End With

    On Error Resume Next
    Set OutlApp = GetObject(, "Outlook.Application")
    If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
    End If
    OutlApp.Visible = True
    On Error GoTo 0

    With OutlApp.CreateItem(0)

    .Subject = Title

    .Body = "Hi," & vbLf & vbLf _
    & "The report is attached in PDF format." & vbLf & vbLf _
    & ActiveSheet.Range("a5").Value
    ' & "Regards," & vbLf _
    ' & Application.UserName & vbLf & vbLf
    .Attachments.Add PdfFile

    On Error Resume Next

    Application.Visible = True
    If Err Then
    MsgBox "E-mail was not sent", vbExclamation
    MsgBox "E-mail ready to send", vbInformation
    End If
    On Error GoTo 0

    End With

    Kill PdfFile

    'If IsCreated Then OutlApp.Quit

    Set OutlApp = Nothing

    End Sub

    We are devising a database, to record and analyse sales through different areas, zones and departments.
    As it is essentially an analysis database, (plus other reasons), Access is not suitable, but part of it does rely on sales figures being input across many different spreadsheets.

    For ease of input and accuracy, we have devised a userform.
    The user selects the area and timeframe and then inputs figures within textboxes for each department within the different zones on a multipage userform.

    The query is, we would want the info as input to automatically populate the different zones within the different tables within the different department spreadsheets corresponding to the area and timeframe selected, when the submit button is clicked

    The timeframes mirror the table headers. The tables have been named on each spreadsheet as AreaXDepartment, for example area1hardware, area1groceries etc etc. The zones are entered as rows.

    In addition, if a user selects a timeframe and area for which the data is already on the table and clicks submit, a warning box will appear, “This information is already entered. Do you wish to overwrite?” with an option to cancel, (to bring back to the userform) or proceed, (to overwrite the info on the tables.
    Obviously some kind of lookup is required but I can’t get it working especially when tables are involved!

    A sample of the database has been attached and if anyone could point me in the right direction, I would be extremely grateful.

    Re: Identify records based on multiple conditions & single occurance of value

    Infomage, apologies for delay in replying!
    In consideration of the end users, (who agreed with the structure) and the varying degree of Excel knowledge, I would prefer that it was unchanged and didn't need to be sorted. However, at this stage I am so stuck, I would consider anything :), so anything you have would be appreciated.
    The end data will have in excess of 20,000 rows and will span the last few years. Do you think the solution could be VBA?

    Excel 2007.
    My title probably doesn't explain very well what I need to do.

    Basically it is not just to identify duplicates based on multiple criteria, but also then to return a value for all of the entries, if another criteria is met by any one of the duplicate records.

    I have a table listing all payments made iro people on a course, the columns to compare / analyze are the Client ref, the start date and the Registration fee, which may be at a reduced rate.
    There are a number of other columns re different payments such as Child Care and Travel and each payment is listed separately. Therefore the Registration fee will not always be completed, but will appear against one different start date per client and a client may have a number of entries.
    I can identify the duplicates across the client ref and start date, however I need to return "Reduced" for all incidences if the client had a reduced registration fee in order to identify all payments made based on a reduced registration fee.

    I have attached a sample workbook, the formula I am using (Column A) is returning "Reduced" for those duplicate client refs and start dates, but only if the Registration fee column is completed with the rate of 200, whereas I need it to return "Reduced" in column A against those rows highlighted in yellow as they are relevant to the course in question.
    The Client reference will not necessarily be in numerical order.

    Any help would be hugely appreciated as I really am just going round in circles.

    Re: Copy and paste entire rows from data sheet based on two criteria within user for

    sktneer - I'm interested in your code too, (as I have never used autofilter). However, even when a name has been selected in the list box and a date entered, the warning boxes still come up. I'm prob doing something really thick, but would appreciate your advice as I want to get my head around this.

    But huge thanks to both!

    Re: Copy and paste entire rows from data sheet based on two criteria within user for

    [FONT=&quot]Firstly - many thanks!

    KjBox - brilliant, (I used the first). The only really minor thing is that the data is being copied into the first row of the summary sheet, (which overwrites info already there). However, it is such a minor thing & I'll figure it out, am so happy to have code that actually works and works so well!


    Excel 2007 VBA assistance.
    Would appreciate any help with this one as it is driving me mad. I can find VBA code to copy and paste rows based on a hard-coded array or specific values. However, I have a user form which allows the selection of any one staff member and any input date and I can't get it working.

    Basically I have a huge "Sales" sheet, from which I want to be able to copy and paste entire rows into a "Summary" sheet based on two chosen criteria (Staff name & date of sales) via a userform when the "Submit" commandbutton is clicked.

    I then also want to be able to email the "summary" spreadsheet only by clicking another commandbutton, "Email", but I should be okay with this part myself.

    Source spreadsheet Name: "Sales"
    Column A Staff Name Column B Date of Sales
    Data starts row 9

    Destination spreadsheet name: "Summary"
    CommandButton: brings up user form
    Listbox1: list of staff names to select
    Textbox1: date to input re sales

    "Submit" Commandbutton - want to copy and paste entire rows based on staff name and specific date from "Sales" spreadsheet (from row 9) into "Summary" spreadsheet (from row 9)

    As I said, any help would be greatly appreciated!!!
    Many thanks

    I'd really appreciate some help on this one as I'm totally confused. I am using Microsot Excel 2003. The idea is fairly simple I have a large workbook, one spreadsheet of which (called review dates) is of client names with corresponding review dates (2 weekly, 3/6/12/18 monthly etc, etc). The columns are conditionally formatted to highlight any dates that fall within a week of the present date but its very time consuming and errror prone to scroll through hundreds of rows of client names plus across a number of columns looking for any due reviews. The idea is then to have a single summary worksheet report, to return all due actions detailing clients name, the date that a review is due and the type of review due, (eg 3 month). As stated any help at all would be good, I'm very new to VBA. Many thanks