-4

I have an excel sheet which contains tables, graph etc. i need to have same sheet duplicated in same workbook or another workbook. This is to allow printing for users. Only the formatting of duplicated sheet will be changed rest all remain same from the source. Hope my question is clear. Thank you for the help in advance.

Community
  • 1
  • 1
Sam
  • 1
  • 3
  • Are you struggling with a particular aspect of this? It’s not clear what you are asking for help with. – Alex P Feb 12 '18 at 07:58
  • right-click the worksheet tab and choose Move or Copy. Record yourself doing this for a startpoint of code, –  Feb 12 '18 at 07:59
  • This will spoil the print format of the target file. – Sam Feb 12 '18 at 13:22
  • @Sam You might have the wrong idea; Stack Overflow is a place for professional (or enthusiast) programmers to share advice when a problem's solution **can't be found elsewhere**. This is neither a free code-writing service nor a tutorial site, if you want a coding service I suggest searching online for Freelancers and paying them accordingly. Please read in [help center](https://stackoverflow.com/help/on-topic) how to best ask questions in order to get quick and useful answers on StackOverflow. – Miguel_Ryu Feb 13 '18 at 08:20
  • @Sam Furthermore, although your question received answer to try and help you I suggest you read these before your next question [How to Ask](https://stackoverflow.com/questions/how-to-ask) and [Minimal, Complete, and Verifiable Example](https://stackoverflow.com/help/mcve). – Miguel_Ryu Feb 13 '18 at 08:20

4 Answers4

0

='{sheet name}'!{cell ref}

Replace {sheet name} with the actual sheet name and {cell ref} with something like a1

To copy everything in the sheet check the following link;

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/sheets-copy-method-excel

LJ01
  • 589
  • 4
  • 11
  • This will not copy the chart in the source sheet – Sam Feb 12 '18 at 08:00
  • And lot of "0" in empty space. This is a huge file. – Sam Feb 12 '18 at 08:02
  • But this is creating new sheet each time when i run macros. Moreover i need to run macros each time to get the updated sheet. – Sam Feb 12 '18 at 08:27
  • There is a sheets.delete too. You will need to re-run the macro or refresh formulas (depending on your approach) to get the updated sheet under any circumstance – LJ01 Feb 12 '18 at 09:00
  • The print sheet shall have a separate print format. Therefore only data should come to target sheet without spoiling the pre-set print format. Is there any option. – Sam Feb 13 '18 at 05:13
  • Another possible approach that may solve your problem is picking tables and charts. You might be able to put a slicer in the source sheet that updates the print sheet – LJ01 Feb 13 '18 at 09:08
0
  1. Start recording macro
  2. Copy source sheet
  3. Change format in new sheet
  4. Stop recording macro
Egan Wolf
  • 3,533
  • 1
  • 14
  • 29
0

The below code is an example of what you can do.

Copy original sheet into a new workbook and format the sheet to your desire.

Sub CopySheetToNewAndFormat()

    'copy sheet 1
    ThisWorkbook.Sheets("Sheet1").Copy
    'set new workbook
    Set NewWorkbook = ActiveWorkbook

    With NewWorkbook
        With .Sheets("Sheet1")

            'formatting example with range
            With .Range(.Cells(1, 2), .Cells(5, 5))
                'borders
                .Borders(xlEdgeBottom).LineStyle = xlContinuous
                .Borders(xlEdgeBottom).Weight = xlThin
                'aligment
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter

                'color
                With .Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorAccent1
                    .TintAndShade = 0.799981688894314
                    .PatternTintAndShade = 0
                End With
            End With
        End With
    End With

End Sub

For more formatting options you could record a macro while formatting the sheet manually and see the resulting code/references.

Edit: Because you might be recording a macro to check formatting option please have a read through this - How to avoid using select in excel-VBA

Miguel_Ryu
  • 1,390
  • 3
  • 18
  • 26
  • This is creating new workbook each time when i run the VB. My requirement is - When i update some cell in eg-Sheet1(source) can this get updated in eg-sheet2(target). sheet2 format shall be adjusted for printing. – Sam Feb 12 '18 at 10:57
-2

There are many ways to do it. On iOs make a copy, go to File, click Restrict Permission next Manage Credentials.

Customize as needed.

NelsMed
  • 1
  • 1