Latest comments

  • It's alive!
    Lee
    Hi, any chance of you sharing your config code for this project.

    Read more...

     
  • It's alive!
    Lee
    Hi Danny, great piece on converting an Overlord to use an MKS Sbase. I'm replacing my Overlord MB ...

    Read more...

     
  • iHobby Expo 2008
    Alison
    Sweet blog! I found it while surfing around on Yahoo News. Do you have any tips on how to get listed ...

    Read more...

     
  • Getting back into it
    online word Counter
    Everything is very open with a very cleaar description of the challenges. It waas really informative.

    Read more...

     
  • TiVo HD upgrade
    BHW
    I blog often and I really thank you for your information. This article has truly peaked my interest.

    Read more...

Programming

New macro - Open a workbook and copy a worksheet

So lately, I've been putzing around with VBA inside Excel 2007.  And as a form of recall, I guess I'm going to start putting up snippets of code.  Here's my latest creation.

Sub SelectAndCopySheet(SourceSheet As String)
 
Dim i As Integer
Dim wsCount As Integer
Dim wbSourceName As String
 
wsCount = ActiveWorkbook.Worksheets.Count
For i = 1 To wsCount
    If ActiveWorkbook.Worksheets(i).Name = SourceSheet Then
        Application.DisplayAlerts = False
        Sheets(SourceSheet).Delete
        Application.DisplayAlerts = True
        Exit For
    End If
Next i
 
FileToOpen = Application.GetOpenFilename(Title:="Please select the report file to import")
If FileToOpen = False Then
    MsgBox "You selected cancel...", vbExclamation, "The macro is stopping here."
Else
    Workbooks.Open Filename:=FileToOpen
    wbSourceName = ActiveWorkbook.Name
    ActiveWorkbook.Sheets(SourceSheet).Copy ThisWorkbook.Sheets(1)
    Workbooks(wbSourceName).Close
End If
 
End Sub
 

So, this little bit of code is a vba sub procedure.  It is looking for a worksheet name that you somehow have to know ahead of time.  Once you feed it that bit of information:

  • It then goes through the current active workbook and cycles through the worksheet names and erases its doppelganger if it exists.  If one does exist, it suppresses any alerts Excel may toss at you. 
  • It then opens up a file dialogue box and asks you to select the excel workbook that contains the worksheet name you are looking for.
  • The macro then opens up that file, copies the desired worksheet and pastes it into the workbook that the macro was executed from.
  • Once copied it then closes the file you selected.

Baby steps on my road back to a programmer mindset.

Add comment


Security code
Refresh