Create Master File With Macro Built In That Will Allow Multiple Users To Use It At Once

Feb 7, 2014

I am trying to create a master file, with a macro built in, that will allow multiple users to use it at once. The macro is to open a dilouge(sp sorry) box showing the contents of a specific folder, allow the user to selct one of the sheets, then copy and paste the set details from the hidden tab on this sheet (All sheets will be the same barring title), append the details to the master list in the first empty row.

I've got this far thus
Sub Macro1()
'
' Macro1 Macro
'
'
' Modify this folder path to point to the files you want to use.
FolderPath = "My Folder name here"

' Set the current directory to the the folder path.
ChDrive FolderPath

[Code] .....

So I can copy the row, but I can't get the first part to open .

View 3 Replies


ADVERTISEMENT

Excel 2007 :: Macro To Send File As PDF From Outlook To Multiple Users?

May 13, 2013

I currently have a spreadsheet setup on a Macro to send & distribute a message from an 2007 Excel file to multiple users through Microsoft Outlook 2007. It is currently setup to send as an attachment. I need to find a way to have this file be sent as a PDF file. I know you can save Excel as PDF's so there must be a way to send them & attach them to as a PDF.

View 3 Replies View Related

VBA Macro To Create Multiple Workbooks From One Master Workbook By Cost Centre

Jun 27, 2013

VBA Macro to work through a worksheet that consist of static data (tab 4) cost centres and to populate a new work book per cost centre consisting of three tabs for every cost centre found in the static data.

The master Workbook has the following tabs:

Tab 1 is called travel and consist of column a which is the cost centre number (plus 14 other columns)

Tab 2 is called Mobile and consist of column a which is the cost centre number (plus 14 other columns)

Tab 3 is called Expenses. and consist of column a which is the cost centre number (plus 14 other columns)

Tab 4 Static Date column 1 cost centre number and consist of column 1 which is the cost centre number (plus 14 other columns).

If no information found on a specific cost centre, the tab will include the headers and return the words "No transactions for this period"

Whilst splitting data into Tabs the workbooks should check against the Static Data table and include cost center description in Column B of each tab in the new workbook.

If master workbook consist of the following....

Tab 1 is called travel and consist of column 1 which is the cost centre number (plus 14 other columns), cost centres, 555,557,

Tab 2 is called Mobile column 1 cost centre number and consist of column 1 which is the cost centre number (plus 14 other columns) cost centres, 555, 78689,

Tab 3 is called Expenses. column 1 cost centre number and consist of column 1 which is the cost centre number (plus 14 other columns), cost centres, 555,

tab 4 Static Date - column 1 cost centre number and consist of column 1 which is the cost centre number (plus 14 other columns). cost centres, 555, 557,78689

It should output 3 workbooks by cost centre number.

One for 555, which consists of 3 tabs, travel, mobile and expenses.

A second for 557 which has 3 tabs travel, mobile and expenses, but only with data in the travel tab.

A third for 78689 which has 3 tabs travel, mobile and expenses, but only with data for mobile data.

The workbooks will be replicates of the contents within the tabs where column a wil be the cost centre plus 14 additional columns.

View 5 Replies View Related

Macro To Collect / Vookup Data From Multiple Files To One Master File

Oct 29, 2013

I have approx 11 files in one folder and one master file with same format. 11 files are split user wise and user inuputting the remarks against the invoice in coloum Y and Z in their respective files. I want macro/forumul to collect all the remarks coloum from all users to master files against the respective invoice no.

user file format :

file name temp-1.xlsx

A B Y Z
USER
INVOICE
Remark
Follow-up Date

[Code]......

View 1 Replies View Related

Multiple Users In Excel File?

Aug 1, 2013

I was wondering if it's possible for several users to be working in an excel file at the same time? I know that 1 person can be working in a file & other users can look at it in Read-only, but is there any way for more than 1 user to be WORKING in it at a time?

View 4 Replies View Related

Create Subfiles From Master File Using Key To Split Workbooks To Separate Files

Jun 13, 2013

