Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi,

    Canyone help me in making this macro??

    Problem given below:


    I have seprate sheet tabs named as 3545, 3564, 3256, 6958, 3254 and so on. These are the names of sheet tabs.


    Now every sheet contains data in some columns. In few cells there is value "On_HOLD".

    I have one more sheet named as "Consolidated". I want a macro or any formula such that when there is a value"ON_HOLD" in any cell of sheets, it will copy that entire row into "Consolidated" sheet.


    One thing I would like to mention that I dont want macro to copy data from every sheet. I want only few sheet's data.



    Attached is the sample workbook. In this workbook, in sheet" Consolidated" only data from two tabs( 3658 & 3564) is copied.

    Any help would be highly appreciated.


    Thanks and Regards,
    Ankit
    Attached Files Attached Files

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    I suspect that we have not been given the complete story.

    Can "ON_HOLD" appear in any column? Just specific columns on the specific sheets of interest?

    Is data limited to specific columns? or can it appear in any column?

    Is it acceptable to have the Consolidated sheet updated only on demand? (it might be a lengthy procedure).
    Regards
    Don

  3. #3
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts
    [quote name='wdwells' post='789665' date='19-Aug-2009 01:15']I suspect that we have not been given the complete story.

    Can "ON_HOLD" appear in any column? Just specific columns on the specific sheets of interest?

    Is data limited to specific columns? or can it appear in any column?

    Is it acceptable to have the Consolidated sheet updated only on demand? (it might be a lengthy procedure).[/quote]

    Thanks for your prompt reply.


    "ON_HOLD" will appear only in one Column( with column name "Status") and nowhere else and data is limted to 13 Columns starting from Column A to Column M...

    Also, I dont want Consolidated sheet to be updated on demand. I will save the macro and will run it only once in different workbooks.


    I think, now I've given the whole story.


    Thanks in advance!!!!!

    Waiting for reply.



    Ankit

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='ankitag85' post='789666' date='18-Aug-2009 21:25']Thanks for your prompt reply.


    "ON_HOLD" will appear only in one Column( with column name "Status") and nowhere else and data is limted to 13 Columns starting from Column A to Column M...

    Also, I dont want Consolidated sheet to be updated on demand. I will save the macro and will run it only once in different workbooks.

    Ankit[/quote]
    The following code should do the trick. Select the sheets which you wish to consolidate, then run "Copy_On_Hold_Records".
    [codebox]Dim Col As Long
    Dim Ctr As Long
    Dim LastRow As Long
    Dim ROI As Long 'Row of Interest
    Dim SOI() As String 'Sheets of Interest
    Dim SC() As Long 'Status column
    Dim Shts As Long

    Option Explicit
    Public Sub Copy_On_Hold_Records()
    Dim Abort As Boolean
    Call Initialize(Abort)
    If Abort Then Exit Sub

    For Shts = 1 To UBound(SOI)
    With Sheets(SOI(Shts))
    Range("A" & ActiveCell.Row + 1).Select
    LastRow = .Cells(.Rows.Count, SC(Shts)).End(xlUp).Row
    For ROI = 2 To LastRow
    If .Cells(ROI, SC(Shts)) = "ON_HOLD" Then
    .Range("A" & ROI & ":M" & ROI).Copy
    Range("A" & ActiveCell.Row & ":M" & ActiveCell.Row).PasteSpecial (xlPasteValues)
    Range("A" & ActiveCell.Row + 1).Select
    End If
    Next ROI
    End With
    Next Shts
    End Sub

    Public Sub Initialize(Optional Abort As Boolean)
    Dim msg As String

    'Determine Sheets of interest
    Shts = ActiveWorkbook.Windows(1).SelectedSheets.Count
    ReDim SOI(1 To Shts)
    For Ctr = 1 To Shts
    SOI(Ctr) = ActiveWorkbook.Windows(1).SelectedSheets(Ctr).Name
    If SOI(Ctr) = "Consolidated" Then GoTo ConsSel
    Next Ctr

    'Determine the Status columns
    ReDim SC(1 To Shts)
    For Ctr = 1 To Shts
    With Sheets(SOI(Ctr))
    For Col = 1 To 13
    If .Cells(1, Col) = "Status" Then
    SC(Ctr) = Col
    Exit For
    End If
    Next Col
    End With
    Next Ctr

    'Clear the Consolidated sheet

    Sheets("Consolidated").Activate
    Cells.ClearContents
    Range("A1").Select
    Exit Sub

    ConsSel:
    msg = "You have selected the ""Consolidated"" sheet " & vbCrLf
    msg = msg & "as one of the Sheets of Interest." & vbCrLf & vbCrLf
    msg = msg & "The process has been aborted."
    MsgBox msg, vbCritical, "Consolidation of ""ON_HOLD""."
    Abort = True
    End Sub

    [/codebox]
    Regards
    Don

  5. #5
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts
    [quote name='wdwells' post='789675' date='19-Aug-2009 03:21']The following code should do the trick. Select the sheets which you wish to consolidate, then run "Copy_On_Hold_Records".
    [codebox]Dim Col As Long
    Dim Ctr As Long
    Dim LastRow As Long
    Dim ROI As Long 'Row of Interest
    Dim SOI() As String 'Sheets of Interest
    Dim SC() As Long 'Status column
    Dim Shts As Long

    Option Explicit
    Public Sub Copy_On_Hold_Records()
    Dim Abort As Boolean
    Call Initialize(Abort)
    If Abort Then Exit Sub

    For Shts = 1 To UBound(SOI)
    With Sheets(SOI(Shts))
    Range("A" & ActiveCell.Row + 1).Select
    LastRow = .Cells(.Rows.Count, SC(Shts)).End(xlUp).Row
    For ROI = 2 To LastRow
    If .Cells(ROI, SC(Shts)) = "ON_HOLD" Then
    .Range("A" & ROI & ":M" & ROI).Copy
    Range("A" & ActiveCell.Row & ":M" & ActiveCell.Row).PasteSpecial (xlPasteValues)
    Range("A" & ActiveCell.Row + 1).Select
    End If
    Next ROI
    End With
    Next Shts
    End Sub

    Public Sub Initialize(Optional Abort As Boolean)
    Dim msg As String

    'Determine Sheets of interest
    Shts = ActiveWorkbook.Windows(1).SelectedSheets.Count
    ReDim SOI(1 To Shts)
    For Ctr = 1 To Shts
    SOI(Ctr) = ActiveWorkbook.Windows(1).SelectedSheets(Ctr).Name
    If SOI(Ctr) = "Consolidated" Then GoTo ConsSel
    Next Ctr

    'Determine the Status columns
    ReDim SC(1 To Shts)
    For Ctr = 1 To Shts
    With Sheets(SOI(Ctr))
    For Col = 1 To 13
    If .Cells(1, Col) = "Status" Then
    SC(Ctr) = Col
    Exit For
    End If
    Next Col
    End With
    Next Ctr

    'Clear the Consolidated sheet

    Sheets("Consolidated").Activate
    Cells.ClearContents
    Range("A1").Select
    Exit Sub

    ConsSel:
    msg = "You have selected the ""Consolidated"" sheet " & vbCrLf
    msg = msg & "as one of the Sheets of Interest." & vbCrLf & vbCrLf
    msg = msg & "The process has been aborted."
    MsgBox msg, vbCritical, "Consolidation of ""ON_HOLD""."
    Abort = True
    End Sub

    [/codebox][/quote]

    You are simply Awsome!!!!!!!

    Thanks a Million....





    Ankit

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Oops!

    The original code can get into trouble if the file of interest has more than one window open. The following code overcomes this problem.[codebox]Dim Col As Long
    Dim Ctr As Long
    Dim LastRow As Long
    Dim ROI As Long 'Row of Interest
    Dim SOI() As String 'Sheets of Interest
    Dim SC() As Long 'Status column
    Dim Shts As Long

    Option Explicit
    Public Sub Copy_On_Hold_Records()
    Dim Abort As Boolean
    Call Initialize(Abort)
    If Abort Then Exit Sub

    For Shts = 1 To UBound(SOI)
    With Sheets(SOI(Shts))
    Range("A" & ActiveCell.Row + 1).Select
    LastRow = .Cells(.Rows.Count, SC(Shts)).End(xlUp).Row
    For ROI = 2 To LastRow
    If .Cells(ROI, SC(Shts)) = "ON_HOLD" Then
    .Range("A" & ROI & ":M" & ROI).Copy
    Range("A" & ActiveCell.Row & ":M" & ActiveCell.Row).PasteSpecial (xlPasteValues)
    Range("A" & ActiveCell.Row + 1).Select
    End If
    Next ROI
    End With
    Next Shts
    End Sub

    Public Sub Initialize(Optional Abort As Boolean)
    Dim msg As String

    'Determine Sheets of interest
    Shts = ActiveWindow.SelectedSheets.Count
    ReDim SOI(1 To Shts)
    For Ctr = 1 To Shts
    SOI(Ctr) = ActiveWindow.SelectedSheets(Ctr).Name
    If SOI(Ctr) = "Consolidated" Then GoTo ConsSel
    Next Ctr

    'Determine the Status columns
    ReDim SC(1 To Shts)
    For Ctr = 1 To Shts
    With Sheets(SOI(Ctr))
    For Col = 1 To 13
    If .Cells(1, Col) = "Status" Then
    SC(Ctr) = Col
    Exit For
    End If
    Next Col
    End With
    Next Ctr

    'Clear the Consolidated sheet

    Sheets("Consolidated").Activate
    Cells.ClearContents
    Range("A1").Select
    Exit Sub

    ConsSel:
    msg = "You have selected the ""Consolidated"" sheet " & vbCrLf
    msg = msg & "as one of the Sheets of Interest." & vbCrLf & vbCrLf
    msg = msg & "The process has been aborted."
    MsgBox msg, vbCritical, "Consolidation of ""ON_HOLD""."
    Abort = True
    End Sub
    [/codebox]
    Regards
    Don

Posting Permissions

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