Need formula or macro to copy rows from one worksheet to another, including form

Question

Need formula or macro to copy rows from one worksheet to another, including formatting if there is any value in an specific column, then hiding specific columns so that the worksheet can be printed

Hello,

I have an Excel workbook with several worksheets. One has a list of items, one item per row and columns with specific information about those items.

Step 1:

I need a formula or Macro to copy any row that has any entry on an specific column (M) to a different worksheet in the same workbook, ignoring any row which has no entry or "0" (zero) in that column (M). The formula or Macro needs to create a new worksheet using the current date in the "mm/dd/yyyy" format as the name of the newly created worksheet with the extracted data.

I like for this to be copied preferably using a button or automatically creating the worksheet as data is entered into the worksheet. If a worksheet is already there with that name then appending rows to that worksheet. In another day when the Macro is ran another worksheet would be created with a different date for the name of that worksheet.

Step 2:

Then from the newly created worksheet rearrange data based on information stored on a specific column (A). Then group data that is the same in that particular column (A) by inserting 2 blank rows below each group of data. So if in cell A3 to A10 I have "Test" and Cell A11 to A20 I have "Good" and cells A21 to A25 I have "Test" again, then it will group all the rows with "Test" in Column "A" together and insert 2 blank rows below them and then group the next data that is the same below and so on...

Step 3:

On the SOURCE worksheet I like for this Macro to highlight with yellow all the rows that have data on Column (M) other than blank or the number "Zero" (0).

Hope this is not too much.

At the very least I would like to see step 1 and 3. The rest I could do manually.

Thank you for your assistance.

Explanation / Answer

1) data on sheet1 named 'data' (change name)

2) in row 1 are headers

3) in column M: ignore zeros and empty cells

4) in column A: group data by names

if so, try this...

Sub ExportData_Group()

'Sep 09, 2014

Const shName As String = "data"   '<< sheet name
Const CritC = "M" '<< column M, ignore zeros and empty cells
Const SortC = "A" '<< names in column A
Dim ws As Worksheet
Dim r As Long, c As Long, i As Long
Set ws = Sheets(shName)
r = ws.Cells(Rows.Count, "A").End(xlUp).Row
c = ws.Cells(1, Columns.Count).End(xlToLeft).Column
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each sh In Sheets
If sh.Name = Format(Date, "dd-mm-yyyy") Then sh.Delete
Next
Application.DisplayAlerts = True
Sheets.Add
ActiveSheet.Name = Format(Date, "mm/dd/yyyy")

ws.Range("A1").Resize(r, c).Copy
Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
With ActiveSheet.Sort
.SortFields.Clear
.SortFields.Add Key:=Range(SortC & "2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range(SortC & "2:" & SortC & r)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range(CritC & ":" & CritC).Replace What:="0", Replacement:="", LookAt:=xlWhole
Range(CritC & ":" & CritC).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
r = Cells(Rows.Count, "A").End(xlUp).Row
For i = r To 2 Step -1
If i - 1 = 1 Then
'nothing
Else
If Cells(i, SortC) <> Cells(i - 1, SortC) Then Rows(i).Resize(2).Insert
End If
Next
ActiveSheet.UsedRange.EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.