Total Pageviews

Wednesday, March 7, 2018

Selection of a specific sheet from a workbook

Hello Everyone,

The use case is as follows:
Data file which contains separate sheets for separate data. You need to select one sheet from all the available sheets in the file. 

In this example, 
1. User is asked to browse the data file
2. An userform shows a list of all the available sheets in the file
3. You select the required sheet and the data will be copied to your Tool workbook.

This may not be useful alone but you can combine this to your existing automation's where you are required to copy paste a data from a specific sheet in a file.

CODE in the module
===============
Sub fileOpen()
    toolWorkbook = ActiveWorkbook.Name
    On Error Resume Next
    data.Select
    data.Cells.Clear
    data.Range("A1").Select
    Dim strFileToOpen As String
    strFileToOpen = Application.GetOpenFilename(Title:="Please select an Excel file to open", FileFilter:="Excel Files *.xls* (*.xls*),")
    If strFileToOpen = "False" Then
        MsgBox "No file selected.", vbExclamation, "Status"
        Exit Sub
    Else
        Workbooks.Open Filename:=strFileToOpen
    End If
    dataWorkbook = ActiveWorkbook.Name
    frm_sheetSelect.Show
End Sub

Sub copyData()
    Dim tempName As String
    Workbooks(dataWorkbook).Activate
    ActiveSheet.Select
    
    tempName = ActiveSheet.Name
    
    Cells.Select
    Selection.Copy
    Workbooks(toolWorkbook).Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    ActiveSheet.Name = tempName
    
    Workbooks(dataWorkbook).Activate
    ActiveWorkbook.Close False
    MsgBox "Data Import Complete", vbInformation, "Status"
End Sub

CODE in the userform
===================

Private Sub UserForm_Activate()
    Dim sh As Worksheet
    Workbooks(Module1.dataWorkbook).Activate
    For Each sh In ActiveWorkbook.Sheets
        frm_sheetSelect.lb_sheetNames.AddItem sh.Name
    Next sh
End Sub

Private Sub cmd_select_Click()
    If lb_sheetNames.Value <> "" Then
        Sheets(lb_sheetNames.Value).Activate
    Else
        MsgBox "Please select the sheet (containing the raw data) from the list to continue.", vbExclamation, "Status"
        Exit Sub
    End If
    Unload Me
    Call Module1.copyData
End Sub



Please put your thoughts and comments below so that I can improve.

THANKS EVERYONE.

SUBHAJIT.


No comments: