#1
|
|||
|
|||
VBA to automate spreadsheet processing
[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.
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 |
#2
|
||||
|
||||
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? |
Tags |
automate, macro, vba |
|
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 |