Home>

I used to fix the macro I picked up on the net and used it, but when I made a copy of the "basic" sheet and pasted it to a new sheet, the format settings (cell size) changed. So I want to copy and paste the basics
What I did was change Range("A1:J461").Copy to cells.select.copy, but it didn't work.

With Worksheets.Add()
.Name = Sheets("Customer Management").Range("D1")
.Move After:=Worksheets(Worksheets.Count)
End With

Worksheets("Basics").Range("A1:J461").Copy Worksheets(Worksheets.Count).Range("A1")
Columns("B:B").EntireColumn.AutoFit

With Worksheets("Customer Management").Activate
Dim LastRow As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(LastRow, 1) = (Range("D1"))
End With

I think I should fix this, but I do not know how to fix it
......................................↓ How to change here (specify the entire sheet)
Worksheets("Basics").Range("A1:J461").Copy Worksheets(Worksheets.Count).Range("A1")
↓ I don't think this part is necessary...
Columns("B:B").EntireColumn.AutoFit

................................................. .................
ABCDEF
1
2
3
Four

A If i press the buttons on the left 1 and select all, then copy and paste it, it will be displayed as it is (cell size etc.), so I would like to do so.
With the current macro, it will return to the initial cell size.
Of course you can choose a different copy (since I know only this way)

  • Answer # 1

    If you insert a new sheet and paste the contents of the "basic" sheet as it is, you will get the following contents.

      Worksheets.Add After:=Worksheets(Worksheets.Count)
        Sheets("Basics").Cells.Copy
        Sheets(Worksheets.Count).PasteSpecial
        Application.CutCopyMode = False

  • Answer # 2

    Excel
    The width of a column depends on the column and the width of a row depends on the row (it is not possible for individual cells in the same column to have different cell widths), so if you want to keep the column width the whole column If you want to keep, you need to copy the entire line.
    If both, copy the sheet itself, or
    I think there is no choice but to set rows and columns individually.


    Addendum

    You can copy the entire cell.
    It is a scale from the eyes.

    If you want to operate manually, select it and tell Excel,
    In VBA, you will be instructed in writing what you want to operate, so
    It is not necessary to write instructions for selecting and activating.
    It's a useless operation.

    Sub test1()
        Dim wshTemplate As Worksheet'' Template Sheet
        Dim wshNew As Worksheet'New sheet
        Dim strName As String'Sheet name
        '' Preparation (arrangement of prerequisites)
        strName = Worksheets("Customer Management").Range("D1").Value
        With Worksheets
            Set wshTemplate = .Item("Basics")
            Set wshNew = .Add(after:=.Item(.Count))
        End With
        '' Post&add
        With wshNew
            wshTemplate.Cells.Copy .Cells
            .Name = strName
            With .UsedRange
                .Cells(.Rows.Count + 1, 1).Value = strName
            End With
        End With
    End Sub

    However,
    1) Create a new sheet
    2) Copy and paste the cell of the template sheet
    If so, the work will be two steps,

    1) Copy the template sheet

    Does one process produce the same result?
    Changing the sheet name is the same.

    Sub test2()
        Dim s As String
        Dim ix As Long
        With Worksheets
            s = .Item("Customer Management").Range("D1").Value
            ix = .Count
            .Item("Basics").Copy after:=.Item(ix)'← copy the whole sheet
            With .Item(ix + 1)
                .Name = s
                .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Value = s
            End With
        End With
    End Sub

    If you have any commentary, please ask.

  • Answer # 3

    I tried it with a macro recording

    Sub Macro1()
    '
        Cells.Select
        Range("A3564").Activate
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet1").Select
        Cells.Select
        Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
            , SkipBlanks:=False, Transpose:=False
    End Sub