×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Move Sheets To New Workbook

Move Sheets To New Workbook

Move Sheets To New Workbook

(OP)
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

RE: Move Sheets To New Workbook

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Move Sheets To New Workbook

CODE

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

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Move Sheets To New Workbook

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close