I am trying to make my macro more dynamic for a spreadsheet I am working on. I have this code which splits out worksheets (using the specific names of the worksheets-not dynamic) from a main workbook into template workbooks (which are basically just an empty workbook because I didn't know how else to do it) and then saves them using the paths below. I would like to make this more dynamic by splitting the different worksheets into new workbooks based on a key column in the hierarchy worksheet.

Basically I have a list of accounts in column B with the file name they should be exported to in column A. There are about 30 accounts being split into 6 different region files. Also note that the same account might be listed multiple times in column A (needed to add multiple numbers for other lookup formulas in the worksheets) but that account worksheet will still only be going to one of the six region files and not to multiple regions. After these are copied to an individual file I would like to save it to a location on my computer. All files will go to this location.

VB:
Sub Create_Subfiles()

Dim FDMName As String
Dim FBName As String
Dim DIYName As String
Dim WMName As String

[Code] .....

View 3 Replies View Related

Multiple Users In Excel File At One Time?

Aug 1, 2013

I was wondering if it's possible for several users to be working in an excel file at the same time? I know that 1 person can be working in a file & other users can look at it in Read-only, but is there any way for more than 1 user to be WORKING in it at a time?

View 3 Replies View Related

Create Master Workbook From Multiple Workbooks

Jul 3, 2013

I need to consolidate a lot of information from multiple workbooks all the workbooks are located in a folder, i am not bothered about running each one separately or a group at a time, each work book has ten sheets with each sheet in the workbook being different, it needs to add to the next blank row on each sheet.

View 1 Replies View Related

How To Create Multiple Spreadsheets From One Master Spreadsheet

Apr 16, 2014

I am trying to find a code that will allow me to generate multiple sheets depending on what is selected in one specific column. I have found a code that is perfect for this however I can't seem to set the range. For example instead of copying all of column headings I only need to copy up to column AN2.

View 1 Replies View Related

Consolidate All Data In Multiple Worksheets Of Multiple Workbooks In One Master File?

Jul 12, 2014

I need a macro that would consolidate all data in multiple worksheets of multiple workbooks in one Master file.

All the workbooks will be in one particular folder. The macro should search for data in all the workbooks and consolidate it in one master excel workbook.

I am currently using both excel 2007 and excel 2010. This macro would really reduce manual work as currently consolidating data from 45 to 50 sheets takes an ample amount of time...

View 4 Replies View Related

Macro Error - First Sheet In Each File To Master File

Mar 7, 2012

I am receiving an error at

Set CopyRng = Wkb.worksheet1.Range(Cells(RowofCopyworksheet, 1),
Cells(Cells(Rows.Count, 1).End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column))

I am trying to copy the first sheet in each file in the designated folder and paste it into a master worksheet.

Below is the code.

'Description: Combines all files in a specific folder to Format File for Upload.xls
Sub MergeMultipleFiles()
Dim path As String, ThisWB As String, lngFilecounter As Long
Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet
Dim filename As String, Wkb As Workbook
Dim CopyRng As Range, Dest As Range
Dim RowofCopySheet As Integer

[Code] ....

View 1 Replies View Related

Create Name That Conflicts With Built-in Cell Name

Aug 15, 2012

I am attempting to create the Name "L1" for a list of cells but it conflicts with the built-in name for Cell L1. Is there any way around this if I need it to read "L1" exactly, and not "L_1" or a variation?

View 6 Replies View Related

Multiple Files To 1 Master File

Dec 19, 2013

I have a department of around 20 ppl, each have excel files that can contain upto 10 tabs with those in turn possibly containing upto 500 lines each.

I now need to take totals from each person, linking it back to my one 'master' file.

Now i could do this with various VLookups but speed would be an issue here. I will be working off company servers too, so shared drives are involved.

View 1 Replies View Related

Create Conditional Format In VBA Instead Of Built-in Excel Feature?

Sep 14, 2013

I was able to create a conditional format that does the following:

if any cell in C column is changed, it checks G column...if the time in G column is within two hours of the current time, it highlights in yellow A column (in that corresponding row). The formula that got this working is:

Code:
=AND(MOD(NOW(),1)>= $G1-"02:00",MOD(NOW(),1)

View 2 Replies View Related

Append Multiple Csv Files Into Master File?

Jan 13, 2010

I have about 100 csv files of the same format that I would like to append into a single master file. Order is not important for appending (I can do a column sort later) I can do copying and pasting, but this will take a long time especially because I will creating more master files from completely different CSVs in the future. Is there a faster way to append CSV files?

View 14 Replies View Related

Combine Xls Files Into One With Multiple Worksheets In The Master File

Feb 26, 2014

adapt the code write by TURBO at [URL]....

I'm trying to add more sheets to consolidate the data from different worksheets

What I want it to do is to consolidate each worksheet in every excel file into one workbook that will have the same worksheet structures as the child files

If every excel file has {Sheet1,Sheet2,Sheet3} structure The Master Consolidated workbook should have the same structure but with all the date copied from the child excel files.

Attache it's also an example

Test xls files2.zip

View 5 Replies View Related

How To Copy Fix Range From Multiple Workbooks To Master File

Aug 6, 2014

I have multiple excel files from which I would like to copy specific ranges to a master file. The row should add up to the previous rows. From this side I have no problems things work fine. The problem is to copy the header which remains the same data to the top of the master file. Currently the macro I use overwrite the first line of the master file. So I loose the date from one of the excel files.

View 3 Replies View Related

Copying Files With Multiple Sheets Into One Master File

Apr 17, 2008

How do I write a vb macro that copies everything from multiple files, including sheets within files, and puts them into one master file. Here's what I have so far. I used a script from gnaga that worked great but it didn't copy seperate sheets. If you can help me out, I would greatly appreciate it.

Sub MergeSheets()
Dim SrcBook As Workbook
Dim fso As Object, f As Object, ff As Object, f1 As Object

Application.ScreenUpdating = False
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.Getfolder("C:Temp")
Set ff = f.Files

For Each f1 In ff..........

Set

View 9 Replies View Related

VB- Search Directory, Update Master File From Multiple Files

May 16, 2007

Here is a project I cant seem to do on my own if you can point me in the right direction I would be grateful! here is the best suedo code to describe the needs of the script:

In the open workbook named MASTER, on Worksheet named -MASTER- ( Lets refer to this all as just MASTER)

(In production, the name of this workbook will be an account #_ date, and the worksheet will be a date)

For each numeric value in row 6 (well call these values AD###) of MASTER

'*****Part one of routine*****

Search directory "H:AccountingAdvertising Accounts" for workbook named AD### AX.xls

(Note the AX suffix of the file name)
If match is found open workbook and proceed to Part two

If match is not found, goto Private Sub AD_MISSING

'*****Part two of routine*****

View 10 Replies View Related

Save Macro For Multiple Users

Apr 6, 2009

When saving a file, you get a popup with a suggested path. I need to find a macro that will either offer a suggested file name based on a cell's date value (a1) and a suggested path (to the desktop) for any user that uses the spreadsheet - or simply will save the file using the cell value as the name to the user's desktop.

Each user will have their own version of the file, to save to their own desktop. The macro will need to overcome the issues that each user's path to their desktop will be unique since each will have logged in to the window's session with their own profile.

View 5 Replies View Related

Sharing Workbooks With Macros Built In The File

Jan 15, 2014

Is there a way I can share the workbooks with macros built in the excel file?

View 1 Replies View Related

Macro Definitions - File Name Changes From Master Templates

Apr 10, 2014

I am currently working on a performance document. I am working on the principle of having a master template which people can then access, Save As, and use to monitor performance around KPI's.

The issue I am having is around the name of the document changing when it is saved as, as all records have to retained. The macro I am struggling with is designed to unlock the workbook & worksheet, copy the worksheet specified into a new workbook and then return to the workbook the macro is held within and lock it back up. However, when the name changes it just locks the new workbook rather that the version I am asking it to.

ActiveWorkbook.Unprotect Password:="KPIreview"
ActiveSheet.Unprotect Password:="KPIreview"
Sheets("EID Graphs").Select
Sheets("EID Graphs").Copy

[Code] ....

Is there anyway I can change the “Kent – Monthly Activity & Performance Review – Version 17 – Master.xlsm” statement within the macro to reflect the change in name of the document?

View 2 Replies View Related

Macro To Add New Sheet To Many Excel Files From A Master File?

Jul 11, 2013

I have many folders (around 500) - each of them contains a excel file (the excel files have all the same name), ideally I would like to be able to run a macro from a master excel file that would allow me to add a sheet which I would create in the master excel file and add it to all of the excel files that are in the folders. So far I have used the code from the link: [URL]

VB:
Sub CopyWorkbook()
Dim sh As Worksheet, wb As workbook
Set wb = workbooks("1.xlsx")

[Code].....

but when I change it from 2.xlsx to 1.xslx in the code it works just fine, copying the sheets from the file to itself.

View 2 Replies View Related

Adjust Macro - Imports Data From Several Input Files And Rearranges It In A Master File?

Apr 25, 2014

I have the following macro that imports data from several input files and rearranges it in a master file.I want to change it so that I can use it in each of the input files. Therefore, it should look in the input file for Spreadsheet "XYZ" and rearrange it in Spreadsheet "Data".

[Code].....

View 9 Replies View Related

Macro - Copy Multiple Sheets To Master

Mar 21, 2007

Have 52 weekly spreadsheets in a workbook and would like to create a macro to copy them into a master spreadsheeet in the same workbook plus, would like the master to be continually updated with any changes that are made to the individual spreadsheets.

View 4 Replies View Related

Macro Copy Data From Multiple Workbooks Into Master Workbook

Aug 4, 2012

I have 17 workbooks which contain similar data and formatting. The only variance is the number of rows of data in each. I want to write a macro in the Master worksheet that will accomplish the following

1. Create a worksheet name in the master workbook with the same name as the workbook that it's pulling data from.
2. Copy over the column headings from columns A:P to the newly created worksheet on the master workbook
3. Copy over all rows that contain the word "down" under column D titled Operational status

The naming convention for the master workbook will be titled "All Switches". The naming convention for the worksheets containing the data are titled Port_Map_on_tiegcsw01, Port_Map_on_tiegcsw02, Port_Map_on_nypgcsw01. There are other names , but I can reference the code an update accordingly.

I found some searches on the forum for creating a macro to copy data between workbooks, but not quite sure how to write the macro for the three functions.

View 2 Replies View Related

Macro To Pull Data From Dynamic Named Multiple Worksheets Weekly Into A Master Sheet

Jul 11, 2014

Attached is a master metrics deck, where data needs to be refreshed every week, from two different workbooks.

The catch is, the two parent workbooks would be new versions every week (data would be same format, and style within), but file name would change each week, for eg: for 1st workbook, 'XXX_Weekly_Week24.xlsx' to 'XXX_Weekly_Week25.xlsx' and so on.

for 2nd workbook, 'YYY_Weekly_Week24.xlsx' to 'YYY_Weekly_Week25.xlsx' and so on.

In the master metrics deck, a consolidated vew of some of data from workbook 1 and workbook 2 is presented for each week.

This master file would remain same, and only be refreshed each week (preferably with a button on its sheet "REFRESH!!")

Next, need to manually change cells C2:G2 each week to reflect week titles correctly.

For e.g.: week6 would show week 1 through week 5, but during week7, it needs to show week 2 to week 6. Only last five weeks of data any given time.

The first datarows of each set are conditionally formatted (they are compared with other rows in the set and need to reflect danger values, good values etc) (I have done that already).

View 1 Replies View Related

Create Links To Multiple Csv File In The Same Workbook

Jul 19, 2006

My first post at last.

I've recorded a Get External Data macro and wish to modify source and destination components. I would like to replace the absolute link to the file name with a variable and the Destination range to the active cell at the time I run the macro.

This is the is the macro code line that handles the source and destination components I want to modify.

Range("AQ2").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:filename.csv", Destination:=Range("AQ2"))

View 3 Replies View Related

Copy Data From New Everyday Csv File To Next Empty Row In Master File

Dec 15, 2009

I have csv files auto generated (with date stamp in name) and saved in a specific folder everyday. I need to find a macro that will copy the row(s) every day and add to the next empty row in the master excel file. Some days the csv file may have a single row of data and on some other days it may have multiple rows of data to copy and paste.

View 4 Replies View Related

Use Vba To Create Copies Of Excel Workbook For Different Users?

Nov 26, 2012

I have an excel workbook that serves as a master workbook. I have to create 30 copies of this workbook, 1 for each site. Each workbook will have small differences in the data held. This is because I will refresh the sheet before saving it. There is a dropdown list on the 1st tab of the workbook that contains the names of the sites. The sequense, I suppose would be like: select the next name on the dropdown list"calculate" the spreadsheet to enable the refreshsome sort of "save-as" with the name from the dropdown list forming the name of the file. The Master file must be kept intact.select the next name from the dropdown listcalculate the sheet ........and so on I have a few other things I will add to the script like Protect Sheet etc.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved