Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Macro copy and paste in next blank cell

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Macro copy and paste in next blank cell

    I currently have set up a data collection template in excel for Project managers to complete. Once this is returned to me all this data needs to be copied into another spreadsheet (One row per project) ready for importing into excel.

    I have a simple macro that will copy into the first row only but I would like to be able to copy many of the questionaires into the second worksheet before importing to access, to do that the macro will need to copy into the next blank cell.............ideas

    The whole copy paste macro is made up of about 30 of these commands for different cells pasting into the same row of the sheet "SOA_ACCESS_TEMPLATE"

    Windows("SOA_TEMPLATE.xls").Activate
    Range("B4:H4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("SOA_ACCESS TEMPLATE.xls").Activate
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False

    I need to add something into the code that will paste in the next Blank cell ?

    Any ideas would be appreciated as I have not had much experience with VBA.

  • #2
    Post you rcurrent code - it will help us to amend it for your circumstances
    Kind Regards, Will Riley

    LinkedIn: Will Riley

    Comment


    • #3
      Re: Macro copy and paste in next blank cell

      There was no response to this one, but this is exactly what I need to do as well. I need to combine two different tabs into one. I have a tab copying over to a sheet columns A-K or something like that, and then I have another tab that copies over, but I want to find the next blank row in column A to paste to. Is there a way to just select the next row down after the first initial paste with Active Cell Offset? and then select the other tab, copy what I need to, select the sheet to paste to and paste? Thank you very much for any insight you could provide!!

      Comment


      • #4
        Re: Macro copy and paste in next blank cell

        The code below will find the last used row. Use it in your vba code and you can set your target for pasting to:

        Code:
        Cells(xllastrow + 1, 1)
        to find the first empty row with the focus set to column A.

        Code:
        Function xlLastRow(Optional WorksheetName As String) As Long 
             
             '    find the last populated row in a worksheet
             
            If WorksheetName = vbNullString Then 
                WorksheetName = ActiveSheet.Name 
            End If 
            With Worksheets(WorksheetName) 
                xlLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _ 
                xlWhole, xlByRows, xlPrevious).Row 
            End With 
             
        End Function
        Check out this link to see an example.



        HTH,
        Brandtrock

        Brandtrock Consulting | Brandtrock Files | ISU Athletics | Bricktown | VBA Express

        Comment


        • #5
          Re: Macro copy and paste in next blank cell

          Brandrock,

          Everytime I try to use that code I keep getting an error At

          Code:
          Range(cells(xlLastRow + 1, 1)).Select
          I how to fix this. Please help
          Last edited by AAE; November 19th, 2010, 05:53. Reason: add code tags

          Comment


          • #6
            Re: Macro copy and paste in next blank cell

            Hello griderae,

            Welcome to Ozgrid.

            Posting your question in threads started by others is a violation of the Forum Rules and is known as thread hijacking. Posting solutions is acceptable.

            ALWAYS start a new thread for your questions. You may, if you find it helpful, provide a link back to this (or any other) thread.

            Start a new thread and be sure to give it a title using only search friendly key words that accurately describe your thread content or overall objective and avoid the use of non-essential words such as "I need", "Help", "urgent", "desperate", etc.

            Additionally,

            Per the Forum Rules, VBA code must be wrapped in code tags, which you omitted.
            I've added the tags for you this time; please be sure to use them in future posts when needed.

            Omission of code tags may result in future threads being locked without notice. See the link in my signature for more information on how to use tags.


            How to use code tags
            Starting tag > > [code]
            Your code here
            Ending tag > > [/code]
            AAE
            ----------------------------------------------------

            Forum Rules | Message to Cross Posters | How to use Tags

            Comment

            Trending

            Collapse

            There are no results that meet this criteria.

            Working...
            X