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!
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!