Advance Excel

Welcome to Advance Excel page where you will get all the important tutorials macros and many more tips and tricks to skill up yourself..

Advanced Excel

Advanced Excel Tricks and Tips

Simple Macro Code for Collating Data:

                    Sub simpleXlsMerger()
                    Dim bookList As Workbook
                    Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
                    Application.ScreenUpdating = False
                    Set mergeObj = CreateObject("Scripting.FileSystemObject")

                    'Change folder path of Excel files here
                    Set dirObj = mergeObj.Getfolder("C:\Users\chetanc\Desktop\Collate\Collate")
                    Set filesObj = dirObj.Files
                    For Each everyObj In filesObj
                    Set bookList = Workbooks.Open(everyObj)

                    'Change "A2" with cell reference of start point for every file here
                    'For example "B3:IV" to merge all files starting from columns B and rows 3
                    'If your files use more than the IV column, change it to the latest column
                    'Also change "A" column on "A65536" to the same column as the start point
                    Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
                    ThisWorkbook.Worksheets(1).Activate
                    'Do not change the following column. It's not the same column as above
                    Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
                    Application.CutCopyMode = False
                    bookList.Close
                    Next
                    End Sub

Download it here

Advanced Macro for data collate Advance macro for data collate, extreamly advanced vba macro only need execution for collate the data from different sheets in one sheet.

Download it here

                    Merge multiple sheets into one
                    Select and paste multiple sheets in one sheet (Merge)

                    Public Sub Consolidate()

                    Dim i As Integer

                    For i = 1 To Worksheets.Count - 1

                    Worksheets(i).Select
                    Range('a2').Select
                    Range(Selection, Selection.End(xlDown)).Select
                    Range(Selection, Selection.End(xlToRight)).Select
                    Selection.Copy

                    Worksheets('report').Select
                    Range('A1048576').Select
                    Selection.End(xlUp).Select
                    ActiveCell.Offset(1, 0).Select
                    ActiveSheet.Paste

                    Next i

                    End Sub

Download it here

                Randomization
                Randomization between character text or number
                Random Number 100 - 500  :  =RANDBETWEEN(100,500)
                Random Number Decimal 1 - 50 : =RANDBETWEEN(1*10,50*10)/10
                Random Dates 1-Jul-2021 to 31-Jul-2021 : =RANDBETWEEN(DATEVALUE("1-July-2021"),DATEVALUE("31-July-2021"))
                Random time between 9:00 AM to 6:30 PM : =TIME(9,0,0)+RAND()*TIME(18,30,0)-TIME(9,0,0)
                Random Alphabet between A to Z : =CHAR(RANDBETWEEN(CODE("A"),CODE("Z")))
                Random text string/ password :  =RANDBETWEEN(0,9)&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(33,47))
                Random Names :  =CHOOSE(RANDBETWEEN(1,9),$A$26,$A$27,$A$28,$A$29,$A$30,$A$31,$A$32,$A$33,$A$34)

                Sub Get_Data_From_File()
                Dim FileToOpen As Variant
                Dim OpenBook As Workbook
                Application.ScreenUpdating = False
                FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
                If FileToOpen <> False Then
                Set OpenBook = Application.Workbooks.Open(FileToOpen)
                OpenBook.Sheets(1).Range("A1:E20").Copy
                ThisWorkbook.Worksheets("SelectFile").Range("A10").PasteSpecial xlPasteValues
                OpenBook.Close False
                End If
                Application.ScreenUpdating = True
                End Sub

Download it here

                Sub selctRange1()
                Range("A2").Select
                Range(Selection, Selection.End(xlToRight)).Copy
                Range("I2").PasteSpecial
                End Sub

                Sub Selector()
                Range("A2").End(xlToRight).Select
                ActiveCell.End(xlToDown).Select
                Range(Selection, Selection.End(xlToLeft)).Copy
                End Sub

                Sub CopyRna()
                Dim i As Integer
                i = Range("A50000").End(xlUp).Row
                MsgBox (i)

                Range("A2: F" & i).Copy
                Sheets("Dash").Select
                Range("A50000").End(xlUp).Offset(1, 0).PasteSpecial

                End Sub

Download it here