VBA to automate spreadsheet processing

Musab · #1 05-10-2018, 07:35 PM

[FONT="Arial"]Hi fellow MS'ers,

I have a macro of me processing a mainsheet labelled MAINSHEET into multiple sheets, SHEET1 to SHEET6. I am completely lost when it comes to VBA, need some support here, I have listed down the variations I require to the code below, thanks guys.

When the remove duplicates runs, it doesn't remove for a dynamic range, it removes for a fixed number of rows.
Field2 to Field9 on SHEET1, I want all these fields with empty cells to display "Blank" instead.
Field10 on SHEET2, I want all the empty fields to have their entire row removed, Field 11 to Field 23 to display "Blank" if cell is empty on SHEET 2.
CUSTOM_FIELD on SHEET2 needs to be copied down to all rows that have Field 24 values.
Field24 on SHEET3, I want all the empty fields to have their entire row removed.
Field 25 to Field 30 to display "Blank" if cell is empty on SHEET 3.
CUSTOM_FIELD_2 on SHEET3 needs to be copied down to all rows that have Field 10 values.
Field31 & Field32 on SHEET4, I want all the empty fields to have their entire row removed.
Field 33 to Field 36 to display "Blank" if cell is empty on SHEET 3.
CUSTOM_FIELD on SHEET4 needs to be copied down to all rows that have Field 32 values.
Field37 on SHEET5, I want all the empty fields to have their entire row removed.
Field 33 to Field 36 & Field 38 to Field 53 to display "Blank" if cell is empty on SHEET 3.
CUSTOM_FIELD on SHEET5 needs to be copied down to all rows that have Field 37 values.
Field24 on SHEET6, I want all the empty fields to have their entire row removed.
Field 26 to Field 30 to display "Blank" if cell is empty on SHEET6.

I swear if I can get someone with a working code on all different sheets I will donate :-)))))

I have uploaded an example EXCEL FILE, please refer to this when suggesting a solution, thank you guys! Now imagine there are 3 pumps that need preventative maintenance planned out, so I created this spreadsheet to layout the maintenance information with the necessary FIELDS (details of maintenance) as data. It is very confusing to read guys so please ask me if you need additional info, thank you so much.

Code:

Sub AwesomeMacroRecordBroHaha()

'Creates multiple sheets based on main sheet, MAIN SHEET is called: MAINSHEET'
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "SHEET1"

    Sheets.Add After:=ActiveSheet
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "SHEET2"

    Sheets.Add After:=ActiveSheet
    Sheets("Sheet3").Select
    Sheets("Sheet3").Name = "SHEET3"

    Sheets.Add After:=ActiveSheet
    Sheets("Sheet4").Select
    Sheets("Sheet4").Name = "SHEET4"

    Sheets.Add After:=ActiveSheet
    Sheets("Sheet5").Select
    Sheets("Sheet5").Name = "SHEET5"

    Sheets.Add After:=ActiveSheet
    Sheets("Sheet6").Select
    Sheets("Sheet6").Name = "SHEET6"

'Pasting and processing data in SHEET1 sheet FROM MAIN SHEET called: MAINSHEET'
    Sheets("MAINSHEET").Select
    Columns("A:I").Select
    Selection.Copy

    Sheets("SHEET1").Select
    ActiveSheet.Paste

    Columns("A:I").Select
    Range("I1").Activate
    Application.CutCopyMode = False

    ActiveSheet.Range("$A$1:$I$107").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, _
        7, 8, 9), Header:=xlYes
    Columns("B:B").Select

    Selection.FormatConditions.AddUniqueValues
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).DupeUnique = xlDuplicate
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With

    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With

    Selection.FormatConditions(1).StopIfTrue = False


'Pasting and processing data in SHEET2 sheet FROM MAIN SHEET called: MAINSHEET'

    Sheets("MAINSHEET").Select
    Columns("A:AF").Select
    Selection.Copy

    Sheets("SHEET2").Select
    ActiveSheet.Paste
    Columns("C:I").Select

    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("Q:W").Select
    Selection.Delete Shift:=xlToLeft
    Columns("R:R").Select
    Selection.Copy
    Columns("S:S").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("S1").Select

    ActiveCell.FormulaR1C1 = "CUSTOM_FIELD"
    Range("S2").Select
    Application.CutCopyMode = False

    ActiveCell.FormulaR1C1 = "=RC[-2]&""/""&RC[-1]"
    Columns("A:S").Select

    ActiveSheet.Range("$A$1:$S$107").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, _
        7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19), Header:=xlYes
    Rows("3:3").Select
    Selection.Delete Shift:=xlUp
    Columns("C:C").Select

    Selection.FormatConditions.AddUniqueValues
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).DupeUnique = xlDuplicate
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With

    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With

    Selection.FormatConditions(1).StopIfTrue = False

