+ Reply to Thread
Results 1 to 4 of 4

Append new text at bottom of column based on value at top of column

  1. #1
    Registered User
    Join Date
    01-23-2013
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    18

    Append new text at bottom of column based on value at top of column

    Hi Everyone,

    I get a data pull weekly that has multiple tabs across multiple workbooks, and within each tab there are summaries of different company data.

    So a tab might look like this:

    COMPANY Name
    data 1
    data 2
    data 3
    data 3
    display sum

    COMPANY Name #2
    data 1
    data 2
    display sum

    There is an existing spreadsheet which links to these files and automatically pulls the data, but I have to go into each sheet and tab and manually append the company name after "display sum". So in the end it will end up being "display sum COMPANY Name"

    Couple of important points:
    -The number of companies in each tab can change week to week
    -the number of data points can change week to week
    -The "display sum" will ALWAYS be the last row of the data
    -The company name and "display sum" will always be in the same column (A)

    Is there something I can write that will cycle through the sheets and append the company name to the end of "display sum"?

    Any suggestions welcome!

  2. #2
    Forum Contributor
    Join Date
    05-27-2014
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    2007 & 2010
    Posts
    273

    Re: Append new text at bottom of column based on value at top of column

    Your example shows:
    • All data in a single column
    • The company name is the first piece of data in a block of data
    • Each company (except the first) is separated from the previous by a blank row

    It would really be a good idea to upload a sample, but, based on what you've said, this should work (untested):
    Please Login or Register  to view this content.
    Last edited by kadeo; 09-03-2015 at 12:37 PM.
    Please click *Add Reputation if I've helped

  3. #3
    Registered User
    Join Date
    01-23-2013
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Append new text at bottom of column based on value at top of column

    Hi Kadeo,

    That looks like it will work! And I'm seeing the logic, but for whatever reason when I get to "for each wSheet in wBook", an error comes up that says "Run-time error '438': Object doesn't support this property or method"

    I also tried to change "a1" to "a11" because every sheet starts on a11...I've attached an example of one of the files I get. I'll keep playing around with what you've provided, it would be ok if it did 1 workbook at a time, it will still save me a huge amount of time.

    Thank you!!

  4. #4
    Forum Contributor
    Join Date
    05-27-2014
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    2007 & 2010
    Posts
    273

    Re: Append new text at bottom of column based on value at top of column

    I looked at your data.

    Try this: (I forgot to include the collection on the 'for each wsheet in wbook' line)

    Please Login or Register  to view this content.
    The reason why it checks for "PERSONAL.XLSB" is because that workbook doesn't have any sheets and would cause a crash. Also, some sheets have no data which is why it now checks if the last row is > than 11 (row 11).

    If you don't have a personal macro workbook, you should set one up and save this macro to a standard module in it. Then you have access to it all the time and not just when a specific workbook is open. If you don't this macro should work just as fine but you'd need to store it in a workbook and open that workbook everytime you want to use it.
    Last edited by kadeo; 09-04-2015 at 05:02 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA to append data with additional information based on row and column reference
    By nielsen555 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-19-2015, 09:30 PM
  2. Append text to a pivot table column
    By saya_26 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 03-05-2014, 02:45 PM
  3. Move a specific text from on column and append to another
    By helloworld2012 in forum Excel General
    Replies: 1
    Last Post: 03-22-2011, 04:46 AM
  4. Append column to list based on match
    By dougw03 in forum Excel General
    Replies: 3
    Last Post: 01-31-2011, 12:47 PM
  5. Append text to the begining of each row in column A
    By gjack72 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-04-2010, 03:18 PM
  6. [SOLVED] Append text to entire column
    By ryanj4314 in forum Excel General
    Replies: 10
    Last Post: 01-16-2010, 12:44 PM
  7. Add like text totals of columns at bottom of column
    By duugg in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 07-08-2009, 11:03 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1