0

I have used the following code successfully for the past few months. I now need to have 2 worksheets upload to the two separate sheets in the Master Workbook.

The breakdown of the sheet names would be the following:
Sheet: “Referrals” GOING TO Master Sheet: “Referrals Report”
Sheet: “New Accounts” GOING TO Master Sheet: “New Accounts Report”

Below is the code to send data from one worksheet to another but macros in the same workbook to send to their appropriate sheet in the Master Workbook. I'm hoping to use the same code but just need help telling it the sheets. Any help is appreciated. 

Sub ExportNewAccounts()

Dim wbl As Workbook
Dim wbm As Workbook
Dim wsl As Worksheet
Dim wsm As Worksheet
Set wbl = ThisWorkbook
Set wbm = Workbooks.Open("\\PROFILER\2016 Global Tracker.xlsx")
Set wsl = wbl.Sheets("New Accounts")
Set wsm = wbm.Sheets("New Accounts Report")

If MsgBox("Are you sure you want to export all data entered?", vbYesNo + vbInformation) = vbNo Then Exit Sub
Application.ScreenUpdating = False

With wsl.Range("A4")
    Range(.Cells(1, 1), .End(xlDown).EntireRow).Copy
End With ****HERE***
wsm.Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
wbm.Save
wbm.Close
Application.ScreenUpdating = True
MsgBox ("Thanks, your data has been successfully exported.")

End Sub

Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer
On Error Resume Next
filenum = FreeFile()
Open filename For Input Lock Read As #filenum
Close filenum
errnum = Err
On Error GoTo 0
Select Case errnum
Case 0
IsFileOpen = False
Case 70
IsFileOpen = True
Case Else
Error errnum
End Select
End Function
Community
  • 1
  • 1
AHern
  • 11
  • 4
  • 1
    Try setting your books and sheets? – findwindow Dec 02 '15 at 17:30
  • Default references... default references everywhere... – Chrismas007 Dec 02 '15 at 17:38
  • Yes that is the trouble I am having. Setting the books and sheets – AHern Dec 02 '15 at 17:56
  • Check [here](http://stackoverflow.com/questions/26039872/declaring-variable-workbook-worksheet-vba) – findwindow Dec 02 '15 at 19:39
  • I guess I am stuck in the sense of how to address it based on the code I already have. Where to put the addressing of the worksheets. – AHern Dec 02 '15 at 23:09
  • Hi all, the code has been edited. The editing of the variables worked great! However, I am having trouble with the code above. Which copies from the 2nd sheet and to go to the 2ndsheet in the master workbook – AHern Dec 03 '15 at 22:26

0 Answers0