Skip to main content

VBA Coding - For Making Consolidated Data sheet by using MultipleSheets data (MultipleSheets डेटा का उपयोग करके समेकित डाटा बनाने के लिए)

VBA Coding - For Making Consolidated Data sheet by using MultipleSheets data
 (MultipleSheets डेटा का उपयोग करके समेकित डाटा बनाने के लिए)



Sub For_Mastersheet_Update()

Dim wb As Workbook
Dim rng As Range


Set wb = Workbooks("Pk_File.xlsm")

wb.Activate

Worksheets("Master").Activate
Worksheets("Master").Cells.ClearContents

Worksheets("XYZ2").Activate

Worksheets("XYZ2").Range("a1").CurrentRegion.Select  ' For Select Current Region or All Data
Worksheets("XYZ2").Range("a1").CurrentRegion.Copy    ' For Copy Select Data
Worksheets("Master").Activate                        ' For activate sheet where you want to copy data
Worksheets("Master").Range("a1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats  ' Paste Copy Data
Selection.Columns.AutoFit                            ' For Selected Columns Auto Fit

'For Next Sheet Active,Select,Copy & Active Master sheet With go to End Down Cell command & down one cell more and Paste & Autofit

Worksheets("XYZ1").Activate

Worksheets("XYZ1").Range("a2").CurrentRegion.Select  ' For Select Current Region or All Data
Worksheets("XYZ1").Range("a2").CurrentRegion.Copy

Worksheets("Master").Activate

With wb.Worksheets("Master").Range("a1").End(xlDown).Select                 ' For go to END Down cell
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats   ' Down One Cell and PasteValue with Number Format
Selection.Columns.AutoFit

End With

'For Next Sheet Active,Select,Copy & Active Master sheet With go to End Down Cell command & down one cell more and Paste & Autofit

Worksheets("XYZ7").Activate

Worksheets("XYZ7").Range("a2").CurrentRegion.Select  ' For Select Current Region or All Data
Worksheets("XYZ7").Range("a2").CurrentRegion.Copy

Worksheets("Master").Activate

With wb.Worksheets("Master").Range("a1").End(xlDown).Select                 ' For go to END Down cell
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats   ' Down One Cell and PasteValue with Number Format
Selection.Columns.AutoFit

End With

'For Next Sheet Active,Select,Copy & Active Master sheet With go to End Down Cell command & down one cell more and Paste & Autofit

Worksheets("XYZ4").Activate

Worksheets("XYZ4").Range("a2").CurrentRegion.Select  ' For Select Current Region or All Data
Worksheets("XYZ4").Range("a2").CurrentRegion.Copy

Worksheets("Master").Activate

With wb.Worksheets("Master").Range("a1").End(xlDown).Select                 ' For go to END Down cell
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats   ' Down One Cell and PasteValue with Number Format
Selection.Columns.AutoFit

End With

'For Next Sheet Active,Select,Copy & Active Master sheet With go to End Down Cell command & down one cell more and Paste & Autofit

Worksheets("XYZ3").Activate

Worksheets("XYZ3").Range("a2").CurrentRegion.Select  ' For Select Current Region or All Data
Worksheets("XYZ3").Range("a2").CurrentRegion.Copy

Worksheets("Master").Activate

With wb.Worksheets("Master").Range("a1").End(xlDown).Select                 ' For go to END Down cell
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats   ' Down One Cell and PasteValue with Number Format
Selection.Columns.AutoFit

End With

'For Next Sheet Active,Select,Copy & Active Master sheet With go to End Down Cell command & down one cell more and Paste & Autofit

Worksheets("XYZ6").Activate

Worksheets("XYZ6").Range("a2").CurrentRegion.Select  ' For Select Current Region or All Data
Worksheets("XYZ6").Range("a2").CurrentRegion.Copy

Worksheets("Master").Activate

With wb.Worksheets("Master").Range("a1").End(xlDown).Select                 ' For go to END Down cell
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats   ' Down One Cell and PasteValue with Number Format
Selection.Columns.AutoFit

End With

'For Next Sheet Active,Select,Copy & Active Master sheet With go to End Down Cell command & down one cell more and Paste & Autofit

Worksheets("XYZ9").Activate

Worksheets("XYZ9").Range("a2").CurrentRegion.Select  ' For Select Current Region or All Data
Worksheets("XYZ9").Range("a2").CurrentRegion.Copy

Worksheets("Master").Activate

With wb.Worksheets("Master").Range("a1").End(xlDown).Select                 ' For go to END Down cell
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats   ' Down One Cell and PasteValue with Number Format
Selection.Columns.AutoFit

End With

'For Next Sheet Active,Select,Copy & Active Master sheet With go to End Down Cell command & down one cell more and Paste & Autofit

Worksheets("XYZ5").Activate

Worksheets("XYZ5").Range("a2").CurrentRegion.Select  ' For Select Current Region or All Data
Worksheets("XYZ5").Range("a2").CurrentRegion.Copy

Worksheets("Master").Activate

With wb.Worksheets("Master").Range("a1").End(xlDown).Select                 ' For go to END Down cell
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats   ' Down One Cell and PasteValue with Number Format
Selection.Columns.AutoFit

End With

'For Next Sheet Active,Select,Copy & Active Master sheet With go to End Down Cell command & down one cell more and Paste & Autofit

Worksheets("XYZ8").Activate

Worksheets("XYZ8").Range("a2").CurrentRegion.Select  ' For Select Current Region or All Data
Worksheets("XYZ8").Range("a2").CurrentRegion.Copy

Worksheets("Master").Activate

With wb.Worksheets("Master").Range("a1").End(xlDown).Select                 ' For go to END Down cell
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats   ' Down One Cell and PasteValue with Number Format
Selection.Columns.AutoFit

End With

'For Next Sheet Active,Select,Copy & Active Master sheet With go to End Down Cell command & down one cell more and Paste & Autofit

Worksheets("XYZ8").Activate

Worksheets("XYZ8").Range("a2").CurrentRegion.Select  ' For Select Current Region or All Data
Worksheets("XYZ8").Range("a2").CurrentRegion.Copy

Worksheets("Master").Activate

With wb.Worksheets("Master").Range("a1").End(xlDown).Select                 ' For go to END Down cell
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats   ' Down One Cell and PasteValue with Number Format
Selection.Columns.AutoFit

End With

'For Next Sheet Active,Select,Copy & Active Master sheet With go to End Down Cell command & down one cell more and Paste & Autofit

Worksheets("XYZ").Activate

Worksheets("XYZ").Range("a2").CurrentRegion.Select  ' For Select Current Region or All Data
Worksheets("XYZ").Range("a2").CurrentRegion.Copy

Worksheets("Master").Activate

With wb.Worksheets("Master").Range("a1").End(xlDown).Select                 ' For go to END Down cell
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats   ' Down One Cell and PasteValue with Number Format
Selection.Columns.AutoFit

End With

'For Next Sheet Active,Select,Copy & Active Master sheet With go to End Down Cell command & down one cell more and Paste & Autofit

Worksheets("XYZ12").Activate

Worksheets("XYZ12").Range("a2").CurrentRegion.Select  ' For Select Current Region or All Data
Worksheets("XYZ12").Range("a2").CurrentRegion.Copy

Worksheets("Master").Activate

With wb.Worksheets("Master").Range("a1").End(xlDown).Select                 ' For go to END Down cell
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats   ' Down One Cell and PasteValue with Number Format
Selection.Columns.AutoFit

End With

'For Next Sheet Active,Select,Copy & Active Master sheet With go to End Down Cell command & down one cell more and Paste & Autofit

Worksheets("XYZ11").Activate

Worksheets("XYZ11").Range("a2").CurrentRegion.Select  ' For Select Current Region or All Data
Worksheets("XYZ11").Range("a2").CurrentRegion.Copy

Worksheets("Master").Activate

With wb.Worksheets("Master").Range("a1").End(xlDown).Select                 ' For go to END Down cell
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats   ' Down One Cell and PasteValue with Number Format
Selection.Columns.AutoFit

End With


'For Delete Filter Rows except Heading

With Worksheets("Master").Range("a2:p500")
.AutoFilter field:=1, Criteria1:="Date"
.EntireRow.Delete

Worksheets("Master").Range("a1").CurrentRegion.Select  ' For Select Current Region or All Data
Selection.Columns.AutoFit                              ' For Columns Auto Fit

' Table Creation for current selection

Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, Selection, xlYes)
tbl.Name = "Master"


