Move Sheets To New Workbook

[DavCl]

Hi
I am trying to move sheets to a new workbook but I am having no luck

Sub MoveSheets()
Const ShDir As String = "C:\Users\daves\Desktop\Office\Excel\Charts Singles\"
Dim thisWB As Workbook: Set thisWB = thisWorkbook
Dim theYearBook As Workbook: Set theYearBook = Workbooks.Open(ShDir & "2020.xlsx")
Yearbkshts = theYearBook.Sheets.Count
With thisWB
.Activate
For Each sh In Sheets
sh.Activate
ActiveSheet.Move After:=Workbooks(theYearBook).Sheets(Yearbkshts) ' Error on this line
Next sh
End With
End Sub

Thank you

 

[SkipVought]

Hi DavCl,

You posted twice earlier and never responded to answers that were posted. Do you intend to interact with us as we post?

Lots of members look at these threads in order to learn more about coding VBA, so unanswered posts are a bit unsatisfying.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

With thisWB
   For Each sh In .Sheets
      sh.Move After:= theYearBook.Sheets(theYearBook.Sheets.Count) ' Error on this line
   Next sh
End With

Comments:
1. You do not need to, in fact is is counterproductive to, Activate or Select repeatedly and needlessly in a loop or anywhere else, for that matter, unless it is to arrange the final display for the user.
2. Regarding the Sheet Count in theYearBook[sub][/sub], it changes each time you add a sheet, so the variable Yearbkshts is useless.
3. In the For...Next loop, your code failed to reference thisWB. Hence, .Sheets.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

BTW, and beyond the exercise of moving sheets properly, is the question of structuring and storing data. You seem to have a series of sheets that represent similar data for a composite, in this case data for 2020. It might be daily, weekly, monthly or for some periods less than an entire year.

Than answers the WHAT but WHY? The reason for saving data is to be able to use the data at some time or times in the future.

Chopping data up into pieces like sheets, in this instance, greatly complicates many analysis tasks. You may not see it now, but this is a HUGE mistake, despite what your boss, who wants to see each month on a separate sheet, might think. When the front office wants to see a comparison of first quarter of 2020 with first quarter of 2019, you gotta do some handstands and cartwheels before you can come up with that answer from 2 workbooks and 6 worksheets, rather than ONE workbook with ONE sheet.

From someone who's been using Excel for nearly 3 decades in the aircraft manufacturing industry, chopping up data is a bad idea! The database professionals don't do it either!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein