0

I've been trying to build a workbook for my company to use to track sales.

The problem I'm running into is that I want each user to have their own workbook saved on their local computer for their own info. This workbook uses a userform to submit data, which pulls info from a sheet in the workbook to autofill based on brand of device sold.

I want to be able to update this information on a network drive, and have the workbook updated automatically each time it is opened.

I've been able to do this for an individual range, with no problem.

I want to be able to do this for a full worksheet, so I can define additional ranges as needed and have it populated to users without need for individual updating.

Basically, there are 2 processed that need to be done, the first process is already taken care of, but I'll show you why I need to copy multiple ranges

-Open Userform
-Select Brand
-Userform automatically populates multiple models based on Sheet2.Range("Brand")
-Select Brand2
-Populates based on Sheet2.Range("Brand2")
-Submit

Second process is
-Open NetworkLocation/Workbook
-Add Range("Brand3")
-Save

-Open Local Workbook

-Sheet2 = NetworkLocation/Workbook/Sheet2
-Userform populates with additional data for Brand3

Is this possible, and could you advise on how to proceed?

Thanks :)

  • So you want database functionalities in excel ? – dvhh Dec 18 '14 at 2:05
  • Effectively, I think you might be right. I haven't done any work in databases for a very long time, so forgive me if I'm a bit rusty. In Excel I've got the local database saved, I just want to be able to update a network location and have the local location updated to match – Guest123465798 Dec 18 '14 at 2:09
1

Okay, fixed it all up. :)

And answering my own question lol - Just for googles sake.

Private Sub Workbook_Open()

Const fromFile = "NetworkDriver\Folder\Workbook.xlsx"

Application.DisplayAlerts = False
Sheets("Sheet8").Delete
Sheets("Sheet9").Delete
Application.DisplayAlerts = True

Dim srcBook As Workbook
Dim wkb As Workbook
Set wkb = ActiveWorkbook
' open workbook and set reference!





Set srcBook = Application.Workbooks.Open(fromFile, _
UpdateLinks:=False, _
ReadOnly:=True, _
AddToMRU:=False)

Application.ScreenUpdating = False
srcBook.Sheets("Sheet8").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
srcBook.Sheets("Sheet9").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

srcBook.Close False
wkb.Sheets("Sheet8").Visible = False
wkb.Sheets("Sheet9").Visible = False

Application.ScreenUpdating = True



End Sub

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.