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
THANKS 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:
Post a Comment