I have a workbook which has sales data in one worksheet and inventory data in one worksheet. There around 50 workbooks I get each week one for each customer. The column headers are same for sales and inventory in all the sheets. All the 50 files are in the same folder.
Is there a way to put the sales data of all customers in one worksheet and inventory in one worksheet. Currently I am doing it manually.
I have a problem in Excel for Supply Chain. I want to aggregate a set of data, change the aggregated data and then disaggregate back to constituents using the same proportion in which they were aggregated . I can do aggregation using pivot tables but then I cannot change and do the deaggregation.
Initial Disaggregated data
--------------------
Group Item# Qty
BALL423242254BALL534534555BALL4232422442BALL543535433
BALL534535322BAT53453532BAT5343533222BAT535353222
CYCLE432424222CYCLE4234244222CYCLE43243241313
CYCLE42342257676CYCLE43244226565
Aggregated data
-----------
BALL606BAT246CYCLE15798
Change in Disaggregated data
-------------------------
BALL1000BAT3000CYCLE5000
After the change, I should be able to disaggregate the data using the same co-efficient I used in aggregation
I have three source workbooks that needed to be consolidated into a master workbook pasting the data with values & formatting.
The Master workbook also contains 6 additional worksheets that link to the data pulled in from the source files.
My question is how do I write the code so the source files populate the master in a specific order.
For example, the 3 source files are named "Central" "NED" and "WEST" and I need them to populate the master workbook in that order.
This is the code I am using to consolidate the data:
Option Explicit
Sub ConsolidateSheetsFromWorkbooks()
'Author: Jennifer Starr
'Date: 7/12/2013
'Summary: Open all files in a folder and merge data (stacked) on all
[Code] .....
I have data in 2 workbooks. Book1 - Sheet1 - Col A and Book2 - Sheet2 - Col A. I want to compare Book2 with Book1 and list all values in Book2 that do not find a match in Book1. I want to run a macro for this
View 7 Replies View RelatedYou have two Workbooks open. Workbook 1 and Workbook 2.
You COPY all the data from Workbook 1 and PASTE the data as a 'Link' into Workbook 2.
You then save and close both Workbooks.
You then open Workbook 1 and edit the data. You save and close Workbook 1.
QUESTION
Will the linked data in Workbook 2 update automatically (i.e., without opening the file)?
I have an excel spreadsheet supplied by a client, let's call this the master sheet, with about 500 URLs and I've been asked to gather information from Google Analytics and place in a spreadsheet. This spreadsheet can't be changed as it goes into their CRM system.
On the master sheet, we have a list of URLS, what I need to do is go through each URL and place the number of visits to each URL on a weekly basis.
Google Analytics excel spreadsheet, will spit out a list of URLs and the page visit numbers.
My question is:
If I have both spreadsheets open, the master spreadsheet and the information from Google Analytics spreadsheet. Is there a way of having both sheets open and doing an exact match on the URLs between both spreadsheets, so we can copy and paste the information directly.
Or even a formula that says something like:
If the master sheet spread has the same URL in the Google Analytics spreadsheet, paste the relevant information into the correct cells?
As you can see, doing this manually will take a long time. I'm just looking for a productive method to save time.
Itransferring data across two work books I have.
Every month I get sent sales figures of products, from now I have been typing in every sales figure which is long and boring. So I was wondering whether there is a way to make excel match the products on each workbook (mine and the one I get sent) seeing as there is a product number on both workbooks and then have excel automatically fill in the field I type manually which are quantity and value. So excel would see on the one workbook that product X has sold 10 units with 1000 value and then would go across to my workbook and fill that in the area I need on mine matching from the product code?
I have to workbooks and I want to populate one of them with data from the other. The receiving document has the fields Account and SubAccount which I need to match up with the account and subaccounts in the other workbook.
However, in the other workbook, the line looks like "1000 Wages 000001" (which is account, name, subaccount). I can't find out how to have my receiving document scanning the providing document and when it finds an account and subaccount (in the above string) that matches the account and subaccount in the receiving document, to bring over the data.
I recieve a data file on a monthly basis. Is there a way to take all of the monthly worksheets and combine into one workbook without doing a copy paste every month?
View 1 Replies View Relatedi had received a new project. i want to compare the data among 2 different workbooks. there are book1 and book2 which i attach at here. if there are 1 cells is different with both workbooks, then the whole row will copy and paste to a new workbook "output.xls".
i also attach the "output.xls" at here.
the background color i highlighted which data is different, just for easy to view. so can ignore the color.
A while back someone helped me out with a code to pull data out of all workbooks in a folder.
View 10 Replies View RelatedLook in folder and find workbooks with date in name:
Date format is MMDDYYYY
Workbooks have multipule sheets
Look at columns in worksheet E and G.
If E >=10 and G is "ENABLE" copy A:G and
the tab name and paste in the workbook that has
this VB code.
What I am going to do is create a workbook
with a button on Sheet1 and put your code
behind the button that will tackle this great
feet that I do manually that takes about
3 days to do.
Is it possible too do this if they are on the same computer. Lets say I want all the data on page3 in one workbook, data on page2 in another and have it show up in another workbook. If this can be done can it delete cell entry that match?
View 14 Replies View RelatedI'm trying to copy data from several workbooks into another - in Excel 2003. The following code works perfectly except when there is only the header row in the target workbook. Then I get the following error message, "Run time error 1004. Application defined or Object defined error". How can I get it to find A2 the first time - without coding A2 in?
View 4 Replies View RelatedIs it possible when i am in my current workbook to refer to the value in another cell in another workbook within a formula?
Example:
I have a workbook named "Sample1.xls" that contains a spreadsheet "revenue" and in cell C24 is the value that i want to have in my current workbook.
I am working on a project that requires me to copy columns from multiple workbooks to a main one. I am a new comer to VBA what I could come up with gives me an error. Here's where I am right now:
Code:
Sub Import_Click()
Dim MainFile As Workbook
Dim ComFile As Workbook
Dim RDFile As Workbook
Dim UTIFile As Workbook
MainFile = Dir("C:ModelingProcess FilesMain File.xlsm")
ComFile = Dir("C:ModelingProcess FilesModeling - Commercial.xlsx")
[code].....
I have a series of of quesitons but honestly, I think it'll be answered by ONE question.
This is the my request as described below:
Bob, Jim, and Lisa are Project Managers (PM). Each PM has a Project Template Workbook with required info they need to fill out PER job. This Template workbook has a VBA button that submits their info to the PM Master File.
The PM Master file is a seperate workbook that sums up each project as filled out by the Project Template Workbook.
What I need is the code to either copy/paste the values from the template to the master file ensuring that the most recent addition of information goes to the next row and doesn't paste over the existing data.... or ... I need a different approach.
I have two workbooks book1.xls and book2.xls
I want to load data in sheet1 of book2 to sheet1 of book1.
This sheet1 of book2 has a table of 10 rows and 10 columns.
I'm still using Excel 97. I know I'm a dinosaur but I find it does what I want, just like a Ford Model T would.
I have one workbook (workbook "A") which links to another workbook (workbook "B") when "A" opens.
If I now update and save "B" I find I need to close "A" and re-open it to get the updated data from "B".
Is there anything which might be called "update field"
Spreadsheet 1 is an Excel Spreadsheet. Spreadsheet 2 is a CSV file downloaded daily from UPS. Spreadsheet 2 contains the UPS tracking numbers in a column called Tracking Number. Both Spreadsheets contain a Ship-to- name column. Is there a way to automatically take the UPS tracking numbers from Spreadsheet 2 and place them in a column called Tracking Number in Spreadshhet 1 by matching the Ship-to-name columns between the two spreadsheets?
View 2 Replies View RelatedI want to copy a perticular coloum in one xls sheet ie a.xls(sheet3) coloum to another xls ie b.xls in sheet2(coloum) of that xlsHow can i do this using a macro is their any other better way of doing this
View 7 Replies View RelatedI got 2 workbook called book1 and book2. Book1 is design to be form in the spreadsheet for the user to fill up the particular data. Book2 is the password protected workbook. I created a button in book1 such that when the user click the button, it will automatically transfer the data in book1 (worksheet1) to book2 (worksheet1) without opening up the book2 during transfer data. It simply add a record in the book2 (worksheet1). Can I write such a code to do this?
View 6 Replies View RelatedI need a macro code to combine data from 2 different workbooks to a new workbook.
See attachments for ease of understanding.
Note:
- Data in sheet1 of both book1 and book2 should be pasted in a new book (eg: book3)
- The result I am looking for is like sheet2 of book1 (colour differentiation done for ease of understanding and not required to be implemented in the code). Also since I could attach only 2 books, I have pasted the desired result in sheet2 of book1.
- Also note that headings of book1, book2 and the resultant book3 will be same.
- Both book1 and book2 are stored in the same folder (im not sure if this info helps)
- It is important to note that data keeps changing and new rows will be added in either book1 or book2. So when I refresh the resultant book3, it should add these rows that were previously missing.
- First it should paste all data from book1 (old and new rows), then it should paste all data from book2 (old and new rows).
- I think the sheet2 of book1 will help you to understand what I mean by the above note.
The above exercise is for me to get a logic. In the actual scenario, there are more than 2 books to pull the data from. And also there are more than 20 columns to pick data from.
I am using the following code to attempt to pull data from closed workbooks. Something in the code is not working and i can't figure it out. ARG is resulting in the path i need with the exception of TOTAL which is the sheet i need the data from.
Private Function getvalue(path, file, sheet, ref)
'Retrieves a value from a closed workbook
Dim arg As String
' Make sure the file exists
If Right(path, 1) <> "" Then path = path & ""
If Dir(path & "k217811.xls") = "" Then
getvalue = "File Not Found"
Exit Function
End If
Trying to solve a problem. I have two excel files one called test.xls and the other is test1.xls. I want to transfer the contents of a worksheet in test.xls called data to test1.xls and onto another worksheet called data1 (within test1.xls). I do not want to overwrite the test1.xls file but simply move
test.xls!data to test1.xls!data1, keeping any existing formulas etc on other worksheets within test1.xls intact. Anyone konw some vba code as a macro that could do this for me? Version of Excel is Mac 2004
I am looking for help with a macro that will match up line items from two different spreadsheets. Basically I have a master accounting sheet which has the Income and Expenses for a building that dates back quite a ways. Each month I grab the financials and add it to the master sheet. The problem is that the master sheet has many more line items than the current month, so it takes me 5 or 10 minutes to match up the line items.
I have attached an example. The "Master (Before)" sheet is the historical financials. The last two columns are the new month before it has been lined up. The "Master (After)" sheet is what it should look like after the line items are matched up.
Line Items.xls
I am looking for the easiest way to find duplicate Work Order numbers that exist in 2 separate Workbooks. EX. Workbook 1 Sheet one contains the numbers 1-100 in A1:A100
Workbook 2 Sheet one contains X amount of the numbers between 1-100 located.
somewhere in A:A. For arguments sake let's assume those numbers are 3,6,33,87,99.
What would the formula be to return the values that are in both of the workbooks?
I wish to be able to get data from several workbooks (.xls), and gather all the data in one master workbook.
As I said, I've got a number of workbooks with data I wish to copy and merge into one master worksheet. Each workbook contains a sheet called "MILESTONES" (project) in which I want to pick up data. The data ranges from A5:J88, but I think this can be expanded by the users (more rows). There is no guarantee that the users will enter data in all the columns. Columns A and B occur frequently blank, so the users usually starts entering in information in column C (heading = description).
The Master workbook contains of exactly the same headings, except that the columns has been expanded by 1. Column A1 has the heading "Workpack" which is the name of the workbook the information has been gathered from (description is in column D1). This name can be found in a sheet called "REPORT" in all of the workbooks, and I want this to be copied over, row by row so it matches the data. I don't know if it can cause any trouble, but the names are merged through C to J in row 12 in all of the workbooks.
The workbooks (or workpacks) and the master workbook (summary) are placed in the same folder. Let's say C:Project. The workbooks also changes names frequently (each month). Lastly, if there is a way to automatically update the master workbook every time it is opened.
I have a whole group of workbooks. Is there a way to make a new workbook that will look in the folder containing all of the other customer's books and pull information from them? In other words, I want a workbook where in Column A would contain all of the Customer's Names (Cell Info!G6), Column B would contain the Insurance Company (Cell Info!M14), etc. Is there a way to to that?
View 14 Replies View RelatedA) Is this even possible to do in Excel? I've seen similar ideas in foums, Excel help, etc., but they are all limited to worksheets within a single workbook.
B) My front half logic will work if I can do the back half. But if that's not possible, is there another way to accomplish this?