Page 1 of 2 12 LastLast
Results 1 to 10 of 14
Like Tree3Likes

Excel Macro to Copy a cell from multiple sheets into a Single sheet based on a value

This is a discussion on Excel Macro to Copy a cell from multiple sheets into a Single sheet based on a value within the Excel Questions forums, part of the Question Forums category; Hi All, I am new here and am trying to learn VBA , need assistance on a excel report I ...

  1. #1
    New Member
    Join Date
    Aug 2013
    Posts
    9

    Default Excel Macro to Copy a cell from multiple sheets into a Single sheet based on a value

    Hi All,

    I am new here and am trying to learn VBA, need assistance on a excel report I am creating.
    I have an excel document with multiple excel sheets(sheet1, sheet2...etc), now every sheet contains a cell "total".
    Now I want to copy the row containing "total" from all the sheets into another sheet called "report".

  2. #2
    Board Regular kevatarvind's Avatar
    Join Date
    Mar 2013
    Location
    India
    Posts
    1,030

    Default Re: Excel Macro to Copy a cell from multiple sheets into a Single sheet based on a value

    Hi And Welcome To the Board

    one question in which Column the value total is containing ?
    Excel 2010 / Windows 7 (Home)
    Excel 2007 / windows xp (Work)


  3. #3
    New Member
    Join Date
    Aug 2013
    Posts
    9

    Default Re: Excel Macro to Copy a cell from multiple sheets into a Single sheet based on a value

    Quote Originally Posted by kevatarvind View Post
    Hi And Welcome To the Board

    one question in which Column the value total is containing ?
    Value total is in column "D".

  4. #4
    Board Regular kevatarvind's Avatar
    Join Date
    Mar 2013
    Location
    India
    Posts
    1,030

    Default Re: Excel Macro to Copy a cell from multiple sheets into a Single sheet based on a value

    try below code
    Code:
    Sub Test1()
    Dim ws As Worksheet
    Dim lr As Long
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "report" Then
            ws.Activate
            lr = Range("D" & Rows.Count).End(xlUp).Row
            For i = 1 To lr
             If Cells(i, 4).Value Like "*total*" Then
              Rows(i).Copy
              Sheets("report").Range("D" & Rows.Count).End(xlUp).Offset(1, -3).PasteSpecial Paste:=xlPasteValues
             End If
             Next
          End If
    Next
    End Sub
    prakashv23 likes this.
    Excel 2010 / Windows 7 (Home)
    Excel 2007 / windows xp (Work)


  5. #5
    New Member
    Join Date
    Aug 2013
    Posts
    9

    Default Re: Excel Macro to Copy a cell from multiple sheets into a Single sheet based on a value

    Quote Originally Posted by kevatarvind View Post
    try below code
    Code:
    Sub Test1()
    Dim ws As Worksheet
    Dim lr As Long
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "report" Then
            ws.Activate
            lr = Range("D" & Rows.Count).End(xlUp).Row
            For i = 1 To lr
             If Cells(i, 4).Value Like "*total*" Then
              Rows(i).Copy
              Sheets("report").Range("D" & Rows.Count).End(xlUp).Offset(1, -3).PasteSpecial Paste:=xlPasteValues
             End If
             Next
          End If
    Next
    End Sub
    Thanks kevatarvind.
    I tried the above macro, I get an error "Subscript Out of Range".

    I tried this on a document with sheet1 and sheet 2, both had cell with "total' in column D but one was on row 170 and other on 150.

  6. #6
    New Member
    Join Date
    Aug 2013
    Posts
    9

    Default Re: Excel Macro to Copy a cell from multiple sheets into a Single sheet based on a value

    Quote Originally Posted by prakashv23 View Post
    Thanks kevatarvind.
    I tried the above macro, I get an error "Subscript Out of Range".

    I tried this on a document with sheet1 and sheet 2, both had cell with "total' in column D but one was on row 170 and other on 150.

    My Bad that worked , thanks kevatarvind.

    Just another question, does it matter if i change the names of sheets ???

    Also, would it be possible that in sheet "reports" ...I know which total came from which sheet ??? i.e = is it possible to prefix the total's in report sheet with sheet name ???
    Last edited by prakashv23; Aug 16th, 2013 at 09:49 AM.

  7. #7
    Board Regular kevatarvind's Avatar
    Join Date
    Mar 2013
    Location
    India
    Posts
    1,030

    Default Re: Excel Macro to Copy a cell from multiple sheets into a Single sheet based on a value

    No doesnt matter you can change sheets name just report sheet name should same as in code

    And yes you can get sheet name also from which sheet data copied

    Right now i am on the way when i will reach home provide u that code
    Excel 2010 / Windows 7 (Home)
    Excel 2007 / windows xp (Work)


  8. #8
    New Member
    Join Date
    Aug 2013
    Posts
    9

    Default Re: Excel Macro to Copy a cell from multiple sheets into a Single sheet based on a value

    Quote Originally Posted by kevatarvind View Post
    No doesnt matter you can change sheets name just report sheet name should same as in code

    And yes you can get sheet name also from which sheet data copied

    Right now i am on the way when i will reach home provide u that code
    Thanks for the response kevatarvind .
    You have been a great help and I would await for code(later).

  9. #9
    Board Regular kevatarvind's Avatar
    Join Date
    Mar 2013
    Location
    India
    Posts
    1,030

    Default Re: Excel Macro to Copy a cell from multiple sheets into a Single sheet based on a value

    try below code your all sheet name will come in Column A and your data will be paste from Column B onwards

    Code:
    Sub Test1()
    Dim ws As Worksheet
    Dim lr As Long
    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "report" Then
            ws.Activate
            lr = Range("D" & Rows.Count).End(xlUp).Row
            For i = 1 To lr
             If UCase(Cells(i, 4)) Like "*TOTAL*" Then
              Sheets("report").Range("E" & Rows.Count).End(xlUp).Offset(1, -4).Value = "Copied From WorkSheet-" & ws.Name
              Range(Cells(i, 1), Cells(i, 200)).Copy
              Sheets("report").Range("E" & Rows.Count).End(xlUp).Offset(1, -3).PasteSpecial Paste:=xlPasteValues
             End If
             Next
          End If
    Next
    Sheets("report").Select
    Range("A1").Select
    Application.ScreenUpdating = True
    End Sub
    prakashv23 likes this.
    Excel 2010 / Windows 7 (Home)
    Excel 2007 / windows xp (Work)


  10. #10
    New Member
    Join Date
    Aug 2013
    Posts
    9

    Default Re: Excel Macro to Copy a cell from multiple sheets into a Single sheet based on a value

    Quote Originally Posted by kevatarvind View Post
    try below code your all sheet name will come in Column A and your data will be paste from Column B onwards

    Code:
    Sub Test1()
    Dim ws As Worksheet
    Dim lr As Long
    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "report" Then
            ws.Activate
            lr = Range("D" & Rows.Count).End(xlUp).Row
            For i = 1 To lr
             If UCase(Cells(i, 4)) Like "*TOTAL*" Then
              Sheets("report").Range("E" & Rows.Count).End(xlUp).Offset(1, -4).Value = "Copied From WorkSheet-" & ws.Name
              Range(Cells(i, 1), Cells(i, 200)).Copy
              Sheets("report").Range("E" & Rows.Count).End(xlUp).Offset(1, -3).PasteSpecial Paste:=xlPasteValues
             End If
             Next
          End If
    Next
    Sheets("report").Select
    Range("A1").Select
    Application.ScreenUpdating = True
    End Sub
    Thanks kevatarvind

    I tried the above vba, but nothing happens when I run it.
    Neither does it throw-up an error nor does it populate the sheet "report".

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com