troubleshooting Question

Find a worksheet within a closed workbook

Avatar of la_pepe59
la_pepe59 asked on
Visual Basic ClassicMicrosoft ExcelVB Script
5 Comments1 Solution960 ViewsLast Modified:
I am trying to use the following code to get data from a closed workbook and copy it to a sheet in the existing workbook.  In cell B2 a date will be input and based on that date, it should pull the correct worksheet from the closed workbook.  Ex.  If cell B2 = 07/02/2007 change it to a short date (Jul-07) and then find the corresponding worksheet in the closed workbook.  The wb name will be the same but the sheets be named by month and year.  Currently the name of the worksheet don't include the "-" in between the month and year so if that can be excluded, that would be great also.  Here is what I have..

Sub CopyFromClosedWB(strSourceWB As String, _
      strSourceWS As String, strSourceRange As String, _
      rngTarget As Range)
' copies information from a closed workbook, no input validation!
' use like this to copy information to the active worksheet:
' CopyFromClosedWB "C:\test.xls", "wsname", "A1:D100", Range("A1")
Dim wb As Workbook
      Application.ScreenUpdating = False ' turn off the screen updating
      Application.StatusBar = "Copying data from " & strSourceWB & "..."
      On Error Resume Next ' ignore errors
      ' open the source workbook, read only
      Set wb = Workbooks.Open(strSourceWB, True, True)
      On Error GoTo 0 ' stop when errors occur
      If Not wb Is Nothing Then ' opened the workbook
            On Error Resume Next ' ignore errors
            With wb.Worksheets(strSourceWS).Range(strSourceRange)
                  .Copy rngTarget
            End With
            On Error GoTo 0 ' stop when errors occur
            wb.Close False ' close the source workbook without saving changes
            Set wb = Nothing ' free memory
      End If
      Application.StatusBar = False ' reset status bar
      Application.ScreenUpdating = True ' turn on the screen updating
End Sub
Sub TestCopyFromClosedWB()
      CopyFromClosedWB "C:\Workbookname", "Worksheet", "B2:f55", Range("Sheet2!A1")
End Sub

I have it running when cell b2 changes.  If I input the name of the worksheet, Jul 07 it works great but I need it to be based on cell.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros