First off I am new to the whoel VBA/Macro world and I am learning as I go so please be gentle... I have 3 seperate Macro functions that I would like to combine into one "click this button" and you're done process. Here is what I'm working with:
Step #1 combines data from 7 different pages into 1 page:
PHP Code:
Sub ShelmanCopyRangeVersion2() Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim CopyRng As Range
With Application .ScreenUpdating = False .EnableEvents = False End With
'Delete the sheet "CompareMacro" if it exist Application.DisplayAlerts = False On Error Resume Next ActiveWorkbook.Worksheets("CompareMacro").Delete On Error GoTo 0 Application.DisplayAlerts = True
'Add a worksheet with the name "RDBMergeSheet" Set DestSh = ActiveWorkbook.Worksheets.Add DestSh.Name = "CompareMacro" Range("A1:B1").Value = Array("Sevice Order", "Manufacturer") 'Fill in the start row StartRow = 1
'loop through all worksheets and copy the data to the DestSh For Each sh In ActiveWorkbook.Sheets(Array("SS Old Tags", "SS HP_Compaq Laptops", "SS Toshiba Laptops", "SS Gateway_Emach Laptops", "SS Sony_Misc Laptops", "SS Desktops", "SS PT", "SS PA"))
'Find the last row with data on the DestSh Last = LastRow(DestSh)
'Fill in the range that you want to copy Set CopyRng = sh.Range("A1:B500")
'Test if there enough rows in the DestSh to copy all the data If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then MsgBox "There are not enough rows in the Destsh" GoTo ExitTheSub End If
'This example copies values/formats, if you only want to copy the 'values or want to copy everything look at the example below this macro CopyRng.Copy With DestSh.Cells(Last + 1, "A") .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With
Next
ExitTheSub:
Application.Goto DestSh.Cells(1)
'AutoFit the column width in the DestSh sheet DestSh.Columns.AutoFit
With Application .ScreenUpdating = True .EnableEvents = True End With End Sub
Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function
Step #2 sorts the information into numerical order while deleting blank rows (while you're here, what's a good way to delete blanks without 'guessing' how blank rows there will be?):
PHP Code:
Sub SortRemoveBlanks2() ' ' SortRemoveBlanks2 Macro ' Macro recorded 6/2/2008 by Shelman '
And lastly step #3 takes this now combined and sorted information and "copies and pastes" to a seperate sheet with conditional formating and formulas already on the sheet and ready to go:
PHP Code:
Sub CopyCompareMacroToCompare() ' ' CopyCompareMacroToCompare Macro ' Macro recorded 6/2/2008 by Shelman '
' Columns("A:B").Select Selection.Copy Sheets("Compare").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A1").Select End Sub
How can I combine these so that they are one click and done? Any help is appreciated!
Bookmarks