Link to home
Start Free TrialLog in
Avatar of Cook09
Cook09Flag for United States of America

asked on

Copy Sheet to Workbook - Rename the Sheet tab

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.
Avatar of Hamed Nasr
Hamed Nasr
Flag of Oman image

Upload an example. And specify in clear steps what is required.
Avatar of Cook09

ASKER

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
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
Avatar of Cook09

ASKER

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

Avatar of Cook09

ASKER

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

Cook
Ok try this:
b1.xlsm using code external to b1.xlsx and b2.xlsx
b1.zip
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
@Cook09

Did you happen to try the solution I posted above ?
gowflow
Avatar of Cook09

ASKER

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
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
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
Avatar of Cook09

ASKER

gowflow
Attached is a document that has the report title that appears on every report downloaded to Excel.
Cook
Report-Title.docx
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
ASKER CERTIFIED SOLUTION
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Cook09

ASKER

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
No problem take your time and let me know when you have something.
gowflow
Any chance to have tried out ?
gowflow
Avatar of Cook09

ASKER

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
What do you want me to do with all this ???
gowflow
Avatar of Cook09

ASKER

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
... 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
Avatar of Cook09

ASKER

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:
https://www.experts-exchange.com/questions/28349395/Provide-a-Copy-Paste-of-One-Worksheet-into-another-with-criteria.html

Cook
Avatar of Cook09

ASKER

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
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
Avatar of Cook09

ASKER

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
@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
Avatar of Cook09

ASKER

Answer to the original question