VBA Code in Separate File

[dwoom65]

I am running this code which opens files on a network drive and pastes them into an Excel file on the tabs specified in the code. Is there a way to tweak the code so that the macro could be in a separate file with the code referencing the destination spreadsheet file and file tabs? I would be fine with having the destination file open when running the code. Thanks.

Sub BILLS()

Dim path As String
Dim xxxEnd As String

Dim xxxStart As String

path = "\folder path\" (location of files pasted into destination file)

xxxEnd = "E500"
xxxStart = "A1"


Call exportRoutine(path, "month_bill_xxx.csv", xxxEnd, "destination file tab name", xxxStart)


End Sub

Sub exportRoutine(path As String, file As String, copyEnd As String, Reports As String, pasteCell As String)

Application.DisplayAlerts = False

Application.Workbooks.Open path & file
Windows(file).Activate
Range("A1", copyEnd).Select
Selection.Copy

ThisWorkbook.Activate
Sheets(Reports).Select
Range(pasteCell).Select
Selection.PasteSpecial Paste:=xlValues
Windows(file).Activate
ActiveWorkbook.Close SaveChanges:=False
ThisWorkbook.Activate

End Sub

 

[dave3009]

Hi

Maybe you could try something like

Sub exportRoutine(path As String, file As String, copyEnd As String, Reports As String, pasteCell As String)
Dim srcWB As Workbook
Dim srcSH As Worksheet
Dim actSH As Worksheet

Application.DisplayAlerts = False

Set actSH = ThisWorkbook.Sheets(Report)

Set srcWB = Application.Workbooks.Open(path & file)
Set srcSH = srcWB.ActiveSheet

actSH.Range(pasteCell, copyEnd).Value = srcSH.Range("A1", copyEnd).Value

srcWB.Close SaveChanges:=False
ThisWorkbook.Activate

End Sub

 

[dwoom65]

Thanks. I am getting an error on this line--not sure what I should be referencing in the parentheses where the word report is.

Set actSH = ThisWorkbook.Sheets(Report)

 

[dave3009]

Looks like a typo, your original code uses Reports, I put Report. Apologies.