Copy data and paste everything

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,122
I have the following code and I want it to copy and paste values and format like cell size etc.

Code:
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

Well-known Member
Joined
May 7, 2003
Messages
1,082
I have the following code and I want it to copy and paste values and format like cell size etc.

Code:
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-known Member
Joined
Aug 14, 2018
Messages
1,122
Well, how do I paste the formats too?
 
Last edited:

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,122
What's wrong with this code?

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
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,724
Office Version
365, 2010
Platform
Windows, Mobile
Maybe (untested)...

Code:
    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.
 
Last edited:

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,122
I get an error with that saying subscript out of range and it highlights this row of code:

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

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,470
Office Version
365
Platform
Windows
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:
Code:
.copy Sheets("MonthYear").Range("A1")
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,724
Office Version
365, 2010
Platform
Windows, Mobile
If MonthYear is the name of the sheet (not a variable) then:
Code:
.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

Well-known Member
Joined
Aug 14, 2018
Messages
1,122
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?

Code:
    ActiveSheet.copy After:=Worksheets(Worksheets.Count)
 
Last edited:

Forum statistics

Threads
1,085,311
Messages
5,382,902
Members
401,808
Latest member
huyennhiteen9xx

Some videos you may like

This Week's Hot Topics

Top