![]() ![]() SrcRng.Copy Destination:=DstSht.Range("A" & DstRow) Step 5.4: Copying data from the input Worksheet and cocatinating with destination Worksheet. MsgBox "There are not enough rows to place the data in the Consolidate_Data worksheet." Otherwise it displays message to the user and go to the IfError. Step 5.3: Check whether there are enough rows in the ‘Consolidate_Data’ Worksheet. Finally finding Input data range in the Input Worksheet and assigning it to the ‘SrcRng ‘ object.ĮnRange = Sht.Cells(LstRow, LstCol).Address įinding Last used cell address in the Worksheet and assigned it to object EnRange. Step 5.2: Finding Last used row and Last used column in the Input Worksheet and assigning it to the objects LstRow and LstRow. Step 5.1: Finding the last row in the ‘Consolidate_Data’ Worksheet using ‘fn_LastRow ‘ function. If it is not equal to then it copies the input data and concatenate to Consolidated Worksheet.įor Each Sht In ActiveWorkbook.Worksheets If it is equal then it is going to check next worksheet. Step 5: It is lLooping through each(or all) WorkSheet in the workbook.Īnd if statement is checking the Input sheet(Input Data) and destination sheet(Consolidated Sheet) is equal or not. Sheets.Add(After:=.Sheets(.Sheets.Count)) ![]() And finally it is assigned it to object (DstSht). Step 4: Adding a new WorkSheet at the end of the Worksheet. And Display Alerts is used to stop popups while deleting Worksheet.ĪctiveWorkbook.Sheets("Consolidate_Data").Delete Step 3: Deleting the ‘Consolidate_Data’ Worksheet if it exists in the Workbook. Step 2: Disable Screen Updating is used to stop screen flickering and Disable Events is used to avoid interrupted dialog boxes / popups. Now, let us see the code for each step: Step 1: Declaring variables which are using in the entire project.ĭim Sht As Worksheet, DstSht As Worksheetĭim LstRow As Long, LstCol As Long, DstRow As Long Note: We will be creating two user defined functions which we will be using In the steps 5 to find last row and last columns.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |