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
Bookmarks