Solved

Copy Sheet to Workbook - Rename the Sheet tab

Posted on 2014-01-13
29
487 Views
Last Modified: 2014-01-28
There are two workbooks that are downloaded daily from a third party application.  The first workbook is named Error_Log_Report[1].xls, by that application.  This workbook 1, contains the main part of the information needed. A macro has already been written that will reformat and rename Sheets 1-4 (only four are listed and/or visible).

The second workbook that is downloaded is also named Error_Log_Report[1].xls and has the same number of sheets with similar information.   However, the only sheet of interest is Sheet1.  What is the best way to:

1.Copy this Sheet1 to the first workbook as the last sheet in the workbook
2. Rename it to a day prior to "today" with the following format - "mm-dd PM"
3. Delete this second workbook, keeping only the workbook that now has five sheets within it

Sheet Tab Name example for the copied sheet into workbook 1:  
If today is 01-14, then the sheet tab would be name 01-13 PM

As the downloading is manually done, the focus of the workbooks can be set, if that will help, as they both have the same name.
0
Comment
Question by:Cook09
  • 13
  • 13
  • 3
29 Comments
 
LVL 30

Expert Comment

by:hnasr
ID: 39778419
Upload an example. And specify in clear steps what is required.
0
 

Author Comment

by:Cook09
ID: 39779075
The steps should be pretty clear.  They are ordered 1, 2, and 3.  How much clearer do you need the steps have to be?  

The three workbooks attached should provide an example.  The only item not shown, is that in the original download the "1" is placed in [ ]'s.  Example: Error_Log[1].xls
Error-Log-Report-1-.xls
Error-Log-Report-1-.xls
Error-Log-Report-2-.xls
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39782510
I can fix you this as long as the file that contains the macro is not the same name of a file we want to open !!!
Having 2 files with the same name create a problem when opening at the same time.

Would it be a problem for you if we put the macro say in a file that is called Main.xlsm and then you may add every day to it the Sheet1 of the selected workbook and will rename it to "mm-dd PM"

Let me know.
gowflow
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:Cook09
ID: 39782778
goflow-
Both files would already be open when the macro is run. The code does not have to find these files. The macro would probably reside in my Personal.xlsb workbook, as is the where the reformatting code is now.  When pressing the "Switch Windows" button, the Primary file is listed as 1 (opened first) and the secondary listed as 2.

Although, I have noticed that when the worksheet is being copied, the choices that appear in the dropdown, are actually from the first workbook, which is odd.  It would seem logical that the workbook that has the single worksheet that needs to be copied contains the sheets of that workbook, as it is in the focus window when the macro is run.

So, I guess that one check could be that if Sheet4 is named Sheet4 then that is the correct workbook to transfer the Posted Late spreadsheet (codename Sheet1), or that if the first sheet of the active workbook is named "Posted Late (Today - 1 day) PM," then it is copied to the end of the non-active Primary workbook.

Hope this helps....
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39783175
Here is the code in a command button on Workbook1
I am using b1.xlsm and b2.xlsx.

Private Sub CommandButton1_Click()
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim b2name As String
    Dim sh1name As String
    
    Set wb1 = Workbooks("b1.xlsm")
    Set wb2 = Workbooks("b2.xlsx")
    
    b2name = "D:\" & wb2.Name
    
    Set sh2 = wb2.Worksheets("Sheet1")
    wb2.Activate
    sh2.Select

    ActiveSheet.UsedRange.Select
    Selection.Copy
    
    sh1name = Month(Now) & "-" & Day(Now) - 1 & " PM"
    wb1.Activate
    Worksheets.Add After:=Worksheets(4)
    ActiveSheet.Name = sh1name
    Set sh1 = wb1.Worksheets(sh1name)
    sh1.Paste
    wb2.Close False
    Kill b2name
End Sub

Open in new window

0
 

Author Comment

by:Cook09
ID: 39783516
hnsar-
I can't really use a commandbutton as each sheet is created seperately everyday.  It needs to be external to both sheets.

