Hello
Is there a better way to accomplish the below , rather than using used range?
Also, is there a better way to clear the contents of cells D:I all in one, rather than listing each out?
Purpose of Macro:
Step 1. INPUT Tab: Remove all data in Columns D:I , if the text in column D = “PMFA” . Keep all others. ( this clears out the current day data and prepares the sheet for tomorrow)
Step 2. INPUT tab: Re-sort the data in columns D: I, sorting by Column D, Largest -smallest. ( this should put the non-PMFA data on the top)
Step 3. Go to “PMFA” tab within the same workbook and copy values from only the filled/used in data from columns Q:V . ( there could be data from cells Q2: V500, but we only want the cells that have data, for example in Q2: Q30)
Step 4: Go back to the Input tab in this same workbook, and append the data as values into columns D:I , on the next available row in column D.
Note: columns A-C on the Input tab have formulas used for another purpose, so they can’t be ignored.
Note: this code works, but sometimes we have problems using used range ( cells look blank, but the macro thinks they are used)
________________________________________________________________________________________
Is there a better way to accomplish the below , rather than using used range?
Also, is there a better way to clear the contents of cells D:I all in one, rather than listing each out?
Purpose of Macro:
Step 1. INPUT Tab: Remove all data in Columns D:I , if the text in column D = “PMFA” . Keep all others. ( this clears out the current day data and prepares the sheet for tomorrow)
Step 2. INPUT tab: Re-sort the data in columns D: I, sorting by Column D, Largest -smallest. ( this should put the non-PMFA data on the top)
Step 3. Go to “PMFA” tab within the same workbook and copy values from only the filled/used in data from columns Q:V . ( there could be data from cells Q2: V500, but we only want the cells that have data, for example in Q2: Q30)
Step 4: Go back to the Input tab in this same workbook, and append the data as values into columns D:I , on the next available row in column D.
Note: columns A-C on the Input tab have formulas used for another purpose, so they can’t be ignored.
Note: this code works, but sometimes we have problems using used range ( cells look blank, but the macro thinks they are used)
________________________________________________________________________________________
VBA Code:
Sub INPUT_AddPMFA()
Dim g As Long
For g = 2 To ActiveSheet.UsedRange.Rows.Count
If Cells(g, "D").Value = "PMFA" Then
Cells(g, "D").ClearContents
Cells(g, "E").ClearContents
Cells(g, "F").ClearContents
Cells(g, "G").ClearContents
Cells(g, "H").ClearContents
Cells(g, "I").ClearContents
End If
Next
ThisWorkbook.Activate
Sheets("PMFA").Select
Range("Q2:V500").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Application.CutCopyMode = True
Selection.Copy
Sheets("Input").Range("D" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range("D1").Select
End Sub
Last edited by a moderator: