Log In
Register
Home
December 4, 2021
Offline
Hello all and thanks for looking! I am trying to copy my ACTIVE Sheet (named "Lists", but I would prefer it remains dynamic as "active" sheet) to all open workbooks (move to end also). The issue is I am getting this error, and I am not quite understanding it.
Option Explicit
Sub CopyToAllOpen()
Dim wb As Workbook
Dim sh As Worksheet
Set sh = ActiveSheet
For Each wb In Application.Workbooks
If Not sh.Parent Is wb Then
sh.Copy After:=wb.Sheets(wb.Sheets.Count)
End If
Next wb
End Sub
See Answer
Trusted Members
October 17, 2018
Offline
you have to se a variable that retains the file from where you're copying it since this is the 'source workbook'
Trusted Members
Moderators
November 1, 2018
Offline
You probably have a hidden workbook. Test for that before trying to copy:
Sub CopyToAllOpen()
Dim wb As Workbook
Dim sh As Worksheet
Set sh = ActiveSheet
For Each wb In Application.Workbooks
If wb.Windows(1).Visible Then
If Not sh.Parent Is wb Then
sh.Copy After:=wb.Sheets(wb.Sheets.Count)
End If
End If
Next wb
End Sub
Answers Post
December 4, 2021
Offline
@Velouria,
Perfect! The macro was residing in my Personal Workbook (which is hidden), and I carefully review the differences in the original code and your revised code. I now understand the WHY, and I thank you for that. This code is a HUGE timesaver. Thanks again so very much for your help, it is greatly appreciated!
1 Guest(s)
