Macro to move all sheets containing the same text to another workbook

[michellexlaiche]

Hello,

I'm trying to create a Macro that can find all sheets in my current workbook that contain the word "Closed" in cell J8, and collectively move them all to another workbook. I've figured out how to move one sheet (the active one) at at time but am struggling to move multiple ones.

From workbook: OPEN Order Status Reports
To workbook: CLOSED Order Status - 2023
Common cell is J8 that says Closed

Thank you for the help in advance!

 

[DanteAmor]

Hi and welcome to MrExcel

Put the following macro in the book that contains the sheets to move
Note: "CLOSED Order Status - 2023" This book must also be open

Note: At least one sheet must remain in the book "OPEN Order Status Reports"

Sub movesheets()
  Dim sh As Worksheet
  Dim wb As Workbook
 
  Set wb = Workbooks("CLOSED Order Status - 2023")  'This book must also be open
 
  For Each sh In ThisWorkbook.Sheets
    If sh.Range("J8").Value = "Closed" Then
      sh.Move After:=wb.Sheets(wb.Sheets.Count)
    End If
  Next
End Sub

 

[michellexlaiche]

Thank you!

I ran it and got an error message of Type Mismatch for this line: If sh.Range("J8").Value = "Closed" Then

Any idea why that is?

 

[michellexlaiche]

I think I've got it. I changed .Value to .Text and it worked. Thank you so much!