Cook
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39783759
Ok try this:
b1.xlsm using code external to b1.xlsx and b2.xlsx
b1.zip
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39784744
Hi Cook09

I have respected the spirit of your question and this macro will look for open workbooks and check if you have less than 3 opened it will let you know and not run (3 for the current which can also be your Personal.xlsb)

Once all ok then, it will copy as requested Sheet1 to the current workbook that  will be determined by the existence of Posted Late worksheet as last sheet with a date = to previous day & PM. It will close the From workbook and will keep the new workbook for you to check.

Please test this macro and let me know if it fits your need.

PS Please be clear that this macro (as per your instructions) WILL NOT ask you for a file to open as it explicitly confirmed that the files are opened already in 2 windows. If otherwhyse if required we can modify the code. It assumes as you mentioned that the files are already opened.
gowflow
CopySheet1toActiveWB.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39786188
@Cook09

Did you happen to try the solution I posted above ?
gowflow
0
 

Author Comment

by:Cook09
ID: 39786379
Hi gowflow,

Maybe part of the issue is that you may not have all of the code that I use, and parts that we worked on together.

When I had the two worksheets open and ran your last code, received an error toward the beginning - setting temp files I think. The two worksheets, if downloaded one after another would have the same name. I did have to change the name of the second one to just xxxx 1.xls, which is the raw unformatted data that is received.  This file, is the one where only Sheet 1 is reformatted and is the one that will be copied to the one named xxxx(1).xls - 42kb.  This one has been reformatted through the code provided in the ErrReport.docx.

The current process is to open today's Error log xxxx(1) - 42kb and make a call to "Sub ErrReport."  The second one xxxx 1.xls - 41kb is pulled from one day prior.  To reformat this one, the Macro button associated with "Sub PostedLate" is made. These are in the ErrReport.docx.

Some of the code is rather crude,  but segmented in certain cases for flexibility.  In thinking about this, the first one could be exported and reformatted with the current date, and then the second one exported, with a simultaneous reformat and copy/paste into the first one.

Part of the code in the ErrReport.docx has been modified in attempt to copy/paste, but is not fully functional.  But, you have what I'm seeing, after the first one, or "today's" has been reformatted, waiting to do the second and then copy Sheet1 over.

Cook
ErrReport.docx
Error-Log-Report-1-.xls
Error-Log-Report-1.xls
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39786395
Well just notice you renamed the file reason why you had all the trouble.

Please give me the first part of the file that will remain the same.
I looked for this sequence.
error-log-report

Please give me a sequence that is not changed

regds.gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39786404
for my macro to work you should have at least the 2 files name
error-log-report

you can have something like this:
error-log-report1.xls
error-log-report2.xls

try this and the macro should work fine. It does not matter which one is which the macro will find itself.
gowflow
0
 

Author Comment

by:Cook09
ID: 39786774
gowflow
Attached is a document that has the report title that appears on every report downloaded to Excel.
Cook
Report-Title.docx
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39786805
yes it says
Report_Log_Error[1].xls

what is the problem ???
I said to try my macro make sure both files have at least Report_Log_Error in their name and the macro will work fine.

to test it save in a directory both files and open them and run the macro I send you and check the result it should work.

Now adapting the macro to your xlsb personal workbook is a total different ball game you will need to post your personal.xlsb file with all the code and I will incorporate this macro in it.

gowflow
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39786832
OOOOPPPPSSSS !!!!!

I just saw the typeo !!!!!
Your question you have:

The first workbook is named Error_Log_Report[1].xls, by that application ....

and now in fact it is:
Report_Log_Error[1].xls

You should be careful not to make typo like this.

Here is this version that is inline with your files not with the text you mentioned your files to be !!!!

gowflow
CopySheet1toActiveWB-V01.xlsm
0
 

Author Comment

by:Cook09
ID: 39789338
goflow-
Have a lot going on at work today...will test as soon as possible.  Didn't want to leave you hanging, but there is a lot going on that needs immediate attention.

Cook
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39789480
No problem take your time and let me know when you have something.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39795026
Any chance to have tried out ?
gowflow
0
 

