Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    Fixed-row criteria

    Hello,

    Hi All,

    I have a issue , i have 50 plus sheets in a work book each sheets contain dates in column B . I need that the code look to (main sheet) column B dates and adjust rows of all the sheets as like main sheet based on dates. suppose in main sheet the date 1/1/2009 is placed at B row 15 so code adjust the whole dates and rows of all the sheets as like the main sheet, if date 1/3/2009 is place in column B row no 20 then the code adjust all the worksheet dates and rows to row number 20.


    Cheers

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    It is not clear to me what you want exactly. Could you attach a sample workbook of a before and after?

    My preference would be, if the sheets are supposed to be sorted/linked in that manner is to put them all on the same worksheet, so that they can sorted/moved/copied all together.

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Sir Steve,

    I have attached the before and after results , the problem in sorting in one sheet is i need to fixed the different sheet data to ( Data sheet ) . Thats why i want to sort or adjust the rows according to the master sheet. In sample i have deducted the sheet because there are so many

    Sorry if i cannot understand well.

    Thanks
    Attached Files Attached Files
    Last edited by farrukh; 2012-01-11 at 00:15.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Does this code do what you need (Test on a copy of your workbooks since it modifies and deletes sheets!)

    Code:
    Sub SortAll()
      Dim wMaster As Worksheet
      Dim wks As Worksheet
      Dim sWksName As String
      Dim wTemp As Worksheet
      Dim rDates As Range
      Dim rMasterDates As Range
      Dim iHeaderRow As Integer
      Dim iDateCol As Integer
      Dim lRow As Long
      Dim rCell As Range
      Dim i As Integer
      Application.ScreenUpdating = False
      
      'Define initial settings
      iHeaderRow = 2  'Header is in row 2
      iDateCol = 2    'Dates are in col 2 = B
      Set wMaster = Worksheets("Master_sheet")
      
      'set Date lookup range
      With wMaster
        Set rMasterDates = .Range(.Cells(1, iDateCol), _
          .Cells(.Rows.Count, iDateCol).End(xlUp))
      End With
      
      'loop through all worksheets
      For i = ActiveWorkbook.Worksheets.Count To 1 Step -1
        Set wks = Worksheets(i)
        'Ignore Data and Master_sheet
        If wks.Name <> "Data" And _
          wks.Name <> wMaster.Name Then
          'create a new temp worksheets to move the rows
          'in case there is overlap
          sWksName = wks.Name
          Set wTemp = Worksheets.Add(before:=wks)
          With wks
            'copy header
            .Rows(iHeaderRow).Copy wTemp.Cells(iHeaderRow, 1)
            'get date range
            Set rDates = .Range(.Cells(iHeaderRow + 1, iDateCol), _
              .Cells(.Rows.Count, iDateCol).End(xlUp))
            'loop through date range
            For Each rCell In rDates
              If rCell  <> "" Then
                lRow = Application.WorksheetFunction. _
                  Match(rCell, rMasterDates, 0)
                rCell.EntireRow.Copy wTemp.Cells(lRow, 1)
              End If
            Next
            'delete original wks
            Application.DisplayAlerts = False
            .Delete
            Application.DisplayAlerts = True
          End With
          'rename Temp
          wTemp.Name = sWksName
        End If
      Next
      
      Application.ScreenUpdating = True
      Set wMaster = Nothing
      Set wks = Nothing
      Set wTemp = Nothing
      Set rDates = Nothing
      Set rMasterDates = Nothing
      Set rCell = Nothing
    End Sub
    Steve
    Last edited by sdckapr; 2012-01-12 at 17:11.

  5. The Following User Says Thank You to sdckapr For This Useful Post:

    farrukh (2012-01-13)

  6. #5
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Sir Steve,

    The code was showing the syntax error in those lines in the code i have change line like below but when i run this it does not change the before.xls data ?

    Please advise ?

    Code:
    'Ignore Data and Master_sheet
        If wks.Name = "Data" And _
        wks.Name = wMaster.Name Then
    Code:
     'loop through date range
            For Each rCell In rDates
              If rCell = "" Then
    Cheers

  7. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I don't know why the code was missing an item. I corrected the code. It is not an equal sign (=) that was missing, it was a not equal sign. The lines should be:

    Code:
    'Ignore Data and Master_sheet
        If wks.Name <> "Data" And _
        wks.Name <> wMaster.Name Then
    and
    Code:
    'loop through date range
            For Each rCell In rDates
              If rCell <> "" Then
    Steve

  8. The Following User Says Thank You to sdckapr For This Useful Post:

    farrukh (2012-01-13)

  9. #7
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Sir Steve,

    Superb, Fantastic you have saved my time alot for creating this code.


    Thank you

    Regards,
    Farrukh

  10. #8
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    ---
    Last edited by farrukh; 2012-01-30 at 05:00.

Posting Permissions

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