Name Two Ranges On Four Tabs In Workbook Across Multiple Workbooks
Apr 12, 2013
I have timesheets in work, where i created basic formulas to display times & worked hours etc. I also have named ranges on 4 of the sheet.
The sheets are named as the department (area 1, stock control etc.). All the sheets are identical in terms of layout, only the names of staff and hours worked are different.
When i created them, i tried to be pro-active and created flippin loads, through to October this year. Problem being i created a Summary sheet for each workbook, which in short takes all names from all areas and collates them on the last tab, puts Mon-Sun across the top, enters the hours into the relevant days, merges hours worked in different areas and also splits the hours worked over each day.
Everything is working flawlessly.
Except, the above evolution of the sheet was done after i mass created the timesheet, and as such i need to manually open each sheet, set the ranges and name them, add a summary sheet, add the macro, save and close.
Is it possible to create a macro on book1, that when i open however many other workbooks (the actual timesheets) the code would set and name the ranges for me?
The time sheets are all named according to the week -> 'W.C - 08.04.2013', 'W.C - 15.04.2013' etc.
View 1 Replies
ADVERTISEMENT
Nov 23, 2008
There are two teams in my department, and each is assigned to maintain their respective work book and I'm looking to link them in order to save some time.
Team A - Responsible for receiving Invoices (Bills) and entering them in an excel spreadsheet when received and update when bill is paid. Only one tab in this workbook.
Row A - Name of company billing us
Row B - Invoice #
Row C - Invoice Amount
Row D - Once Bill is paid the check amount is entered here
Row E - Balance Due (Row C - Row D = Row E)
Team B - Is Responsible for maintaining a list of all checks issued. All of the checks issued to pay the bills received by Team A are entered here plus other checks to pay a variety of different stuff. On this workbook a new tab is created every month. One tab per month. Since we need to follow accounting rules and record the check NOT on the month it was paid, but on the month the service was provided. for example I might be paying a bill in the month of November for services that were provided in September, so I would need to enter this check in the September Tab.
Row A - Name of company check is paid to
Row B - Invoice #
Row C - Amount Requested to be paid
Row D - Reason for payment
Row E - Date of check issued
Row F - Amount paid
Row G - Check #
Here is what I want to do.
I want to link both of these workbooks so that when Team B fills out the information of the check issued this will automatically update the Workbook of Team A so that
the balance is zeroed out.
He is my challenge. Workbook of Team B has multiple tabs so I can't just do a simple Vlookup and also every month a new tab is created (very dynamic workbook).
TO add to this in Team B's worksheets have to be in alphabetical order, which means that rows are inserted everyday. for example if I paid yesterday to A and C, I enter company A in Row1 and Company C in row 2 but today I received invoice from Company B so in order for them to be alphabetically I would need to insert a row between Row1 and Row2. So if I had links to this workbook they wold not update when the new row is added.
View 14 Replies
View Related
Jul 30, 2004
I have two spreadsheets in different workbooks ( workbook 1: sheet 1 and workbook2: sheet1), here i need to compare column 5 in Book1 and Column 5 for all cells, say X is the value we are looking for..
X occurs once in book1 and might occur more than once in book2..so if a match occurs ( that is once the code checks that there is X occuring in both books in columns 5) it should copy all rows in book 2 where X occurs to a new workbook 3 in sheet 1 and also it shoud copy entire row data where X occurs in book 1 sheet 1 . But this data from book 1 has to be copied at the end of row after the data from book 2 has been copied.
if X occurs 4 times in book 2 , then 4 rows have to be copied in book 3 and then data from Book 1 where X occurs only once is copied 4 times at the end of the data from book 2.
this process has to repeated for all cells in columns 5 in book1 and column 5 in book2 .
Sub Find_Matches()
Dim M, N As Range, x As Variant, y As Variant
Dim NewRange As Range
‘ To get the book1 location
MsgBox " Selec the Location of N File"
Application.Dialogs(xlDialogOpen).Show arg1:=""
ActiveWorkbook.Activate
Windows("N.xls").Activate
Sheets("sheetA").Select .......................
View 9 Replies
View Related
Dec 7, 2008
I want to be able to prompt a user to select a sheet to import to another file. The ranges are as follows.
Sub Importtimesheet()...
View 9 Replies
View Related
Feb 27, 2008
Is there anyway to simplify multiple copy and paste from one workbook to another when there are multiple and different size of data to copy. this is what i have done below. It was a bit tiedious writing it all out
Workbooks("AVA_DA_140906_BPL_SSE_001.csv").Worksheets("AVA_DA_140906_BPL_SSE_001").Activate
Range("E3:G110").Copy
Workbooks("DailyAvailability.xls").Worksheets("Availability").Activate
Range("E17").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("AVA_DA_140906_BPL_SSE_001.csv").Worksheets("AVA_DA_140906_BPL_SSE_001").Activate
Range("C3: C110").Copy
Workbooks("DailyAvailability.xls").Worksheets("Availability").Activate
Range("H17").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ ...
View 4 Replies
View Related
Dec 6, 2006
I need to create a macro to find matches between multiple specific workbooks and a named range. I am new to macros and am very confused. After searching the forums here, I am still lost, even though they are very informative. So far,I have created a Dynamic Named Range called NamesList
=OFFSET(CurrentDay!$B$2,0,0,MATCH("*",CurrentDay!$B:$B,-1),1)
that selects the cells which I update manually each day. Once I have updated the list of names, I need to create a macro that will decide whether or not the names in the dynamic named range CurrentDay (located in workbookA) already exist in workbookB. The macro needs to create a list of the which names already exist in the workbook, and which do not. For the names that do not exist in workbookB, I then need to search workbookB and workbookC for matches, again creating a list of results. Until now I have been manually using Ctrl + F (Find Method)and going down my list of names to search through each workbook.
View 2 Replies
View Related
Aug 11, 2013
how to insert multiple tabs in the workbook?
i try this
Code:
Option Explicit
Sub Add_Multiple_Tabs()
Dim Tabs As Variant
Dim I As Byte
[Code]...
View 7 Replies
View Related
Jul 28, 2014
I have about 30 Excel files, each with multiple tabs. Each file has a tab called "Invoice." I would like to create one workbook with all 30 Invoice tabs. As of not I am manually copying and pasting, which takes a while. There has to be an easier way, and I found this VB script to combine entire workbooks.
Is there any way I can run this and get just that Invoice tab and not all tabs on all files?
Sub GetSheets()
Path = "C:UsersdtDesktopdt kte"
Filename = Dir(Path & "*.xls")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub
View 3 Replies
View Related
Feb 21, 2014
I have 2 workbooks. The workbooks have 36 tabs for each department. Workbook 1 are current charges for the departments and workbook 2 is the revenue for each department. I would like to take the top 2 rows from workbook 2 and add them to workbook 1 at the bottom of each tab. I was able to get it to work to a specific row but the workbook 1 has variable rows for each tab.
View 3 Replies
View Related
Oct 7, 2009
I want create a macro which will extract 6 values (see below) from a workbook tab called summary to an master workbook for reporting purposes. Each workbook has a unique file name e,g ACI1150.
Values on sheet SUMMARY:
A1
A2
A3
G21
G24
G26
I tried to adapt the below to get one item copied/extracted. However it would no work.
I am new to using macros
Sub GetG26s()
Dim MyDir As String, FN As String, SN As String, NR As Long
Application.ScreenUpdating = False
View 9 Replies
View Related
Mar 16, 2008
I have about 20 workbooks with different file names for different projects all saved in the same folder. Each workbook has about 10 worksheets and each worksheet is named in a similar fashion in each of the 20 workbooks (eg. revenue, cost, variance etc.). I want to pull out a worksheet named ' forecast' from each workbook into a master workbook so that the master workbook would contain the 20 forecast worksheets.
View 9 Replies
View Related
Sep 22, 2012
I have been trying to get Excel (2007) to change the colour of tabs to match other tabs in the workbook
Using this code I get the colour code of the current tab
Code:
x = ActiveSheet.Tab.ColorIndex
But when I use this value in a procedure like this:
Code:
ActiveSheet.Tab.ColorIndex = x
The tab is a different colour!
View 3 Replies
View Related
Nov 19, 2012
I have a database from which I extract 5 different excel files. For arguments sake, they are called File 1, File 2 etc. Each of these workbooks will have a sheet called 'Raw Data'. These will all be save in the one directory, C:Data, for example.
I am after a macro that can copy all the data from each of the workbooks, on the sheets called 'Raw Data, however the data in each of the workbooks will vary in length and width.
The workbook which I want them copied to is called "Template" and I would like the copied data onto separate sheets in this file. If possible I would like them copied to already named sheets, for example in the "Template" file, sheets may be called "Raw Data1", "Raw Data2" etc.
View 6 Replies
View Related
May 31, 2013
I have this master file with a list of workbook names in column A on a tab called, conveniently, List. I have saved the master file in the same folder as a number of workbooks where their names match the names on the List.
What I want to do is open the files on the List and copy and move the worksheets to a new workboook. The catch for this is the following (I will use an example):
1. I have 2 workbooks called DS-100 Medicine Admin and DS-101 Medicine Wards
2. I want to take the worksheets from DS-100 and add them in a new workbook
3. Then I want to take the worksheets from DS-101 and add them to the same workbook but AFTER the DS-100 worksheets that were added in step 2
4. Then save this workbook to a folder within the original folder where the files are saved
I have the below code thus far, which opens the DS-100 workbook and copies and moves the worksheets to a new workbook. It doesn't open the DS-101 workbook though. It also saves the new workbook in My Documents rather then the SavePath.
Sub CombineMedicine()
Dim Path As String 'string variable to hold path to look through
Dim FileName As String 'temporary file name variable
Dim Master As Workbook 'this workbook
Dim rng As Range
[Code] ...........
View 1 Replies
View Related
Jun 3, 2014
I found a great code for copying worksheets from multiple workbooks (up to 2000 workbooks) into one new workbook. It works perfectly for 99% of the copying/merging that i do. The only issue with the code is that it only copies from the active sheet in the other workbooks. I need it to copy from a specific sheet ("Travel") in all of the workbooks that i select. I've tried many ways to edit the code to change the activesheet to "Travel" but i cannot get it to work.
Option Explicit Sub CombineDataFiles() Dim DataBook As Workbook, OutBook As Workbook Dim DataSheet As Worksheet, OutSheet As Worksheet Dim TargetFiles As FileDialog Dim MaxNumberFiles As Long, FileIdx As Long, _ LastDataRow As Long, LastDataCol As Long,
[Code]....
View 5 Replies
View Related
Jul 4, 2008
I need to copy multiple workbooks of similar format into one new workbook called "ZZZ.xls". I have developed the below code but I got an error :
Run-time error '1004':
Paste method of worksheet class failed.
Sub create_intransit_stock_FOR_RCP()
Dim Wk As Workbook
Dim FileToOpen As Variant, i, j, k As Integer
Dim NewWBName As String, NewWBName1 As Workbook
Dim OutFileName As String
OutFileName = "C:My_DataZZ.xls"
'On Error Resume Next
FileToOpen = Application.GetOpenFilename(FileFilter:="Microsoft excel files (*.xls), *.xls", Title:="Press CTRL Key to Select Multiple Files", MultiSelect:=True)
If IsArray(FileToOpen) Then
For i = LBound(FileToOpen) To UBound(FileToOpen)..............
View 9 Replies
View Related
May 31, 2007
I'm attempting to take a Workbook that contains 1000 Worksheets and split it into 2 separate WB's of 500 WS's each ... basically half it. This could also apply to a WB that contains less than 1000 WS's, but it is highly unlikely it will ever contain more than 1000 WS's.
I'd like each new WB created to be saved in the original path with the original filename followed by a "1", "2", "3" or "a", "b", "c", etc. For example:
Original Filename = ImpactAnalysis.xls
New Filename 1 = ImpactAnalysis (1).xls or ImpactAnalysis (a).xls
New Filename 2 = ImpactAnalysis (2).xls or ImpactAnalysis (b).xls
I've located 2 modules in the forum that are a good start, but each creates a separate WB for each WS, which is a bit cumbersome with up to 1000 WS's in the original WB.
Sub splittest()
Dim sht As Worksheet
Dim w As Worksheet
Application. ScreenUpdating = False
Application.DisplayAlerts = False
For Each sht In Worksheets
Set neww = Workbooks.Add
sht.Copy neww.Worksheets(1)
With neww .....................
View 9 Replies
View Related
Jun 14, 2007
I have 2 Excel templates using lookups to create customer quotations. When complete a macro is run which strips out formulas and links to data.
I would like to record certain data from the finished workbook in another workbook on the server e.g. quote number, date, customer, total value, follow up date.
Is it possible to include another macro that records data in certain named cells to a separate workbook when the file is saved?
View 9 Replies
View Related
Jun 12, 2013
how to consolidate multiple workbooks into the master workbook? I need to consolidate 12 workbooks into the master workbook every month. The subsidiaries will report me their figures monthly. Hereby attached one of the subsi, "B Co" reporting package, and the master copy "XYZ Holding Co" how it look like. Hence, may I know how to write a macro so that it will auto update monthly when the subsidiaries return me their reporting package, so that I can auto update in my master copy for tab BS and tab PL.
View 6 Replies
View Related
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
Jul 23, 2013
I would like to merge multiple workbooks into a master workbook. All the individual workbooks are identical and only have 1 sheet, and I would like to append them to the master as worksheets then sum them all together. The front sheet showing the totals from all the merged ones.
View 5 Replies
View Related
Aug 13, 2014
I'm trying to set up an excel that will allow me to gather data from multiple workbooks and get it into one master worksheet. All of the worksheets are formatted the same way (See below)
Employee Last Name
Employee First Name
Employee Position
Employee Series Number
Departing City
Departing State
Attended Pilot Training?
We don't have the data yet so the idea is to paste this information into their perspective worksheets and then have them automatically populate into the master tab.
View 1 Replies
View Related
Dec 27, 2012
At our small firm, each staff member keeps a small excel spread sheet on the network that contains their "work in progress." This spreadsheet is roughly six columns wide and roughly twenty lines long.
What I am attempting to do is have one "master" spreadsheet with a tab for each staff member that links their "work in progress" so that partners and managers can easily see how much work each of the fifteen or so staff members have.
I can create fifteen different tabs and fifteen different files on the network and link =[Staff1.xlsx]Sheet1!$A$1:$G$25 =[Staff2.xlsx]Sheet1!$A$1:$G$25 =[Staff3.xlsx]Sheet1!$A$1:$G$25 and so on.
If I make a change to the layout of the work in progress sheet, I have to delete and copy the file fifteen times, then go back through and re-link fifteen tabs to fifteen workbooks in order to keep it uniform.
View 1 Replies
View Related
Feb 13, 2014
I have a series of workbooks which will be completed by different people and emailed back to me and I am looking for a macro that can take them all (they will all be located in the same folder) and reassemble them, so that:
a) the header row (which is the same on each sheet) only appears once in row 1
b) the drop downs and formula remain intact throughout on the reassembled sheet
I've attached some sample sheets, basically I would receive back the sheets, North, South, East and West (although they wouldn't be called that) and I would like to be able to run a macro that results in the sheet called 'All', which maintains the dropdowns and formula. I could either start with a blank sheet for the reassembly or a sheet that only has the header row, either could work.
View 2 Replies
View Related
Jun 3, 2009
On a weekly basis, I receive a single worksheet in a workbook that contains ~30,000 rows of product sales.
Row one contains column headings that is unique to all other rows. Column A contains the store number that sold the product. There are ~50 unique store numbers.
I am trying to create a macro that will break the report up into seperate workbooks.
For example, assume in column A there are 30 rows of data for 3 different store numbers (say store 112, 386, & 798, each with 10 rows of data). I want to create 3 new workbooks and include the same column heading for each. For example, name new workbooks as follows: "Store 112", "Store 386", "Store 798".
View 7 Replies
View Related
Feb 21, 2010
I am trying to combine ~300 workbooks into one single workbook. All 300 workbooks have the exact same header. I tried using the code from thread http://www.excelforum.com/showthread.php?p=696435 but nothing is being copied over. The only difference between my example and the other is I only need to take data from the first sheet in each data workbook. All the workbooks are located in following directory .....
View 9 Replies
View Related
Aug 22, 2012
I about 150 different workbooks that I need to copy the cell data from the first sheet to a second workbook
The code is running all the way through to the "Clear values?" pop-up box, BUT nothing is actually being pasted into my second workbook
Sub TransferData()
Dim wkb As Workbook, wks As Worksheet, LastRow As Long
Dim FilePath As String, FileName As String
Dim ws As Worksheet, blnOpened As Boolean
'Change these variables as desired...
FilePath = "C:UsersPipeline2DesktopOveralnd Focal Points" 'change path here
[code]....
View 2 Replies
View Related
Nov 10, 2009
I do environmental testing for multiple hospitals and surgery centers. I've created a master workbook in excel 2007 that includes about 7 sheets. Each sheet is for account info, testing areas, billing, and reports. In the testing areas sheet, I've used a formula to compute in column F a return date for each row based on when the area in that row was tested AND based on that area's yearly schedule (quarterly, semi-annually or annually). The date tested info is in column E of each row and the yearly schedule is in column H of each row.
Every client has their own workbook created from the master workbook template. I would like to export the return dates data in column F of each workbook into a single workbook that would tell me what testing I have to do for each client in any given month so I don't have to go to each workbook and make a list manually. So, not only the date would have to show, but also all the other info in that row (acct number, location, charge, etc). BTW, the data in this testing areas sheet I want info from is not in a "table"...just in cells.
View 9 Replies
View Related
Aug 13, 2013
I have a folder set up on a network drive which contains a workbook for each member of staff (approx 40) to track queries that they receive on a weekly basis Each one of these is called "Query database - Joe" for example. The queries are stored on a worksheet called "Query data" within this workbook and they need to be collated on a weekly basis on a Monday morning and cut and pasted into a master database worksheet again called "Query data" to then carry out analysis on these.
What I am looking to do is have a macro set up that will automatically cut and paste the data that has been input onto each individual worksheet into the master spreadsheet, starting from the next blank line. Each option I have found seems to be set to copy a specific range, or paste to the start of a worksheet each time, rather than finding all of the 'new' data that is there for that week and then adding it to the bottom of the master sheet.
View 9 Replies
View Related
Jan 9, 2014
I have three workbooks. Every workbook has 50 persons IT information. Those are CPU list, Monitor list, UPS list. I would like to create another workbook. Where all user's IT information will be available. Like X user's information required, under x information his CPU, monitor and UPS information link will be available. How to create link of the CPU, UPS monitor sheets with new workbook?
View 4 Replies
View Related
