Open workbook, copy rows, paste to end of current workbook

try this code.......

Sub copying()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
mynew = ActiveWorkbook.Name
Dim nsh As Worksheet, FR As Long, osh As Worksheet, LR As Long
check = Application.Dialogs(xlDialogOpen).Show
If check <> True Then
Exit Sub
End If
myold = ActiveWorkbook.Name
Set osh = Workbooks("" & myold & "").Sheets("olddatasheet")
With osh
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
End With
Set nsh = Workbooks("" & mynew & "").Sheets("newdatasheet")
With nsh
    FR = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    osh.Range("A2:A" & LR).EntireRow.Copy
    .Range("A" & FR).PasteSpecial (xlPasteValues)
    Application.CutCopyMode = False
    Workbooks("" & myold & "").Close False
    .Range("A1").Select
End With
MsgBox "Done !"
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

or else find the two attached file for validating..

Hope this will work

Hi, gmn734,

you have dimmed wbTarget as Workbook so you may only assign a workbook to that variable, not a workseet as well:

' copy_new_forecasts Macro
'
'open OLDDATA
Application.Dialogs(xlDialogOpen).Show

Dim wbTarget As Workbook 'workbook where the data is to be pasted (NEWDATA, newdatasheet)
Dim wbThis As Workbook 'workbook from where the data is to be copied (OLDDATA, olddatasheet)
Dim strName As String 'name of the source sheet/ target workbook (olddatasheet)
'set to the current active workbook (OLDDATA)
Set wbThis = ActiveWorkbook

'get the active sheetname of the book (olddatasheet)
strName = ActiveSheet.Name

'set target workbook
Set wbTarget = Workbooks("NEWDATA")

''activate the OLDDATA book
'wbThis.Activate

'copy the range from OLDDATA(all rows except row1, number of rows with data will be varied each time)
wbThis.Sheets(strName).Range("A1").CurrentRegion.Offset(1, 0).Copy

'paste the data on the next available row of NEWDATA, newdatasheet
wbTarget.Sheets("newdatasheet").Range("A1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

'close the OLDDATA workbook
wbThis.Close

Ciao,
Holger