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?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.