End With


End Sub


Labels:

Comments

Post a Comment

Your advice or suggestions will be much appreciated and welcomed....

Popular posts from this blog

What is Excel ? एक्सेल क्या है ?

Excel is a spreadsheet application which have rows and columns to organize data. We can easily organize and analysis data in excel. एक्सेल एक स्प्रेडशीट एप्लिकेशन है जिसमें डेटा व्यवस्थित करने के लिए पंक्तियां और कॉलम हैं। हम एक्सेल में आसानी से डेटा व्यवस्थित और विश्लेषण कर सकते हैं।
16 comments
Read more

VBA Code to Clear Cells with Zero शून्य के साथ सेल को साफ़ करने के लिए VBA कोड

# VBA Code to Clear Cells with Zero:- To  clear cells with zero within a cell range using VBA , use a macro with the following statement structure: शून्य के साथ सेल को साफ़ करने के लिए VBA कोड For   Each   Cell  In   Range If   Cell.Value = myValue  Then   Cell.Clear Next   Cell VBA Statement Explanation Lines #1 and #3: For Each Cell In Range | Next Cell Item:  For Each… In… Next. VBA Construct:  For Each… Next statement. Description:  The For Each… Next statement repeats the statement within the loop (line #2) for each element (Cell) in the cell range (Range) you want to search for zeroes in. Item:  Cell. VBA Construct:  Element of the For Each… Next statement and object variable of the Range object data type. Description:  The Element of the For Each… Next statement is an object variable used to iterate through the elements (Cell) of the cell range (Range) you want to sear...
Post a Comment
Read more

VBA Coding : For Sharing data by Email With Attachment using Excel data in other Place

' We have some data in Excel that we want to share by email with attachment हमारे पास Excel में कुछ डेटा हैं जिन्हें हम अनुलग्नक के साथ ईमेल द्वारा साझा करना चाहते हैं Sub For_GSTR3B_Report_Share() Dim sWorkbook1 As Workbook Dim wb As Workbook Dim objApp As Object  'For New Workbook Dim ws As Worksheet Dim savepath As String         'Delete all file from folder temp2         On Error Resume Next         Kill "C:\Users\PRAMOD.GARG\Desktop\pk_temp*.*"         On Error GoTo 0               On Error Resume Next         Set wb = Workbooks("Pkgarg_Control_Sheet_With_Gaurav_Coding.xlsm")         On Error GoTo 0         If Not wb Is Nothing Then         MsgBox "It's open"         wb.Activate         Worksheets("For_GSTR3B_Report_Share...
Post a Comment
Read more