VBA Code to move sheet to another workbook that will be selected by user

[Rastaman1979]

Hi

I'm searching for VBA code to move a sheet from an already open workbook to a workbook that will be selected by the user.
I'm using Excel 2010 on Windows 7

Open workbooks are: Macro workbook and Source workbook

I've searched the forum but could't find a specific answer to my question. I've tried to work with code that was available, but it won't work.

Microsoft Visual Basic Error: I get a Run-time error '1004': unable te get the Select property of the worksheet class

Short explanation what I will achieve:

1) Filter based on criteria in masterfile (= CB Total) and copy results to another workbook (D-10 today() or D-1 today())

I tried to copy filter result first into new sheet in masterfile (that works fine). Thanks to this forum.

When I try to copy the sheet with fixed pathnames and filenames it works fine. The goal is that the user had to choose to wich file the sheet must be moved. Nor Path or filename will be the same each time they run the macro.

'Active workbook is Source workbook

Sub Move Sheet()

 Dim CopyTo As Workbook
 Dim CopyFrom As Workbook
 Dim CopyToBook As String

              
    Set CopyFrom = ActiveWorkbook
    
    CopyToBook = CStr(Application.GetOpenFilename("Excel Files, *.xlsx", , "Open de D-x File"))
        If CopyToBook <> "False" Then
        
        Set CopyTo = Workbooks.Open(CopyToBook)
            
                        
        CopyFrom.Sheets("CB Impact").Select.Move After:=CopyTo.Sheets(ActiveSheet.Index)

End Sub

I get the last line in yellow. Could anyone help me out please?

 

[ParamRay]

.
.

Deleting the .Select characters out of the last line should do the trick. Here is a neater way to write it:

Sub CopySheet()
   Dim varDestination As Variant
   Dim wbkDestination As Workbook
   varDestination = Application.GetOpenFilename("Excel Files (*.xlsx),*.xlsx")
   If varDestination <> False Then
      Set wbkDestination = Workbooks.Open(varDestination)
      ActiveWorkbook.Worksheets("Sheet1").Move wbkDestination.Sheets(1)
   End If
End Sub

 

[Rastaman1979]

@ gpeacock: Thanks for answering so quickly! I will try it as soon as possible, normally monday, and close the thread.

 

[Rastaman1979]

@ gpeacock: Thanks, it works like a charm!