+ Reply to Thread
Results 1 to 6 of 6

Copying columns from multiple sheets into one sheet

  1. #1
    Registered User
    Join Date
    07-30-2016
    Location
    kolkata
    MS-Off Ver
    2010
    Posts
    10

    Copying columns from multiple sheets into one sheet

    Sir,
    I have the values of Energy Consumption for two locations in two columns. Each sheet has the energy consumption for each month. This makes 12 sheets (Jan-Dec). I need to copy the data from each of the 12 sheets into one sheet in the order of the Months, 1.e, In one column I need to have the data first for Jan then Feb till Dec. I have attached the excel sheet.
    Thank you.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Copying columns from multiple sheets into one sheet

    So you just want to copy the entire data from each sheet and add to the bottom of the sheet named ALL, with no restrictions?
    Sounds like a job for VBA.

    First, there's no consistency in your sheet names.
    You have "Jan" but then you have "February", rename it to "Feb". Decide upon a standard naming convention for ALL the monthly sheets and stick to it.
    e.g. Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec.

    This code does what you want perfectly but you MUST rename your sheets as above or it won't work.

    Please Login or Register  to view this content.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Copying columns from multiple sheets into one sheet

    Do you know power query???

    If not then just google it Excel power Query tool. It is most efficient tool that Microsoft excel provides to transform your data base.

    I have done this with help of power query with in just few clicks.

    Checkout the attached file.

    Note formula's for not good for big database and yes if anyone can provide you VBA code then it would be good. But in opinion Power Query is best feature in Excel now days.
    Attached Files Attached Files
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  4. #4
    Registered User
    Join Date
    07-30-2016
    Location
    kolkata
    MS-Off Ver
    2010
    Posts
    10

    Re: Copying columns from multiple sheets into one sheet

    Sir I have changed the sheet names as per your suggestion. I did run the VBA code. The second column i.e for LOC2 is getting copied only, not the first column of LOC1. I want both the columns in ALL Sheet.

  5. #5
    Registered User
    Join Date
    07-30-2016
    Location
    kolkata
    MS-Off Ver
    2010
    Posts
    10

    Re: Copying columns from multiple sheets into one sheet

    Thank you Ankur. But I got to read up power query to understand how it is being done.

  6. #6
    Registered User
    Join Date
    08-01-2017
    Location
    sudan
    MS-Off Ver
    2013
    Posts
    1

    Re: Copying columns from multiple sheets into one sheet

    Thank you

+ 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. Replies: 3
    Last Post: 06-20-2014, 05:49 AM
  2. copying 2 columns in one sheet, to all other sheets (except 2 sheets)
    By aaaaa34 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 05-26-2014, 07:59 AM
  3. [SOLVED] Copying last 6 columns of 152 excel sheets/tabs into 1 sheet
    By JOE HNG in forum Excel General
    Replies: 10
    Last Post: 02-28-2014, 03:44 AM
  4. Copying the same columns from multiple sheets into a new one
    By Gav74 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-05-2013, 06:07 AM
  5. Copying a template sheet and putting those multiple sheets data into a summary sheet
    By John Wolfe in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-18-2011, 06:29 PM
  6. Copying cells from 1 sheet to multiple sheets
    By stevenwhite1968 in forum Excel General
    Replies: 3
    Last Post: 05-10-2011, 09:49 AM
  7. Copying data from multiple sheets into one sheet
    By Todd in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-13-2005, 11:05 PM

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