CODEWITHSUNDEEP
excelvba
Alex Webb
Alex Webb

Reputation: 55

How to use filename to get back to workbook

I am taking tabs out of a master workbook and creating separate workbooks with them. I then go back to the original master workbook and loop through all the tabs except my master tab. I can put the master workbook file name and go back to it no problem but the master filename can change each time. I need to take the filename from the master workbook and pass it into my VBA code to reference back to it. I think I'm close but not quite there

Sub Macro4()

Dim WB As Workbook
WB = GetThisWB2
Dim WSCount As Integer
WSCount = Worksheets.Count


Dim allsheets As Integer                
allsheets = WSCount                     
Do While allsheets > 1

Sheets(allsheets).Select
Sheets(allsheets).Move
''----Windows("FILENAME").Activate this works
WB.Activate

allsheets = allsheets - 1                  
Loop

End Sub

Function GetThisWB()

GetThisWB = ThisWorkbook.Path & "\" & ThisWorkbook.Name
GetThisWB2 = ThisWorkbook.Name

End Function

Upvotes: 0

Views: 75

Answers (1)

Stupid_Intern
Stupid_Intern

Reputation: 3450

Based on what you have described I think you are trying to do something like this:

Sub test()
Dim wK as worksheet

For each wK in thisworkbook.Worksheets
  if wK.Name <> "Master" then

      wk.copy
      Activeworkbook.saveas thisworkbook.path & "\" & wk.name & ".xlsx"
      Activeworkbook.close true

  End if
Next wK

Msgbox "Process Completed"
End Sub

Upvotes: 1

Related Questions