Log In
Register
Home
Dashboards
Power Query
November 15, 2017
Offline
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
October 5, 2010
Offline
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
Trusted Members
Moderators
November 1, 2018
Online
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?
Dashboards
Power Query
November 15, 2017
Offline
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.
Dashboards
Power Query
November 15, 2017
Offline
Phil,
I will reduce the size of my main file so I can attach. It exceeded the amount allowed.
Dashboards
Power Query
November 15, 2017
Offline
Here is my master file and thanks again.
October 5, 2010
Offline
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
Trusted Members
Moderators
November 1, 2018
Online
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.
Dashboards
Power Query
November 15, 2017
Offline
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.
1 Guest(s)
