Move a worsheet to another (open) workbook based on cell value

kuranui

New Member
Joined
Mar 13, 2012
Messages
4
Hi Guys,

i know theré's a very similar post on this subject, but it is intended for use when you knwo the other workbook name.
In my case, the workbook name will depend on date, client name and product. To cover this i have used some simple formulas using "concatenate" and "&" functions to get the directories and names right.

I'm having trouble trying to move sheets from a workbook to the one whose name is based on the cells.
In this case i used:

Dim cliendprodfile as String
clientprodfile = Workbooks("basefile.xlsm").Sheets(1).Range("J8").Value

With ActiveWorkbook
For i = 1 To Sheets.Count
Worksheets(i).Select
If Range("M2") = clientcell Then
ActiveSheet.Move before = Workbooks("clientprodfile").Sheets(1)
End If
Next i
End With

MAcro stops at "ActiveSheet.Move before = Workbooks("clientprodfile").Sheets(1)"

There are 3 workbooks being used here (all open at this point), one "basefile", in which i'm writing the codes; a "productfile", which is the one that has the sheets I'm trying to move (it is the ActiveWorbook in this case); and a "clientfile" which is the one i'm trying to move the sheets to, and whose name is defined by the "clientprodfile" string above. (the cell value is: Client Alfa - 31dec13.xlsm).

I´ve tried some variations such as these ones (among others), but none worked:
WorkSheets(i).Move before = Workbooks("clientprodfile").Sheets(1)
ActiveSheet.Move before = Workbooks("basedir & clientdir & clientprodfile").Sheets(1) (here trying to use the full directory; i.e. C:xxxx\xxxx\xxxxx... etc)

Any thougts?

Thanks!
 

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top