Move sheet

[kizmet311]

I am trying to move a sheet from activeworkbook to a file that is stored in a folder. Any help is greatly appreciated, I have attached the code that i am currently trying to use. I feel that i am over thinking this.

Sub move_sheet()


'Move the active sheet to the end of named Workbook.
'Replace filepath with the full name of the target workbook you want.

Dim filepath As String

filepath = "C:\Users\Nick.Clark\Desktop\Compare.xlsx"

ActiveWorkbook.ActiveSheet.Move After:=Workbooks(filepath).Sheets(Sheets.Count)


End Sub

 

[jmacleary]

Hi there. Doesnt look to be wrong - do you get any error message?

 

[kizmet311]

I tried with both files open and still no luck. I am getting "Run-time error '9': Subscript out of range".

 

[jmacleary]

Ok OK try this change:

ActiveWorkbook.ActiveSheet.Move After:=Workbooks(filepath).Sheets(Workbooks(filepath).Sheets.Count)

 

[kizmet311]

I am still getting the Run-time error 9.

 

[jmacleary]

Sorry, I will think on and try it myself later - hopefully someone else on the forum can help further.

 

[Fluff]

Is the other workbook open?

 

[kizmet311]

I have tried this with the both workbooks open and with only the one i am moving the sheet from. If there is a different way to do this I am open. I can keep the destination file constant, but the one file will change. I appreciate the help. I am fairly new to VBA, working to automate processes for beginner excel users.

 

[Fluff]

How about

Sub kizmet311()
   Dim FilePath As String
   Dim Wbk As Workbook
   Dim Ws As Worksheet

   FilePath = "C:\Users\Nick.Clark\Desktop\Compare.xlsx"
   Set Ws = ActiveWorkbook.ActiveSheet
   Set Owbk = Workbooks.Open(FilePath)
   Ws.move After:=Wbk.Sheets(Wbk.Sheets.Count)
End Sub