Results 1 to 9 of 9

Thread: Copy data and paste everything
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    935
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Copy data and paste everything

    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?

  2. #2
    Board Regular
    Join Date
    May 2003
    Posts
    1,082
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy data and paste everything

    Quote Originally Posted by dpaton05 View Post
    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'?
    =IF(U:U="don't care where you are going",UR:UR<>"LOST",UR:UR="LOST")

  3. #3
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    935
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy data and paste everything

    Well, how do I paste the formats too?
    Last edited by dpaton05; Dec 2nd, 2018 at 09:26 PM.

  4. #4
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    935
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy data and paste everything

    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 by dpaton05; Dec 2nd, 2018 at 09:28 PM.

  5. #5
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,066
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Copy data and paste everything

    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 by MARK858; Dec 2nd, 2018 at 09:50 PM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  6. #6
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    935
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy data and paste everything

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

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

  7. #7
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,043
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Copy data and paste everything

    Quote Originally Posted by dpaton05 View Post
    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")

  8. #8
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,066
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Copy data and paste everything

    Quote Originally Posted by Akuini View Post
    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,
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  9. #9
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    935
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy data and paste everything

    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 by dpaton05; Dec 3rd, 2018 at 10:19 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •