Finding the last used row in a sheet and copying data to cells below it


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 9 of 9

Thread: Finding the last used row in a sheet and copying data to cells below it

  1. #1
    Join Date
    Aug 2009
    Posts
    37

    Finding the last used row in a sheet and copying data to cells below it

    Hi,

    Can anybody help me with this?

    I recorded a macro which copies cells value from one open excel file into another excel file where it needs to find the lasy used row, and then copy into cells in the row below it. But the resultant VBA code references actual cell values eg Range("A15658").Select which does not work because next time the macro is run it should be +1

    steps might be
    open worksheet a and Sheets
    Copy from
    sheetA, row 10, columnB
    to worksheetB
    last used row+1, column A

    Copy from
    sheetA, row 10, columnD
    to worksheetB
    last used row+1, column B ..... etc

    Once I sort this out, I have one more query. If I can't sort it out myself, would like to get back to you or the forum again.

    Many thanks.

    Talāt

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Do something like
    Code:
    Dim intRow As Integer
    
    intRow = YourWorkSheet.Cells.SpecialCells(xlLastCell).Row
    Add 1 to intRow and you should be one below the last one used.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  3. #3
    Join Date
    Aug 2009
    Posts
    37
    Thanks Hack.

    How would I then use the row value of intRow to select specific cells in that row?

    I tried various syntax like and all it gave me errors; like

    Range.cell (intRow "C").select

    Remember, I am copying values from specific cells in anotehr worksheet onto this another worksheet in putting them in defined columns on the intRow+1

    T.

  4. #4
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Try: worksheet.range("C" & intRow).Copy
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  5. #5
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    Finding the last used row Try:
    Activesheet.UsedRange.Rows.Count

    Reference a cell by Last row, column where "A" is column 1:
    Activesheet.cells(Activesheet.UsedRange.Rows.Count, 1)

  6. #6
    Join Date
    Aug 2009
    Posts
    37
    Thank you Hack and Ron. With your responses, I have now sorted this out and wrote the code that does what I need.

    As ever, I am grateful to you both.

    Best.

    Talāt

  7. #7
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Quote Originally Posted by Talat View Post
    Thank you Hack and Ron. With your responses, I have now sorted this out and wrote the code that does what I need.
    Post what you have...it might help someone else.

    Thanks.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  8. #8
    Join Date
    Aug 2009
    Posts
    37
    Sure. Bit late, but better than never...

    Here are the various codes that achieve the end result I was after:

    This one copies specific cells from teh invoice file into a "log" file

    -------------- ooo ------------------------------------------
    Code:
    Sub Write_to_INVData_File()
    '
    ' Write_to_INVData_File Macro
    '
        
       Dim DataRange     As String
       Dim InvDate       As Date
       Dim InvNum        As Long     'invoice "FR" number
       Dim Customer      As String
       Dim Account       As String
       Dim InvTotal      As Currency
       Dim InvPostage    As Currency
       Dim InvSheet      As Worksheet
       Dim DataSheet     As Worksheet
       Dim NextRow       As Long     'the next available invoice row on the batch sheet
       Dim oRow          As Long     'row number on LogSheet
       
        Set InvSheet = ThisWorkbook.Worksheets("INVOICE TEMPLATE")
       
        Workbooks.Open Filename:="G:\PUBS\PP-MS\INVOICES\INVDATA.XLS"
       
        Set DataSheet = ActiveWorkbook.Worksheets("INVDATA2")
           
        oRow = DataSheet.UsedRange.Rows.Count + 1
        
        Windows("INVDATA.XLS").Activate
           
            InvSheet.Range("B6").Copy  'Customer
            DataSheet.Cells(oRow, "D").PasteSpecial xlPasteValues
           
            InvSheet.Range("E5").Copy  'Account
            DataSheet.Cells(oRow, "C").PasteSpecial xlPasteValues
          
          
            InvSheet.Range("K44").Copy  'InvTotal
            DataSheet.Cells(oRow, "E").PasteSpecial xlPasteValues
          
            InvSheet.Range("K38").Copy  'InvPostage
            DataSheet.Cells(oRow, "F").PasteSpecial xlPasteValues
          
           
            InvSheet.Range("K2").Copy  'InvNum
            DataSheet.Cells(oRow, "A").PasteSpecial xlPasteValues
                           
            InvSheet.Range("F17").Copy 'InvDate
            DataSheet.Cells(oRow, "B").PasteSpecial xlPasteValues
            Application.CutCopyMode = False
        
         oRow = oRow + 1
         Range("A1").Select
         ActiveWorkbook.Close True           'save changes and close
         
    End Sub
    This one prints copies of teh invoice before calling the sub procedure above
    Code:
    --------------   ooo  ----------------------------------------------
    
    Sub Print_Paying_Sect_Invoice()
    '
        Range("M2").Select
        Selection.Copy
        Range("A2").Select
        Selection.PasteSpecial xlPasteValues
        
        Range("A2:K143").Select
        ActiveSheet.PageSetup.PrintArea = "$A$2:$K$143"
        ActiveWindow.SelectedSheets.PrintPreview
        Range("A1").Select
        
        Application.Run "Write_to_INVData_File"
        Application.Run "InvoiceToBatch"
        Range("A1").Select
        ActiveWorkbook.Save
    End Sub
    -------------- ooo --------------------------------------------------

    During the conversion process of teh old macro file I have had to write a few more sub procedures. If anyone is interested I can post them all. They probably are not the cleanest of codes, but they do what I want. I still have some fine tuning to to do.

    Merry Christmas everyone, and thank you all who have responded to my queries.

    Talât.
    Last edited by Hack; 12-28-2009 at 09:34 AM. Reason: Added Code Tags

  9. #9
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Quote Originally Posted by Talat View Post
    Merry Christmas everyone, and thank you all who have responded to my queries.
    Same to you and thanks for posting your solution.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

Similar Threads

  1. Copying cells from one sheet to another sheet
    By de_haviland in forum VB Classic
    Replies: 0
    Last Post: 11-13-2007, 04:24 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center