Solved

Copy Sheet to Workbook - Rename the Sheet tab

Posted on 2014-01-13
29
452 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
Comment Utility
Upload an example. And specify in clear steps what is required.
0
 

Author Comment

by:Cook09
Comment Utility
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
Comment Utility
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
 

Author Comment

by:Cook09
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Ok try this:
b1.xlsm using code external to b1.xlsx and b2.xlsx
b1.zip
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
@Cook09

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

Author Comment

by:Cook09
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
No problem take your time and let me know when you have something.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Any chance to have tried out ?
gowflow
0
 

Author Comment

by:Cook09
Comment Utility
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
Comment Utility
What do you want me to do with all this ???
gowflow
0
 

Author Comment

by:Cook09
Comment Utility
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
Comment Utility
... 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
Comment Utility
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
Comment Utility
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
Comment Utility
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
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
Comment Utility
@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
Comment Utility
Answer to the original question
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
This collection of functions covers all the normal rounding methods of just about any numeric value.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now