'Pasting and processing data in SHEET3 sheet FROM MAIN SHEET called: MAINSHEET'

    Sheets("MAINSHEET").Select
    Columns("A:AD").Select
    Selection.Copy

    Sheets("SHEET3").Select
    ActiveSheet.Paste

    Columns("B:I").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("C:O").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:H").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:D").Select
    Selection.ClearContents
    Columns("A:D").Select

    Range("D1").Activate
    ActiveSheet.Range("$A$1:$D$107").RemoveDuplicates Columns:=Array(1, 2, 3, 4), _
        Header:=xlYes
    Range("D1").Select

    ActiveCell.FormulaR1C1 = "CUSTOM_FIELD_2"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=IF(HasStrike(RC[-1])=TRUE,""X"",""Blank"")"

'Pasting and processing data in SHEET4 sheet FROM MAIN SHEET called: MAINSHEET'

    Range("D3").Select
    Sheets("MAINSHEET").Select
    Columns("A:AJ").Select
    Selection.Copy
    Sheets("SHEET4").Select
    ActiveSheet.Paste
    Columns("B:J").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("D:U").Select
    Selection.Delete Shift:=xlToLeft
    Columns("A:I").Select
    Range("I1").Activate

    ActiveSheet.Range("$A$1:$I$107").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, _
        7, 8, 9), Header:=xlYes

    Columns("F:F").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("E:E").Select
    Selection.Copy
    Columns("F:F").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("F:F").Select
    Selection.ClearContents
    Range("F1").Select

    ActiveCell.FormulaR1C1 = "CUSTOM_FIELD"
    Range("F2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-2]&""/""&RC[-1]"
    Range("F3").Select

'Pasting and processing data in SHEET5 sheet FROM MAIN SHEET called: MAINSHEET'

    Sheets("MAINSHEET").Select
    Columns("A:BA").Select
    Selection.Copy
    Sheets("SHEET5").Select
    ActiveSheet.Paste
    Columns("B:AD").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft

    Columns("A:X").Select
    ActiveSheet.Range("$A$1:$X$107").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, _
        7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24), Header:=xlYes
    Columns("I:I").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("H:H").Select
    Selection.Copy
    Columns("I:I").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "CUSTOM_FIELDSHEET5"
    Range("I2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-7]&""/""&RC[-6]&""/""&RC[-1]"
    Range("I2").Select
    Columns("I:I").EntireColumn.AutoFit
    Range("J9").Select
    Cells.Replace What:=" JP 000 00", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("J5").Select
    ActiveWindow.SmallScroll Down:=-9

'Pasting and processing data in SHEET6 sheet FROM MAIN SHEET called: MAINSHEET'

    Sheets("MAINSHEET").Select
    Columns("A:AD").Select
    Selection.Copy
    Sheets("SHEET6").Select
    ActiveSheet.Paste
    Columns("B:W").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("A:H").Select
    Range("H1").Activate
    ActiveSheet.Range("$A$1:$H$107").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, _
        7, 8), Header:=xlYes
    Range("D7").Select
    ActiveWindow.SmallScroll Down:=-18
End Sub

Function HasStrike(Rng As Range) As Boolean
    HasStrike = Rng.Font.Strikethrough
    End Function

BobBridges · #2 05-24-2018, 04:56 PM

Hi, Musab. I thought I'd drop by after a while away, and I see that no one's responded to this yet. I'm guessing it's because this is a relatively complex question and no one cares to just write your program for you, since you say you're helpless in VBA.

But I would say that's where the solution lies, too: If you're helpless in VBA, then you need to get less helpless in VBA—less-helpless enough that you end up being able to write this program and understand how it works. Me, I'm no more enthusiastic about just writing it for you than anyone else. But I don't mind coaching you for a while until you're able to create the solution for yourself. Interested?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Average Processing Time	christian.calimlim22	Excel	3	04-04-2017 11:25 PM
Batch document processing	SadielCuentas	Word VBA	0	04-19-2016 09:34 AM
How to automate opening multiple URLs from an Excel Spreadsheet?	rishumehra	Excel Programming	0	01-28-2016 01:49 AM
Word Processing VERY slowly	zswindle	Word	4	01-04-2016 04:18 PM
Processing Time Intervals	pkrishna	Excel	5	09-30-2011 06:24 AM