• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Copy and Paste a sheet from One Workbook to Another|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Copy and Paste a sheet from One Workbook to Another|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosCopy and Paste a sheet from One Wor…
sp_PrintTopic sp_TopicIcon
Copy and Paste a sheet from One Workbook to Another
Avatar
Kenneth McMillan
USA
Member
Members

Dashboards

Power Query
Level 0
Forum Posts: 29
Member Since:
November 15, 2017
sp_UserOfflineSmall Offline
1
November 15, 2018 - 4:21 am
sp_Permalink sp_Print

I have a main workbook as my master. I then open another workbook from an attachment in email that has a .xls file extension and the code works perfectly.
I tried it with another file to open from an attachment on email with a .xlsm file extension and that one does not copy to my master workbook.
Can anyone please tell me why that is?
Here is the code: I can provide workbooks if needed. Thank you so much
Ken McMillan

Sub Copy_Labor()
  On Error Resume Next
Dim UserInput As Variant
Dim SheetName As String
Dim SheetCount As Integer
Dim Wb As Workbook
UserInput = InputBox("For Example 'Joplin'", "Enter A Name For The Archived Sheet")
Sheets("Weekly Projections").Select
Sheets("Weekly Projections").Copy Before:=Workbooks("DM Labor Tracking.xls").Sheets(1)
Sheets("Weekly Projections").Select
If sh.Name = UserInput Then
Sheets(UserInput).Delete
End If
Sheets("Weekly Projections").Name = UserInput
SheetCount = Sheets.Count
For N = 1 To SheetCount
SheetName = Sheets(N).Name
For M = N To SheetCount
If Sheets(M).Name < SheetName Then
SheetName = Sheets(M).Name
End If
Next
Sheets(SheetName).Move Before:=Sheets(N)
Next
Sheets(UserInput).Select
Range("A1").Select
For Each Wb In Workbooks
    If Wb.Name <> ThisWorkbook.Name Then
        Wb.Close savechanges:=False
    End If
Next Wb

End Sub

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1510
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
2
November 15, 2018 - 8:33 am
sp_Permalink sp_Print

Hi Ken,

So this is the same/similar to the qs you posted recently, or different?  It's just that it's not working with a .xlsm when that xlsm file is opened from Outlook?

Can you please provide the workbooks, it always helps.

Regards

Phil

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 612
Member Since:
November 1, 2018
sp_UserOnlineSmall Online
3
November 15, 2018 - 8:07 pm
sp_Permalink sp_Print

If it works from an .xls and not from an .xlsm, I'd guess you are copying to an .xls file? If so, a sheet from an xlsm (or xlsx/xlsb) has more rows and columns than an xls workbook sheet. Perhaps you could just copy the usedrange of the sheet instead to a new sheet in the master workbook?

Avatar
Kenneth McMillan
USA
Member
Members

Dashboards

Power Query
Level 0
Forum Posts: 29
Member Since:
November 15, 2017
sp_UserOfflineSmall Offline
4
November 15, 2018 - 11:28 pm
sp_Permalink sp_Print

No Phil, this is a different spreadsheet I use for a different project. My main sheet is a .xls. The book/sheet is a .xlsm sheet. It works with a .xls file, but not .xlsm. Here is the main sheet and another stores send in each day.
Thanks very much for your help.

My main file is DM Labor Tracking. The files I will get from the stores on email(example) is Labor D52 Blank which is a .xls file that works and the xlsm file that does not work December Monthly Labor. I open the main file first and then the file from email and use a shortcut key: ctrl and z. The code copies that sheet into my master and closes the attachment on email and the I repeat the process until all sheets are processed.

Avatar
Kenneth McMillan
USA
Member
Members

Dashboards

Power Query
Level 0
Forum Posts: 29
Member Since:
November 15, 2017
sp_UserOfflineSmall Offline
5
November 15, 2018 - 11:31 pm
sp_Permalink sp_Print

Phil,
I will reduce the size of my main file so I can attach. It exceeded the amount allowed.

Avatar
Kenneth McMillan
USA
Member
Members

