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

[Talat]

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

[Hack]

Do something like

Dim intRow As Integer

intRow = YourWorkSheet.Cells.SpecialCells(xlLastCell).Row

Add 1 to intRow and you should be one below the last one used.

[Talat]

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.

[Hack]

Try: worksheet.range("C" & intRow).Copy

[Ron Weller]

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)

[Talat]

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

[Hack]

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.

[Talat]

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

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

--------------   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.

[Hack]

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

Same to you and thanks for posting your solution.