Copy and paste from one workbook to another gives run-time error 1004 1

[vba317]

I am trying to copy and paste from one workbook to another. I have tried different methods with no results. I am hoping that I could get some assistance. The root of the problem is I don't understand how to link the application level, workbook level,sheet level. I have highlighted the line that is causing the error. Any help is greatly appreciated. Tom

Dim rng As Range
Dim xlApp As Excel.Application
Dim xlBook1 As Excel.Workbook
Dim xlBook2 As Excel.Workbook
Dim xlBook3 As Excel.Workbook
Dim xlBook4 As Excel.Workbook
Dim xlBook5 As Excel.Workbook
Dim xlBook6 As Excel.Workbook
Dim xlBook7 As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim lastRow As Long
Dim datemin As Date
Dim strFileName1 As String
Dim strFileName2 As String
Dim strFileName3 As String
Dim strDateMMDDYYYY As String
Dim strDate_Mon_N As String
Dim strDate_MonYr As String
Dim strDate_Yr As String
Dim strDate_ShMon As String
Dim strDate_LgMon As String
Dim strDate_PrevDay As String
Dim str_PrevDay As String
Dim cellvar As Range
Dim wrkbk As Workbook
Dim wbkSrcWrkbk As Workbook
Dim wbkDestWrkbk As Workbook
Dim strSrcWrkbk As String
Dim strDestWrkbk As String
Dim strSrcSheet As String
Dim strDestSheet As String
Dim intCounter As Integer
Dim strPath1 As String
Dim strPath2 As String
Dim strPath3 As String
Dim strPath4 As String

Set xlApp = CreateObject("Excel.Application")

'Set Date variables
 datemin = Date
 strDateMMDDYYYY = Format(CDate(datemin), "MMDDYYYY")
 'Format Number 03 for the month of March
 strDate_Mon_N = Format(CDate(datemin), "MM")
 'Format March Text for the month of March and 4 number year
 strDate_MonYr = Format(CDate(datemin), "MMMMYYYY")
 'Format 4 number Year only
 strDate_Yr = Format(CDate(datemin), "YYYY")
 'Format 3 letter month
 strDate_ShMon = Format(CDate(datemin), "MMM")
 'Format long Month Name
 strDate_LgMon = Format(CDate(datemin), "MMMM")
 
 'Set Workbook variables
 strFileName1 = "NE_DOR_" & strDateMMDDYYYY & ".xlsx"
 strFileName2 = strDate_Mon_N & "_DOR_TEMPLATE_" & strDate_MonYr & ".xls"
 strFileName3 = strDate_Mon_N & "_NP vs Vol Compare" & strDate_Yr & ".xlsx"
 'comp2015-DOR_REPORT_TEMPLATEA
 strFileName4 = "comp" & strDate_Yr & "-DOR_REPORT_TEMPLATEA.xlsm"
 strFileName5 = "Northeast Daily Net Adds - " & strDate_LgMon & " " & strDate_Yr & ".xlsx"
 strFileName6 = "Daily Net Adds - Current Week - Legacy View.xls"
 strFileName7 = "NE_DOR_" & strDateMMDDYYYY & "_orig" & ".xlsx"
 'comp2015-DOR_REPORT_TEMPLATEA
 strPath1 = "\\cable\NCD-Shared\DIV-FIN-Business-Analytics\Production\DOR\DailyData\DTSX_Output/"
 strPath2 = "\\cable\NCD-Shared\DIV-FIN-Business-Analytics\Production\DOR\DOR_BY_ENTITY\DOR_Conversion/"
 strPath3 = "C:\DOR_Files\Daily_Net_Adds/"
 strPath4 = "C:\DOR_Files\Daily_Net_Adds_Legacy/"

'Opens source workbook
Set xlApp = New Excel.Application
Set xlBook1 = xlApp.Workbooks.Open(strPath1 & strFileName1)
Set xlApp = xlBook1.Parent
xlApp.Visible = True

'Opens destination workbook
Set xlBook2 = xlApp.Workbooks.Open(strPath2 & strFileName2)
Set xlApp = xlBook2.Parent
xlApp.Visible = True

strSrcSheet = "CSG_DAYS_OUT"
strDestSheet = "DaysOut_ByRegion"
Workbooks(strFileName1).Activate
Workbooks(strFileName1).Worksheets(strSrcSheet).Select


lastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
'Source Sheet
[BLUE]xlApp.Worksheets(strSrcSheet).Range("A1" & "M" & lastRow).Copy [/Blue]
'Destination Sheet
xlBook2.Worksheets(strDestSheet).Range("A4").PasteSpecial

 

[SkipVought]

hi,

Book1.Worksheets(strSrcSheet).Range("A1" & "M" & lastRow).Copy

But here's the way I'd do it...

Dim wsSRC as excel.workbook, wsDES as excel.workbook

'Opens source workbook
Set xlApp = New Excel.Application
Set wsSRC = xlApp.Workbooks.Open(strPath1 & strFileName1)
Set xlApp = wsSRC.Parent
xlApp.Visible = True

'Opens destination workbook
Set wsDES = xlApp.Workbooks.Open(strPath2 & strFileName2)
Set xlApp = wsDES.Parent
xlApp.Visible = True

strSrcSheet = "CSG_DAYS_OUT"
strDestSheet = "DaysOut_ByRegion"

lastRow = wsSRC.Worksheets(strSrcSheet).Range("A" & Rows.Count).End(xlUp).Row
'Source Sheet
wsSRC.Worksheets(strSrcSheet).Range("A1" & "M" & lastRow).Copy 
'Destination Sheet
wsDRS.Worksheets(strDestSheet).Range("A4").PasteSpecial

I'd put all your sheet names in a table, copy into an array and loop thru the COPY/PASTE

 

[vba317]

I am getting an error 1004 on the following line:
wsSRC.Worksheets(strSrcSheet).Range("A1" & "M" & lastRow).Copy

 

[SkipVought]

Sorry, there were more issues tht I missed

with sSRC.Worksheets(strSrcSheet)
   .Range(.range("A1"), .range("M" & lastRow)).Copy
end with

 

[vba317]

Once again Skip you are awesome. It worked like a charm. I had to use the same principle with the destination worksheet that you used with the source worksheet. Now it is working. Thanks !