Solved: VBA Importing a sheet from a closed excel workbook
Hi PPL,
I am pretty new in VBA and hence would appreciate if anyone could assist me with regards to my problem.
I need a VBA code that will automatically copy and paste a sheet from a closed Excel workbook.
Is it possible? Thank you and realy appreciate :bow:: pray2:
Help to improve on the VBA code
Greetings everyone,
I have come out with a code that do almost what I want. However, this code select the entire sheets in the workbook. How do I modify this VBA to select a specific sheet to import instead of the entire sheets in the workbook?
Thanks! Really appreciate your help :D
Code:
Sub Importsheet()
Dim Importsheet As Worksheet 'Imports worksheet from a closed workbook
Sheets.Add Type:= _
"C:\Users\Leo\Desktop\Testing1.xlsx"
End Sub
Change source to a location specified in Cell A1
Quote:
Originally Posted by
shrivallabha
Hello LeoLee, assign the following code for using import. I have used a little different version for my requirements. So I'd to tweak it little. I have used a function for checking existence of a specific sheet.
Code:
Sub ImportSheet()
Dim sImportFile As String, sFile As String
Dim sThisBk As Workbook
Dim vfilename As Variant
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set sThisBk = ActiveWorkbook
sImportFile = Application.GetOpenFilename( _
FileFilter:="Microsoft Excel Workbooks, *.xls; *.xlsx", Title:="Open Workbook")
If sImportFile = "False" Then
MsgBox "No File Selected!"
Exit Sub
Else
vfilename = Split(sImportFile, "\")
sFile = vfilename(UBound(vfilename))
Application.Workbooks.Open Filename:=sImportFile
Set wbBk = Workbooks(sFile)
With wbBk
If SheetExists("Raw_Data") Then
Set wsSht = .Sheets("Raw_Data")
wsSht.Copy before:=sThisBk.Sheets("Sheet1")
Else
MsgBox "There is no sheet with name :Raw_Data in:" & vbCr & .Name
End If
wbBk.Close SaveChanges:=False
End With
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Private Function SheetExists(sWSName As String) As Boolean
Dim ws As Worksheet
On Error Resume Next
Set ws = Worksheets(sWSName)
If Not ws Is Nothing Then SheetExists = True
End Function
Hi shrivallabha,
Your code is almost perfect for me. I would just like to know how do I make the macro select a file path that is generated on the active worksheet, for example in cell A1, instead of selecting it manually. My Workbooks all have similar names, except for month changes in the name. I can find and replace this easily enough, but I want the macro to select the file path that is generated in the active worksheet.
Thanks in advance!
Copy multiple workbooks to specific sheets
Hi all,
I am having the same problem. I want to copy sheet1 from 20 closed workbooks.
I have the path in column A.
The sheets (of closed workbooks) need to be copied to this file in a specific sheet name that is already created.
The Sheet name is in Column B.
Is thiss possible?
It contains 20+ files with only ±20 rows of text.
Thank you