+ Reply to Thread
Results 1 to 2 of 2

Merging Multiple Sheet's column of data based on other column's cell value

  1. #1
    Registered User
    Join Date
    04-08-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    50

    Merging Multiple Sheet's column of data based on other column's cell value

    Dear All,

    Dear All,

    I have attached a sample sheet for your kind reference. Please read the below requirement and resolve the same.

    1. There is a main sheet named as "Merge"
    2. I need to copy data from cells of Column "B" of each sheet to main sheet only when data of corresponding cells of Column C is "Out"
    3. And when the cells copied from all the sheets to main sheet, I need the same to pasted in the Column A of main sheet
    4. When the data are copied to Column A of main sheet, I need the data of Cell "A3" of corresponding sheets to pasted in the Column "B"
    5. For Example, as per our conditions, there will be five cells data will be copied from "Sheet2" and pasted in main sheet in A2,A3,A4,A5,A6, So then cell B2,B3,B4,B5 should have same value of cell A3 of "Sheet2"
    6. Once the data copied from one sheet, then immediately data from the cells from next sheet need to copied to available cell of Column A with giving break or empty row to identify data copied from the next sheet

    Note: Few rows on the top of data will be have merge cells as to mention the header details

    Thanks and regards
    Bala
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    04-08-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Merging Multiple Sheet's column of data based on other column's cell value

    Sub winmaxservices()
    Set ws1 = Sheets("Merge")
    OpenRow = 2
    For Each Ws In ThisWorkbook.Worksheets
    If Ws.Name <> ws1.Name Then
    lastrow = Ws.Cells(Rows.Count, "C").End(xlUp).Row
    If Ws.Range("C" & lastrow).Value = "Out" Then
    Ws.Range("B" & lastrow).Copy ws1.Range("A" & OpenRow)
    ws1.Range("B" & OpenRow & ":B" & OpenRow + lastrow - 2).Value = Ws.Range("A3")
    OpenRow = OpenRow + lastrow - 1
    End If
    End If
    Next
    End Sub

    The above is the code written by me based on my basic knowledge. And somewhere I have did mistake, which leads giving me a wrong result of my attached sample.

    The result which I have got is
    1. Only the cells of last row which falls under my condition was copied to main sheet
    2. on the next column Cell A3 of corresponding sheet were copied as per count of rows available in each sheet.

    Please correct the mistake which I have done in the code.

+ 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. copy entire row to other sheet based on multiple cell criteria of same column
    By jay_jj in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-16-2016, 09:15 AM
  2. [SOLVED] Dynamically color cell on Summary sheet based on column values on data sheet
    By drewship in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-08-2016, 12:59 PM
  3. [SOLVED] Fill a cell in Column B based on the text in Column A using a table on sheet 2
    By lbrasfie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-25-2013, 01:56 PM
  4. Split Data Into Multiple Workbook Based On Column value with exisitng sheet
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-18-2013, 09:23 AM
  5. Multiple workbooks - merging data from first column
    By AliiShariff in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-04-2013, 08:01 AM
  6. [SOLVED] copying specific certain column data based on cell/column value to another sheet
    By arctic7 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-09-2012, 03:38 PM
  7. Copy column data on one sheet to row or column date on another sheet based on user in
    By soready42012 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-08-2012, 12: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