Posts by GoCavs

    When the data is imported what happens to the URLs in Column 1 of Table1? The URL's is a formula based off the ID in Column B so when data is pasted in Column B, the URL's will be updated based on the new values.


    If the imported data has less rows than the current data in Table1, are the extra URLs deleted? Yes, if they will be deleted.


    If the imported data has more rows than the current data in Table1, do additional URLs need to be inserted with the ending number incremented by 1 for each new URL? Yes, they need inserted but when you insert rows, the formulas automatically get populated since it is a table.

    Ozgrid Pre-payment has been sent.


    I have a weekly report (see attached file - Weekly Report_Import Data Sample.xlsm) that I generate that is based off of data that is exported to excel every week (see attached file - Snapshot Export Sample.xlsx). The data from the Snapshot file is manually copied and pasted into the Excel Table (Table1) located on the Snapshot tab of the Weekly Report. And I also have to add rows (and re-format new rows) to the table every week to accommodate the extra rows before pasting into the table. I was looking for a macro to to accomplish the following steps to streamline the process:

    • Add a macro button to the Data tab of the Weekly Report_Import Data Sample file that will pull up a browser window so a Snapshot Export Sample.xlsx file can be selected and then imported or copied to the Table1 located on the Snapshot tab.
    • The entire data set from the Snapshot Export tab beginning with cell A2 down the last row and through Column CH will be copied. The beginning row (2) is static and the column range A:CH is also static. But the number of rows will change (mostly increase week over week). The data should be pasted as values into cell B11 which is in the 2nd row of Table1.
    • Is there a way that the macro could count the number of rows in the Snapshot Export source file (less the Header row) and compare that that to the amount of rows currently available in Table1? For example, in the attached Snapshot Export file contains 1,973 rows of data below Header. The attached Weekly Report_Import Data file has 1,968 rows of data below the Table Header row (and I always leave an additional blank row at the end of the table above the Total Row). Could a macro count the difference of the the amount of rows that need to be added (or subtracted) (5 for this example) and then insert that amount into the table? Then also applying the same format to the new rows as the existing rows have (I normally have to use the Format Painter option to copy format to new rows when I update manually).

      • If this cannot be done in one macro could another be created to accomplish this prior to the import / copy procedure?


    Let me know if you have any ?'s and I will get right back to you.


    Thank you!

    10% pre-payment made:


    I am looking for a macro that will hide/unhide a set of columns in a range based on cell value (A2). I have a number of Autofilter macro buttons which assign cell A2 a value when they are clicked. These buttons are on top of the sheet in a section called "BU-COM Filter". For example, if the "Americas" button is clicked then the string called "Americas" will be input to cell A2 by the macro. Then this will trigger specific columns to be hidden. The column range that I would the macro to consider is columns S through AK. I attached a sample file to work with.


    When A2 is automatically updated to the following values, I would like these columns hidden (see table below). Maybe row 1 can be used as a helper column of some sort??




    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 106"] [/TD]
    [TD="width: 216"]Range("S:AK")[/TD]
    [TD="width: 510"] [/TD]

    [/tr]


    [tr]


    [td]

    Cell A2 value

    [/td]


    [td]

    EntireColumn.Hidden = True

    [/td]


    [td]

    Note

    [/td]


    [/tr]


    [tr]


    [td]

    Null

    [/td]


    [td]

    S:AK

    [/td]


    [td]

    If A2 = NULL or "", then unhide entire range (S:AK)

    [/td]


    [/tr]


    [tr]


    [td]

    Americas

    [/td]


    [td]

    T:U, Z:AK

    [/td]


    [td]

    Hide any column that DOES NOT CONTAIN "Americas" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    EMEA

    [/td]


    [td]

    S, U, V:Y, AD:AK

    [/td]


    [td]

    Hide any column that DOES NOT CONTAIN "EMEA" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    Asia

    [/td]


    [td]

    A:T, V:AC, AH:AK

    [/td]


    [td]

    Hide any column that DOES NOT CONTAIN "Asia" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    EI

    [/td]


    [td]

    S:U, W:Y, AA:AC, AE:AG, AI:AK

    [/td]


    [td]

    Hide any column that DOES NOT CONTAIN "EI" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    FAS

    [/td]


    [td]

    S:V, X:Z, AB:AD, AF:AH, AJ:AK

    [/td]


    [td]

    Hide any column that DOES NOT CONTAIN "FAS" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    DIG

    [/td]


    [td]

    S:W, Y:AA, AC:AE, AG:AI, AK

    [/td]


    [td]

    Hide any column that DOES NOT CONTAIN "DIG" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    TRS

    [/td]


    [td]

    S:X, Z:AB, AD:AF, AH:AJ

    [/td]


    [td]

    Hide any column that DOES NOT CONTAIN "TRS" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    Americas-EI

    [/td]


    [td]

    T:U, W:AK

    [/td]


    [td]

    Hide any column that DOES NOT EQUAL "Americas" OR "Americas-EI" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    Americas-FAS

    [/td]


    [td]

    T:V, X:AK

    [/td]


    [td]

    Hide any column that DOES NOT EQUAL "Americas" OR "Americas-FAS" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    Americas-DIG

    [/td]


    [td]

    T:W, Y:AK

    [/td]


    [td]

    Hide any column that DOES NOT EQUAL "Americas" OR "Americas-DIG" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    Americas-TRS

    [/td]


    [td]

    T:X, Z:AK

    [/td]


    [td]

    Hide any column that DOES NOT EQUAL "Americas" OR "Americas-TRS" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    EMEA-EI

    [/td]


    [td]

    S, U:Y, AA:AK

    [/td]


    [td]

    Hide any column that DOES NOT EQUAL "EMEA" OR "EMEA-EI" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    EMEA-FAS

    [/td]


    [td]

    S, U:Z, AB:AK

    [/td]


    [td]

    Hide any column that DOES NOT EQUAL "EMEA" OR "EMEA-FAS" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    EMEA-DIG

    [/td]


    [td]

    S, U:AA, AC:AK

    [/td]


    [td]

    Hide any column that DOES NOT EQUAL "EMEA" OR "EMEA-DIG" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    EMEA-TRS

    [/td]


    [td]

    S, V:AB, AD:AK

    [/td]


    [td]

    Hide any column that DOES NOT EQUAL "EMEA" OR "EMEA-TRS" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    Asia-EI

    [/td]


    [td]

    S:T, V:AC, AE:AK

    [/td]


    [td]

    Hide any column that DOES NOT EQUAL "Asia" OR "Asia-EI" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    Asia-FAS

    [/td]


    [td]

    S:T, V:AD, AF:AK

    [/td]


    [td]

    Hide any column that DOES NOT EQUAL "Asia" OR "Asia-FAS" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    Asia-DIG

    [/td]


    [td]

    S:T, V:AE, AG:AK

    [/td]


    [td]

    Hide any column that DOES NOT EQUAL "Asia" OR "Asia-DIG" in Row 10

    [/td]


    [/tr]


    [tr]


    [td]

    Asia-TRS

    [/td]


    [td]

    S:T, V:AF, AH:AK

    [/td]


    [td]

    Hide any column that DOES NOT EQUAL "Asia" OR "Asia-TRS" in Row 10

    [/td]


    [/tr]


    [/TABLE]

    Hi,,,I am looking for a macro that will copy and paste a range from one sheet to another is in the same workbook.


    The macro would first look into the look into sheet5 (SurveyClean) and clear contents from column A through column DG and from Row 2 through the last row of data which will not always be the same (there will always be a value in Column A so that can be used as a reference).


    Then the macro will go to sheet10 (SurveyExport) and copy data from column A through column DG and from Row 2 through the last row of data which will not always be the same (there will always be a value in Column A so that can be used as a reference).


    Then the macro would then go to sheet5 (SurveyClean) and paste the data in cell A3. I have other macros in here which clean up the text in this sheet and do not want users to paste over formulas off to the right after column DG.


    Also, would to add a VBA yes/no message box prompting the user with "Would you like to copy the data to the SurveyClean tab? This will overwrite existing data." If yes, proceed with macro, if no quit.


    Thank you and pre-payment has been made to Ozgrid.

    In the attached file I have built 2 dynamic data validation lists in the Sheet (Picklists) based off column data in a another sheet, The Data Validation List exclude blanks and cells with formulas that result in blanks from the reviewer and evaluator columns in the 'SurveyExport' sheet. Now I am looking to add a set of dependent pick lists where the first is a unique list of names from the column called "Survey Name". The the second (dependent) pick list would be every name in the "Evaluator" column where the "Survey Name" value is listed. For example, if "Sanchez, Fernando" was picked in the 'Survey Name' pick list, then in the Evaluator picklist would only have "Smith, Lisa" and "Brown., Nadia" available to select from. Prepayment has been sent.


    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 171"]Survey Name[/TD]
    [TD="width: 171"]Evaluator[/TD]

    [/tr]


    [tr]


    [TD="width: 171"]Sanchez, Fernando[/TD]

    [td]

    Smith, Lisa

    [/td]


    [/tr]


    [tr]


    [TD="width: 171"]Sanchez, Fernando[/TD]

    [td]

    Brown, Nadia

    [/td]


    [/tr]


    [tr]


    [td]

    Sanchez, Fernando

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="width: 171"]Striker, John[/TD]

    [td]

    Smith, Jim

    [/td]


    [/tr]


    [tr]


    [TD="width: 171"]Striker, John[/TD]

    [td]

    Black, Bill

    [/td]


    [/tr]


    [tr]


    [TD="width: 171"]Striker, John[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="width: 171"]Striker, John[/TD]

    [td]

    Gill, Melissa

    [/td]


    [/tr]


    [tr]


    [TD="width: 171"]Striker, John[/TD]

    [td]

    Mayfield, Baker

    [/td]


    [/tr]


    [/TABLE]

    [ATTACH]n1210336[/ATTACH]

    In the attached file I have a macro which removes specific text strings from a range of cells to clean up the cells, The data is from survey results exported into in excel from an outside source and there is a lot of "junk" in the cells that needs cleaned up. The macro Sub MyReplaceMacro() will remove the text strings but after there are some leading spaces remaining that I would like removed. I tried a few things but not working the way I would like. Is there a way to incorporate a TRIM function into the macro run on the defined range in the macro = myRange = Range("BQ2:DG" & lastRow)?


    10% payment sent