Dashboards

Power Query
Level 0
Forum Posts: 29
Member Since:
November 15, 2017
sp_UserOfflineSmall Offline
6
November 16, 2018 - 12:40 am
sp_Permalink sp_Print

Here is my master file and thanks again.

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1510
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
7
November 16, 2018 - 1:38 pm
sp_Permalink sp_Print sp_EditHistory

Hi Ken,

There's multiple issues here and I can't get this code to run at all.

Before I go through a few things to help there's a few things that you should do in your code always.

  • Use Option Explicit - so that all variables are declared before use.  sh is not declared or given a value but you try to test it's value
  • Don't declare variables in the middle of code.  Declare them all at the top of the SUB before you write any code.
  • Please use indentation and whitespace to make the code readable.
  • Please use comments to explain what the code is intended to do
  • Don't turn off error handling with On Error Resume Next unless write your own error handlers.  Things may appear to work but that's just because you aren't seeing the errors being generated.  
  • Don't hard code sheet/workbook names unless you REALLY have to.  This line throws an error Sheets("Weekly Projections").Copy Before:=Workbooks("DM Labor Tracking.xls").Sheets(1) because the workbook "DM Labor Tracking.xls" doesn't exist.  You sent me "DM-Labor-Tracking.xls".  This line will, of course, also throw an error when you try to copy from ANY file with a .xlsm extension.
  • You've written your own code for the IFERROR function.  You shouldn't use the same name for a UDF as an in-built function.
  • You've used the shortcut CTRL+Z for the Copy_Labor macro.  CTRL+Z is already assigned to Undo.  Don't hijack an in-built shortcut.  When you assign shortcuts to macros you can use a combination of SHIFT and CONTROL e.g. SHIFT+CTRL+Z
  • The VBA code in Labor-D52-Blank-New is password protected so I can't check it.
  • When I tried to open your workbooks I received multiple errors.

Did you try to debug this? If you don't turn off error handling and then step through the code with F8 it will reveal a lot.

Sorry, I don't mean to be brutal but with so many issues it's very hard to get started on working out where the issue lies.  But I suspect because you have hardcoded the workbook name in, "DM Labor Tracking.xls", your macro can't find that when you open another file with a different name and extension.

How many sheets do you need to copy to your master workbook?  Would it be quicker to do it by hand?  Right click on the sheet tab/name and then on Move or Copy ...

If you can have a go at fixing these things and get back to me with workbooks that I'm able to open without error I'll see what I can do.

Regards

Phil

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 612
Member Since:
November 1, 2018
sp_UserOnlineSmall Online
8
November 16, 2018 - 11:29 pm
sp_Permalink sp_Print

You can't copy a worksheet from an XML format workbook to an xls, since they have different numbers of rows and columns. You'll have to insert a new sheet to the xls workbook and then copy paste cells from the source.

Avatar
Kenneth McMillan
USA
Member
Members

Dashboards

Power Query
Level 0
Forum Posts: 29
Member Since:
November 15, 2017
sp_UserOfflineSmall Offline
9
November 17, 2018 - 12:16 am
sp_Permalink sp_Print

No worries. This code was written in the early 2000's and it worked for what I was doing at the time, but just wondered why it didn't work on a .xlsm file.
I did try to step through the code with F8, but didn't see where it was going on.

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1510
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
10
November 19, 2018 - 11:38 am
sp_Permalink sp_Print

Hi Ken,

If you were stepping through using F8, but you had error checking turned off, you wouldn't see any of the generated errors.

Cheers

Phil

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Velouria, Ben Hughes, Dario Serrati, Christopher Anderson, dectator mang, Oluwadamilola Ogun, yashal minahil
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
yashal minahil
Oluwadamilola Ogun
Yannik H
dectator mang
Francis Drouillard
Orlando Inocente
Jovitha Clemence
Maloxat Axmatovna
Ricardo Freitas
Marko Meglic
Forum Stats:
Groups: 3
Forums: 24
Topics: 6200
Posts: 27181

 

Member Stats:
Guest Posters: 49
Members: 31858
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.