Appending rows to existing worksheet/table

How does one copy data from one workbook and then paste it into the first empty row at the bottom of the data in another workbook? I need to create an archive of data...I work with the raw data in one workbook, and then I want to insert this formatted data into the new workbook at the end of the data that is already there. I want to create a macro to do this...is there code anyone can provide that does this? How do I go about creating the macro?

Thanks for all help,
--notaprogrammer

Hello notaprogrammer,

Welcome to the Forum!

This macro will copy the raw data you have selected to the workbook you want the data appended to. The last row is found without having to rely on a particular column of data. If 3 or more workbooks are open, you will be asked to enter the name of the workbook to copy from.

Sub AppendData()

  Dim DstWks As Workbook
  Dim FirstAddx As String
  Dim LastCell As Range
  Dim LastRow As Long
  Dim Msg As String
  Dim OpenWkbs As String
  Dim Rng As Range
  Dim SrcWkb As Workbook
  Dim Wkb As Workbook

   'Is there another workbook open
    If Workbooks.Count = 1 Then
      MsgBox "There are no Workbooks open to Copy Data from.", vbCritical
      Exit Sub
    End If
  
   'Get the names of all the open workbooks
    For Each Wkb In Workbooks
      If Wkb.Name <> ThisWorkbook.Name Then
        OpenWkbs = OpenWkbs & Wkb.Name & vbCrLf
      End If
    Next Wkb
  
   'Ask the user to input the name of the workbook to use if 3 or more workbooks are open
    If Workbooks.Count > 2 Then
      Msg = "Please enter the name of the Workbook you will be copying from:" & vbCrLf & vbLf
      Wkb = InputBox(Msg & OpenWkbs)
        If Wkb <> "" Then
          Set SrcWkb = Workbooks(Wkb)
        Else
          MsgBox "Action Canceled.", vbInformation
          Exit Sub
        End If
    Else
     'Remove the CrLf from the second workbook name
      Set SrcWkb = Workbooks(Left(OpenWkbs, Len(OpenWkbs) - 2))
    End If
      
   'Find the Last Row in ThisWorkbook
    Set Rng = ThisWorkbook.ActiveSheet.UsedRange
    
    Set LastCell = Rng.Find(What:="*", _
                            After:=Rng.Cells(1, 1), _
                            LookIn:=xlFormulas, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False)
                            
      If Not LastCell Is Nothing Then
        FirstAddx = LastCell.Address
          Do
            Set LastCell = Rng.FindNext(LastCell)
          Loop While Not LastCell Is Nothing And LastCell.Address <> FirstAddx
        LastRow = LastCell.Row
      Else
        LastRow = 1
      End If
      
       'Copy the data from the Source Workbook to ThisWorkbook
        SrcWkb.Activate
        Selection.Copy
        
        ThisWorkbook.Activate
        ActiveSheet.Cells(LastRow + 1, "A").PasteSpecial Paste:=xlPasteAll
        Cells(LastRow + 1, "A").Select
    
        Application.CutCopyMode = False
        
End Sub

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sincerely,
Leith Ross