PDA

View Full Version : How to paste data into next available blank row



anne.gomes
05-25-2014, 03:18 PM
Hi, I'm having trouble with pasting my coped data from one workbook into a next available blank row in the other workbook. This is what I have so far:




Sub Copy()
Application.DisplayAlerts = wdAlertsNone
dt = Format(CStr(Now), "mmmm d, yyyy")
ChDir "C:\Users\anneg\Desktop"
Workbooks.Open Filename:="C:\Users\anneg\Desktop\Book1.xlsx"
Rows("1:1").Select
Selection.Copy
ActiveWindow.Close

Range("A1").Select 'how to select next empty row on the worksheet and paste it onto that
ActiveSheet.Paste
Application.CutCopyMode = False


End Sub





Any help would be great,

Thank you in advance :)

Paul_Hossler
05-25-2014, 03:48 PM
This could be made much more general since there are assumptions I made regarding the destination worksheet data layout




Sub test()
MsgBox LastRow.Address
LastRow.Select
End Sub


'assumes activesheet, and that there is at least one row with data starting in row 1
Function LastRow() As Range
Dim iRow As Long
iRow = ActiveSheet.Cells(1, 1).CurrentRegion.Rows.Count + 1
Set LastRow = ActiveSheet.Rows(iRow)
End Function




Post a small sample of the real destination sheet if you want

anne.gomes
05-25-2014, 03:57 PM
Here is a sample of the destination spreadsheet:


ID


ORGANIZATION_ELEMENT_ID


ORGANIZATION_ELEMENT_CODE


ORGANIZATION_ELEMENT_NAME


COMPANY_ID


COMPANY_CODE


COMPANY_NAME


INVOICE_NUMBER


SUPPLIER_ID




fd4caba20b5d4c2daf767abde287679d


FES


FES


Easy Steel


FES


FES


Easy Steel


SI861816


8bbc65aa263b474fb2f575cc4ac7a9b9




58a3d41a46db4f95b5bd868660d59c7c


FES


FES


Easy Steel


FES


FES


Easy Steel


24246


920935c01f4b4e5d8608866521d7807a




13f5f8268ba24dfdbc2451dbe01d630e


FES


FES


Easy Steel


FES


FES


Easy Steel


24254


920935c01f4b4e5d8608866521d7807a




9356fb7d4a34421da805a76efbd12028


FES


FES


Easy Steel


FES


FES


Easy Steel


1289264


71927b29df7644e09bfd34d70741d147




389eefbb768849d5befa5eedde735a72


FES


FES


Easy Steel


FES


FES


Easy Steel


1289276


71927b29df7644e09bfd34d70741d147






I want the macro to go to the last row with data and paste the the newly copied data into the next empty row.

EirikDaude
05-26-2014, 12:01 AM
I'd say replacing ActiveSheet.Paste with LastRow.Paste would do what you want, assuming you copy Paul Hossler's function into your VBA code. Unless there is some trouble with the data you copy not staying on the clipboard after you close the workbook you are copying from?

I notice that you turn off alerts in your sub, presumably to not get the "Do you want to save"-dialog. You can circumvent this by adding the argument "false" to the function call (i.e. activeworkbook.close(false)) if you don't want to save changes, or with the argument "true" if you do want to save them. If you decide to stick with your current solution, you probably want to turn the alerts back on at the end of the sub?