Copy data and paste everything

[dpaton05]

I have the following code and I want it to copy and paste values and format like cell size etc.

Sheets("home").Range("A1:E42").copy Destination:=Sheets(MonthYear).Range("A1")

What do I need to add to it as it only copies the values at the moment?

 

[BAlGaInTl]

I have the following code and I want it to copy and paste values and format like cell size etc.

Sheets("home").Range("A1:E42").copy Destination:=Sheets(MonthYear).Range("A1")

What do I need to add to it as it only copies the values at the moment?

I don't know that there is a way to copy things like cell sizes.

What if you copied the whole worksheet tab to a new sheet tab and then named it 'MonthYear'?

 

[dpaton05]

Well, how do I paste the formats too?

 

[dpaton05]

What's wrong with this code?

    Sheets("home").Range("A1:E42").copy
    
    With Worksheets(MonthYear)
         .PasteSpecial Paste:=xlPasteValues
         .PasteSpecial Paste:=xlPasteFormats
    End With

as it has an error in the code and highlights
.PasteSpecial Paste:=xlPasteValues

 

[MARK858]

Maybe (untested)...

    Dim myRw As Long, myCo As Long
    Application.ScreenUpdating = False
    
    With Sheets("home").Range("A1:E42")
        .Copy Sheets(MonthYear).Range("A1")
        
        For myRw = 1 To .Rows.Count
            Sheets(MonthYear).Range("A1").Resize(.Rows.Count, .Columns.Count).Rows(myRw).RowHeight = .Rows(myRw).RowHeight
        Next myRw
        
        For myCo = 1 To .Columns.Count
            Sheets(MonthYear).Range("A1").Resize(.Rows.Count, .Columns.Count).Columns(myCo).ColumnWidth = .Columns(myCo).ColumnWidth
        Next myCo
    End With
    
    Application.ScreenUpdating = True

as it has an error in the code and highlights
.PasteSpecial Paste:=xlPasteValues

The error is because you haven't specified a range but it won't adjust the row/column heights anyway.

The code you had originally "pastes" the formats just not the cell height/width.

 

[dpaton05]

I get an error with that saying subscript out of range and it highlights this row of code:

.copy Sheets(MonthYear).Range("A1")

 

[Akuini]

I get an error with that saying subscript out of range and it highlights this row of code:

.copy Sheets(MonthYear).Range("A1")

If MonthYear is the name of the sheet (not a variable) then:

.copy Sheets("MonthYear").Range("A1")

 

[MARK858]

If MonthYear is the name of the sheet (not a variable) then:

.copy Sheets("MonthYear").Range("A1")

@dpaton05, if what Akuini has put is correct (and I suspect he is) then please note that the reason I wrote it like that is because that is how you have referred to MonthYear in all the code you have posted in this thread, and so I have had to assume you had it correct as you haven't posted the full code that you are using.

If the above isn't correct then obviously you need to add your code for defining the variable to the code I posted,

 

[dpaton05]

Month year is a variable, it contains the name of the sheet. I have this code working successfully to copy and paste the sheet but how do I rename it to MonthYear?

    ActiveSheet.copy After:=Worksheets(Worksheets.Count)