Need VBA code to Copy all on a sheet to another existing sheet

**appform** · 10-23-2010, 08:42 AM

I have a formatted worksheet “View Format” that needs to be copied to another existing worksheet “View Request” whenever the user makes a new request. Everything on the “View Format” sheet needs to be copied to the “View Requested” sheet. This resets (refresh) the “View Requested” sheet for the next user request.

I was using VBA code to delete worksheet “View Requested”, then create to copy of Worksheet “View Format” that becomes “View Format (2)” then rename it to “View Requested”.

This accomplished the refresh, but keeps incrementing the worksheet numbers in the sheet properties for “View Requested” sheet.

I would like VBA code that will copy everything on the “View Format “ sheet to overlay everything on the “View Requested “sheet. That includes Buttons, Text, Columns, Rows, etc.

Here is the code I am using to delete and copy the existing sheets.

 
Sub Replace_View_Requested_Sheet()
'
' Replace_View_Requested_Sheet Macro
' Macro created 10/22/2010 by Jim Lombardi

'
    Sheets("View Requested Format").Select
    Sheets("View Requested Format").Copy Before:=Sheets(4)
    Application.DisplayAlerts = False
    Sheets("View Requested").Select
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True

    Sheets("View Requested Format (2)").Select
    Sheets("View Requested Format (2)").Name = "View Requested"
    Range("A1").Select
End Sub

Here is the workbook:

Jaguar XJ8 Major Repair & Safety Issues Format WIP.xls

Look at "AT a Glance" worksheet, the option button entitled "Refreash View Requested" runs the macro "Replace_View_Requested_Sheet()". Looks like I misspelled Refresh in the option button text. Oh well, I will fix that later.

Thank you in advance for your help with this coding.

Jim Lombardi

**Marcol** · 10-23-2010, 09:01 AM

Hi Jim

Please use the forum facilities for uploading your workbook.

From Forum FAQs
How do I attach a file to a post?

To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'. To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

On this page, below the message box, you will find a button labelled 'Manage Attachments'. Clicking this button will open a new window for uploading attachments. You can upload an attachment either from your computer or from another URL by using the appropriate box on this page. Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and locate the file. To upload a file from another URL, enter the full URL for the file in the second box on this page. Once you have completed one of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input boxes in this window. You can then close the window to return to the new post screen.

Cheers

**appform** · 10-23-2010, 09:11 AM

I do not understand. I think I used the advanced and then manage attachment to browse, then upload, then select the worksheet after the upload, then it was inserted in the post. I also just repeated the process again now.
Hope the post is OK.

**Marcol** · 10-23-2010, 09:43 AM

I don't think you have uploaded correctly, however try this

Sub Replace_View_Requested_Sheet()
'
' Replace_View_Requested_Sheet Macro
'
    Dim strSheetName As String

    Sheets("View Requested Format").Copy Before:=Sheets(4)
    strSheetName = ActiveSheet.Name
    
    Application.DisplayAlerts = False
    Sheets("View Requested").Delete
    Application.DisplayAlerts = True

    Sheets(strSheetName).Name = "View Requested"
    Range("A1").Select
End Sub

I haven't studied your workbook in detail, but see if that works for you first.

**appform** · 10-23-2010, 11:26 AM

Thank you for your code.
It looks like your code deletes the "Review Requested" sheet.
I do not want to delete the sheet. I want to copy everything from the "Review Requested Format" sheet, then clear everthing from the existing "Review Requested" sheet, then paste everything that was copied from the "Review Requested Format" sheet to the existing "Review Requested" sheet.

I am trying to get away without deleting the "Review Requested" sheet and
without adding another new sheet that will be renamed.

I am hope this makes it a little clearer what I am trying to do.

Hope you have a solution on how to code this procedure.

**Marcol** · 10-23-2010, 12:43 PM

Is this what you need?

Sub Replace_View_Requested_Sheet()
    Sheets("View Requested").Cells.Clear
    Sheets("View Requested Format").Cells.Copy Sheets("View Requested").Cells(1, 1)
End Sub

I'm not sure what you are trying to do. It looks to me that you end up with three identical sheets.
What am I missing?

**appform** · 10-23-2010, 02:35 PM

Hi Marco
Your code is great. It cleared all the cells in the sheet. I used the debug to step thru the macro. I stopped after the clear cells on the sheet and looked at that sheet. Every cell was cleared, but the buttons were still there.
Can you add code to clear all the buttons, etc on the sheet?

I like to explain why there is 3 worksheet being used. The "At a Glance" is the user first menu.

The buttons on it let the user access the specific data the user requests by using the appropiate button (say the "Tensioner").

This runs a macro that copies the Tensioner Summary information (or any other topic that has a button with Summary information) section of the "Summary SubMenu" worksheet.

The copied Tensioner Summary info is then inserted into the "View Requested" worksheet just a couple of rows below the Tensioner caption.

The "View Request Format" is used to replace the information on the "View Requested" worksheet after the user pushed the "Return to Summary" Button (also brings the user back to "At a Glance" worksheet to make another request about another topic).

I am using the "View Requested" sheet to let the user view more information (from the Summary SubMenu sheet) about the topic.

Also the additonal Summary information that was inserted (has Buttons on it that leads to posts within the forum and the other button leads to articles on the internet.

I am trying to make this look like when you press a button the additional information pops-up under the topic that was requested by the user's request.

I came up with this work-around because I do not know enough VBA coding to do it more effiently.

Any ideas would be helpful.
I am an experienced Cobol programmer, but just a beginner using VBA.

**appform** · 10-24-2010, 10:57 AM

Hi Marco
Thank you for your excellent help. You put me on the right track. With your help and some examples from internet searches, I am now able to complete the excel application.
Here is the code that I am now using with great success:

   
Sub Return_to_Tensioner_Summary()
'
' Return_to_Tensioner_Summary Macro
' Macro created 10/21/2010 by Jim Lombardi
'
' This Macro refreshes sheet "View Request" with a copy of sheet "View Request Format"
'    and moves to Column "A" row "1", then sends the user back to the
'    "AT a Glace" Summary and into the Tensioner cell in Column "A" Row "12"
'
    Sheets("View Requested").Select
    Application.DisplayAlerts = False
    Sheets("View Requested").Cells.Clear
    ActiveSheet.Buttons.Delete
    Sheets("View Requested Format").Cells.Copy Sheets("View Requested").Cells(1, 1)
    Application.DisplayAlerts = True
    Range("A1").Select
    Sheets("At a Glance").Select
    Range("A12").Select
End Sub

**Marcol** · 10-24-2010, 11:24 AM

Sorry I did not get back to you, however solving the problem yourself is always a better way to learn.

Happy to know I at least pointed you in the right direction.

Cheers