• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

Excel Experts - Stuck Again

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    #11
    Originally posted by LisaContractorUmbrella View Post
    Hi Eek
    Sub Macro1()

    Columns("F:F").Select
    Selection.Cut Destination:=Columns("N:N")
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "=RIGHT(RC[8],LEN(RC[8])-2)"
    Range("F1").Select
    Selection.AutoFill Destination:=Range("F1:F1015"), Type:=xlFillDefault
    Range("F1:F1015").Select
    Range("A1").Select
    Sheets("Daybook Invoices").Select
    Range("A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row).Select
    NewCell = Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
    Range("A" & NewCell).Select
    Sheets("Daybook Credits").Select
    Range("A1:M1015").Select
    ActiveSheet.Paste
    End Sub

    Comes up with a runtime error 1004 - Paste method of Worksheet class failed. Tricky little blighter!

    Any suggestions gratefully received.
    As its excel you really only want a single cell to be selected when you hit paste otherwise you can be sure the size of the ranges won't match and you get an error. I would be tempted to try.

    Range("A1").Select
    ActiveSheet.Paste


    I would also say that I don't think that there is a lot of code in that macro that probably won't do what you think it will but that will only be confirmed once you've got the paste working.
    merely at clientco for the entertainment

    Comment


      #12
      Originally posted by eek View Post
      As its excel you really only want a single cell to be selected when you hit paste otherwise you can be sure the size of the ranges won't match and you get an error. I would be tempted to try.

      Range("A1").Select
      ActiveSheet.Paste


      I would also say that I don't think that there is a lot of code in that macro that probably won't do what you think it will but that will only be confirmed once you've got the paste working.
      Hi Eek

      Nope no luck unfortunately - came up with same error. The rest of it I have tested and it works ok it's just this last little bit - problem is that the 'cut' area will always be a different size and the 'paste' area will never start in the same cell
      Connect with me on LinkedIn

      Follow us on Twitter.

      ContractorUK Best Forum Advisor 2015

      Comment


        #13
        Originally posted by LisaContractorUmbrella View Post
        Hi Eek

        Nope no luck unfortunately - came up with same error. The rest of it I have tested and it works ok it's just this last little bit - problem is that the 'cut' area will always be a different size and the 'paste' area will never start in the same cell
        I'm not helped by the fact that I only have a very recent version of excel around.

        But try changing the paste to be

        Range("A1016").Select
        Sheets("Daybook Credits").Paste

        and see if that allows the paste to work.
        merely at clientco for the entertainment

        Comment


          #14
          Originally posted by eek View Post
          I'm not helped by the fact that I only have a very recent version of excel around.

          But try changing the paste to be

          Range("A1016").Select
          Sheets("Daybook Credits").Paste

          and see if that allows the paste to work.
          I know - am trying to get used to 2007 myself. Still didn't work I'm afraid
          Connect with me on LinkedIn

          Follow us on Twitter.

          ContractorUK Best Forum Advisor 2015

          Comment


            #15
            Originally posted by LisaContractorUmbrella View Post
            I know - am trying to get used to 2007 myself. Still didn't work I'm afraid
            I think you may need to cut the macro down and work through it step by step as this works


            'select the cells we want to copy
            Range("a1:b1").Select
            Selection.Copy
            'move to the worksheet we want the cells on
            Sheets("Sheet2").Select
            'Move to the correct location on that page (note we only 'want the selected range to be a single cell)
            NewCell = Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
            Range("A" & NewCell).Select
            ' paste the cells
            ActiveSheet.Paste

            I wander if you have anything in the clipboard when you get to the paste request.
            merely at clientco for the entertainment

            Comment


              #16
              Originally posted by eek View Post
              I think you may need to cut the macro down and work through it step by step as this works


              'select the cells we want to copy
              Range("a1:b1").Select
              Selection.Copy
              'move to the worksheet we want the cells on
              Sheets("Sheet2").Select
              'Move to the correct location on that page (note we only 'want the selected range to be a single cell)
              NewCell = Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
              Range("A" & NewCell).Select
              ' paste the cells
              ActiveSheet.Paste

              I wander if you have anything in the clipboard when you get to the paste request.
              Yay - it worked Thank you so much Eek - really appreciated
              Connect with me on LinkedIn

              Follow us on Twitter.

              ContractorUK Best Forum Advisor 2015

              Comment


                #17
                Originally posted by LisaContractorUmbrella View Post
                Yay - it worked Thank you so much Eek - really appreciated
                Just hope the free accountancy advice you are going to get doesn't involve an excel spreadsheet!!!
                'CUK forum personality of 2011 - Winner - Yes really!!!!

                Comment


                  #18
                  Originally posted by northernladuk View Post
                  Just hope the free accountancy advice you are going to get doesn't involve an excel spreadsheet!!!
                  Nope just pure style
                  Connect with me on LinkedIn

                  Follow us on Twitter.

                  ContractorUK Best Forum Advisor 2015

                  Comment

                  Working...
                  X