Results 1 to 8 of 8

Copy rows to new sheet based on date range selected on a form

Threaded View

  1. #4
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Re: Press a button to open form, total and average of items, pivot table

    No takers for the last bit of code??

    Here is something I found on the forum that seems to be more complicated (to me anyway) but I have been able to tweak it to at least start copying data from my Distribution sheet to a new sheet, test5 in this case.

    Application.ScreenUpdating = False
    Sheets("Distribution").Select
    Set ws1 = ActiveSheet
    LC = ws1.Cells(1, Columns.Count).End(xlToLeft).Column
    colName = Replace(Cells(1, LC).Address(0, 0), 1, "")
    colName2 = Replace(Cells(1, LC + 2).Address(0, 0), 1, "")
    Worksheets.Add(After:=ws1).Name = dtselect4
    Set ws2 = ActiveSheet
    ws2.Range("A1:" & colName & 1).Value = ws1.Range("A1:" & colName & 11).Value
    'ws2.Range("A:L").Value = ws1.Range("A:L").Value
    'With ws1
    '  .Range("A1:" & colName & 1).Copy
    '  With ws2.Range("A1:" & colName & 1)
    '    ActiveSheet.Paste
    '    .RowHeight = 75.75
    '    Range("F1").Select
    '  End With
    'ws2.Range("A2:" & colName & 1).Value = ws1.Range("A2:" & colName & 11).Value
    With ws1
      .Range("A2:" & colName & 1).Copy
      
      With ws2.Range("A2:" & colName & 1)
        ActiveSheet.Paste
        .RowHeight = 75.75
        Range("D3").Select
      End With
      LR = .Cells(Rows.Count, 1).End(xlUp).row
      ws1.Range(colName2 & 2).Formula = "=IF(D3>=lodate And D3<=hidate,""Copy"","""")"
      ws1.Range(colName2 & 2).Copy ws1.Range(colName2 & 3 & ":" & colName2 & LR)
      With ws1.Range(colName2 & 2 & ":" & colName2 & LR)
        .Value = .Value
      End With
      NR = 2
      With .Columns(LC + 2)
        Set c = .Find("Copy", LookIn:=xlValues, lookat:=xlWhole)
        If Not c Is Nothing Then
          firstaddress = c.Address
          Do
            ws1.Range("A" & c.row & ":" & colName & c.row).Copy ws2.Range("A" & NR & ":" & colName & NR)
            Set c = .FindNext(c)
            NR = NR + 1
          Loop While Not c Is Nothing And c.Address <> firstaddress
        End If
        
     
      End With
    End With
    'End With
    ws2.Range("C2:C" & NR).WrapText = False
    ws2.Range("A1:" & colName & NR).Columns.AutoFit
    ws1.Range(colName2 & 2 & ":" & colName2 & LR).ClearContents
    ws2.Select
    Range("F1").Select
    Application.ScreenUpdating = True
    Some problems are that it is not copying the complete header, it included filters (don't need those), changes column 'L' on the Distribution tab (don't want to change anything on the Distribution tab), and is not copying the rows that meet the selected date range.

    If anyone could help it would be greatly appreciated.

    Thanks,
    Andrew
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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