Author Comment

by:Cook09
ID: 39803253
gowflow-
Tried to use it, but it errored out on:
        '---> Copy Sheet1 to ThisWB
Set WSTemp = FMWB.Worksheets(1)

Open in new window


I tried to change a few items around, but could not get it to work.  Given the deadline that was at hand, stayed up late on Tuesday, it was due Wednesday and modified the format portion to do the copy worksheet.  There was also an issue with how others wanted to use it, load one sheet preform the reformat, then load the other and run the macro, which then copied that sheet to the first.


There was also an issue with saving the .xls file as a .xlsx file.  It couldn't be done as is normally done in VBA, I had to run the Macro Recorder and copy exactly as it showed. Then the file saveas worked properly.

Cook

Attached is the Word.doc that has the code that was eventually put together.  Not elegant by any means, but it does work.  What this does is reformat the main worksheet and stops. The secondary worksheet is then loaded and the macro is run again.
Format-Copy-Page.docx
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39803321
What do you want me to do with all this ???
gowflow
0
 

Author Comment

by:Cook09
ID: 39809514
Gowflow-
At this point probably nothing. The deadline was met and it works... for this part. This is one piece of a larger project, which I will need your help. The attached files are only to illustrate how some of the code had to be written..the Saveas. The export had to be deliberate in some manner to force one to use the long command.  It would seem to be such that one would have to go back to the company and pay for any changes.

However, there were parts of your code that provided ideas on how to write and integrate the copy sheet into the formatting. And that was helpful. I'll probably accept the last code for my solution. I was in the process of using it when they changed the scope, which meant a change in the code itself. The beneficial aspect as well, was if things just totally fell apart, I knew there was an Expert to provide guidance. That helped in trying to write a solution.

Cook
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39810243
... yes and how all this is translated to this question ?
Do you still need help on this question or you can close it ?

I appreciate all your comments on how to write a code, but I am here to provide solution based on questions asked. In this case I believe you asked a question to which I provided an answer that worked 100%. Now if on your side things have changed and the purpose was different this should not mean that I did not provide a solution for your question.

All this being said, I would appreciate this question be closed properly if no more help is needed within the scoop of the initial question and I am ready to assist you in any other question you may need help with, simply put a link in here and will assist.

Regard.
gowflow
0
 

Author Comment

by:Cook09
ID: 39813077
gowflow,

Yes, you are correct.  Based upon what was initially proposed, you provided the correct solution.  And yes, I do have another one, that is also time sensitive, by Wednesday.  This is actually one that you provided a solution for, but it may need a bit of tweaking, or it's not being run correctly.

The database project of selecting files within a folder and the code pulling the bolded rows and placing them within a static database.  I'm attempting to do this with just one file, so each area can be sorted and be copied by city into a Word .doc.  I'm having some issues with it, but I'll open another question and post link here.

New Question:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28349395.html

Cook
0
 

Author Comment

by:Cook09
ID: 39813550
I've requested that this question be closed as follows:

Accepted answer: 0 points for Cook09's comment #a39803253

for the following reason:

Provided a solution that works within the parameters given.  Thanks for your efforts.

Cook
0
 

Author Comment

by:Cook09
ID: 39813200
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39813551
Good Day,

I think the asker have closed this question by mistake as he acknowledged that I gave him the correct solution in his last comment however he awarded himself 0 points leaving me with no points for this question.

Tks your assistance in correcting this issue.
gowflow
0
 

Author Comment

by:Cook09
ID: 39814062
Gowflow is correct, he provided exactly what was originally requested, and even offered to provide additional assistance incorporating it into my Personal.xlsb. While my scope changed, so did this question, however, it does have value within another area of need. The intent, along with the "A," was to award the 500 points to him, despite how incorrectly the question was closed.

Best Regards,
Cook
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39814890
@Cook09
I think the question has been opened again, and you can choose the proper closure. Select on the item that you feel is the correct solution then attribute the points.
gowflow
0
 

Author Closing Comment

by:Cook09
ID: 39815878
Answer to the original question
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question