Copy Data Between Sheets & Summarize

Sep 8, 2007

I am trying to write a vba code which is attached to a button, that will do the follwing.
Take the data from one sheet and summarize it onto another one.

I'm not sure if I am going about it the correct way. My approach was going to be to copy all the data onto another page, sort it by columns A, B and C. But I've run into problems, I've attached a sample worksheet showing what I'm using and how I want to use it.

As well this is part of my code I started coming up with, am I on the right track?

[EDIT]
Ok so I've been playing around and thanks alot to bryce for making copying simplier, this is what I've gotten for my code so far (its a lot simplier than before)

Sub uTotals()

Dim wsData As Worksheet, wsResult As Worksheet
Dim DataRow As Long
Dim x As Integer

Set wsData = Worksheets("Material")
Set wsResult = Worksheets(" Totals")

DataRow = wsData. Range("A65536").End(xlUp).Row

For x = 1 To DataRow
wsData.Range("A" & x & ":" & "C" & x).Copy wsResult.Range("A65536").End(xlUp).Offset(1, 0)
wsData.Range("F" & x).Copy wsResult.Range("D65536").End(xlUp).Offset(1, 0)
wsData.Range("I" & x).Copy wsResult.Range("E65536").End(xlUp).Offset(1, 0)
Next x ...

View 8 Replies


ADVERTISEMENT

Summarize Data Meeting Criteria, From Multiple Sheets

Jun 30, 2009

I have a "SUMMARY" sheet and then five other sheets. I need to be able to extract the data which is >0% from each of the individual sheets and capture the results in the relevant section of the "SUMMARY" sheet.

For example, the data from sheet "PAYG" would need to be entered into cells F148:AJ155.
In each instance it is just the "model" detail I need to capture (column D)

View 5 Replies View Related

Summarize Data From Columns In Multiple Sheets To A Single Datasheet Of Rows

Jun 19, 2014

I need a macro that will take hours by day (columns) by service (rows) per client (sheet) and summarize the data into one database of rows containing client, service, date, and hours. The "Summary of Charges" should only include service hours > zero. I am attaching a sample file. I have little to no experience with vba so I don't even know where to begin. I can copy code.

View 4 Replies View Related

Loop Through Specific Ranges From All Sheets To Summarize Data In Report Sheet

Jul 1, 2012

i attached link to sample and its contains employee sheets and Report sheet ,and in Report sheet there is comment

[URL]

View 7 Replies View Related

Copy Data From Sheets In Workbooks In Folder To Main File Sheets Of Same Name

Aug 29, 2008

I would like to use VBA to search a folder and copy data from tabs within the excel files there. The data will be pasted to a tab of same name in the the main file. All the files are in the same format.

So far I have only managed to list the files in the folder using code I found on your site!

View 7 Replies View Related

Select Multiple Sheets And If Value In Cell Is True Then Copy Values In All Sheets And Hardcode Data

Feb 26, 2012

I have a workbook that updates from external source and creates sheets depending on a cell range.

I have put tab 1 and tab 0 on either end of where the new sheets will be inputted, will never know how many sheets

What i need to happen is if someone fills in "complete" in A7 in my "summary" sheet then the values in row 6 in all the other sheets get hardcoded. This needs to happen from A7 down to A26, so A8 = complete then copy row 7 etc
This is what i have so far

I get compile error here ........Sheets(ArrSh(1)).Activate

Also need it to work for all the other rows.

Sub hardcode()
'
'Sheets("Summary"). Select
If Range("a7") = "complete" Then
'
Sheets(Array("1", "0")).Select
Sheets(ArrSh(1)).Activate

[Code] ......

View 2 Replies View Related

Summarize The Data By VBA

Jul 5, 2007

Date ModelStationLot_SizeSampleReject

04/7/07APCA771
04/7/07CPCA64320
04/7/07BPCA36320
04/7/07BPCA441
04/7/07CPCA110
04/7/07AB/B880

How can I quick summariz these data by VBA ? as below result.

Date ModelStationLot_SizeSampleReject

04/7/07APCA771
04/7/07AB/B880
04/7/07CPCA65330
04/7/07BPCA40361

This is just my sample data. The real data is more complicate than this. I want someone suggest me about VBA code.

View 9 Replies View Related

Sumif - Summarize Data

Mar 22, 2007

I am trying to summarize data for my boss,

She has a spreadsheet with all of the employees of the company listed. Each employee is associated with a cost ceter (there are several hundred employees, and about 90 cost centers). Each month we count how many people are in which cost centers. I am using a sumif equation, and that works well, however, we keep the historical data, as well as the budget data in the same spreadsheet, so each month the "sum_range" column needs to change (ie column 4 for feb, 5 for March). I can use a find and replace to address a different column in my equation, but my boss would prefer something simpler.

View 9 Replies View Related

Summarize A Table Of Data

Jun 20, 2006

I use INDEX and MATCH equations quite frequently now and they are invaluable! However what I'm trying to do now doesn't quite fit. I've attached a small sample of my workbook. The sheet called 'data' has three columns of data that I need to split into a grid (called summary). I'd know how to do the opposite of this, as in changing from a grid to a list by using an index and match. But I'm not sure how to do this the opposite way around?

View 3 Replies View Related

Summarize Data In Another Layout

Aug 25, 2006

I have a data feed that gives me a summary of a set of data. I want to be able to work back to what would be the original data (this unfortunately isn't available). The attached excel file as an example of what I am trying to do.

I am not too sure about the working with the dates etc.

View 9 Replies View Related

Summarize Data From All Worksheets

Oct 4, 2007

Im creating a workbook for my office. The workbook has a sheet for each user. The user chooses a channel from the drop down list, then complete the next 5 fields (must add to 100). I have a totals sheet where I want to summarise the data from the whole workbook as per the template on the totals sheet. Ive attached my example.

View 3 Replies View Related

Summarize Data By Criteria

Apr 11, 2008

I have the attached spreadsheet that has 122 columns I would like to create several common button at the top left hand side to summarize my information. The columns that I would like to are name, id, partner.

View 2 Replies View Related

To Summarize On List Of Data - Can't Pivot

Aug 21, 2007

I have included some example data and what I need to be able to show in the attached file.

I have tried using pivot tables but need to keep the totals static when I hide a column.

Can anybody suggest another way of achieving what I need? I need the solution to be dynamic (like a pivot table) but need to hide certain data and have totals including the hidden data.

View 14 Replies View Related

Summarize Customer Order Data

Jul 3, 2009

I have created a simple order form to take customer orders . I would like to summarize the orders on a summary page .(The order Form will be very long so i don't want to print the entire document every time .

I have started to create a summary page using information from a similar post on this forum. I am having trouble creating a summary when the products on my order form have multiple sizes rather than just one

View 9 Replies View Related

Summarize, Report & Print Data

Aug 28, 2006

It does everything I need it to do but I would like it to sort 3 columns and then merge the data. I have included a sample with a "WishReport" tab the shows what I am looking for. If possible (not imperative) I would like it to add a "unit cost" column and provide a grand total.

Option Explicit
Public Const SZ_INPUT As String = "SheetData"
Public Const SZ_OUTPUT As String = "Report"
Public Const I_FIRST As Integer = 0
Public Const I_LAST As Integer = 1
Public Const I_DATA As Integer = 2
Public Const ROW_HEADER As Long = 1
Public Const ROW_DATA As Long = 2
Public Const COL_FIRST As Integer = 1
Public Const COL_PROJECT As Integer = 2
Public Const COL_LAST As Integer = 5

Sub CombineProjectData()
Dim lInputRows(I_FIRST To I_DATA) As Long
Dim lFirstInstance As Long, lOutputRow As Long
Dim szData As String
Dim iCurrColumn As Integer
Call FindRowArray(lInputRows(), , COL_PROJECT)...............

View 5 Replies View Related

Summarize Data Spread Across Workheets

Oct 23, 2006

Is it possible to consolidate data from various worksheets into a single worksheet using multi reference points or lookup_values?

For example, I have 2 worksheets named Section A and Section B in which I have detailed monthly expenditure details (e.g. overtime, allowance, uniform, etc) for each sections, respectively.
Then in the 3rd worksheet, called Combined, I have 2 columns - first is 'MONTH' and second, 'SECTION'.
Now, suppose I have a 3rd column in which I want to pull over the overtime expenses for both sections. That means, my VLOOKUP must first look for the SECTION worksheet, then find the MONTH before returning the value found in the overtime column.
How can I do this?

View 9 Replies View Related

Summarize Data In Pivot Table

Dec 30, 2006

I have a worksheet with several columns of data that consists of month to date billing, cost, profit, etc. that are tied to job numbers.

I currently have code ( AdvancedFilter) that seperates this data judging by the first two numbers of the job number. For instance it will take all job numbers that start with 11, copy them to a worksheet, print it and then do the same for 12, 13, 21, etc..

View 4 Replies View Related

Copy / Paste Frequently Changing Data From 4 Sheets Into One Master Data Sheet

Jul 4, 2012

I have a workbook that includes 4 seperate sheets that are used to record time and expenses for 4 members of staff. I want to write a macro to select the data I need from each sheet and colaberate together in a 'data' sheet so I can combine all the info to run time and expense reports per client showing combination of all time and expense incurred from all 4 staff.

I have named cell ranges in each of the 4 time-sheets. I proceed to record a macro, select the first named range, copy and paste into my data sheet, do a control home then control down arrow, then one more down arrow to get to the first blank cell and repeat the process for all four time-sheets.

This works until I add a new line and then the data will only appear for the last time-sheet (last row of data).

View 2 Replies View Related

Copy Data From Different Sheets Weekly Into Master List With Data Of Whole Year

Mar 8, 2014

We have folders of daily cash collections stored in such a manner, yearmonth. In every month, we will have worksheets sent by the end user to the finance dept, naming it using mmdd. (The folder in the drive will reads: C:Daily Cash Collection2013), (C:Daily Cash Collection2013 0104.xlxs), (C:Daily Cash Collection2013 0115.xlxs). I intend to put the master list outside the year folder, meaning, in the Daily Cash Collection folder (C:Daily Cash CollectionDCC_2013.xlsm). When I have a new folder for year 2014, my master list will be here (C:Daily Cash CollectionDCC_2014.xlsm)

I am looking to automate this opening of all the daily worksheets, select all data except the header row, and copy it into a master list (which will be data for the whole year, with 3 months of the previous year data).

The data in the daily sheets, it will have collections of the same Debit Note number from the file sent earlier. Meaning, if the file was sent on 0104, there is a DN0114-0002, collection of $50. In another daily sheets 0115, it will also have a collection of DN0114-0002 of $20. This 2nd information of $20 will also need to be captured as the payment in 0301 is partial and incomplete.

I will need to copy the daily sheets into the master list every now and then. Is there a way to check and copy the daily sheets and not repeating it and missed out one?

In another words, if I had already copied Jan sheets into the master list, will it look for the next worksheet that I had not copy and copy according the DN number? (it will be in running number but sometimes will have DN of the previous month due to the partial payment ealier).

Alternatively, if this is too complicated, how to insert a macro to copy all cells except the header (will be in fixed column and the first row will be fixed) from an open daily sheet, find the last row in the master list and copy it to the master list, and after copying, unclear the selection of the daily sheet and close the daily? Where can I put this macro as the daily sheet is from the end user. I can only put it into my master list, but my problem is, the file name of the daily sheet is not fixed, it depends on the day the end user saved and email the data to Finance Dept.

DCC foler.jpg

View 10 Replies View Related

Summarize Quarterly Data In The Summary Table?

Aug 7, 2014

summarize quarterly data in the summary table taking into consideration the months and the district. see the attachment for a more clear picture.

View 4 Replies View Related

Summarize Daily Data Into Weekly Average

Jan 17, 2009

I have two time series which span several years. The first series measures stock levels on every Friday (52 values a year). The second series measures the price level every weekday (260 values a year).

I'd like to condense the daily data in to a weekly average, can I do this easily? For example, I could manually use the Weeknum function to calculate the week number of each daily price data, then find the average daily price for each week, thus giving me 52 values which I can compare to the weekly stock series. Is there an automatic, fast way of doing this? Alternatively, I'd be happy to settle with a monthly average. Is this possible via macro's or does VBA need to be used?

View 5 Replies View Related

Summarize Data Based On Multiple Criteria

Jan 6, 2010

I have a worksheet with several columns of data that I have to summarize elsewhere on the sheet. Here are the columns:

ID
Status
Joined
Name

Name can take on 4 possible values. For each value in Name, I want to count the number of cells in a column that fit certain criteria. Here are the counts I want to make:

Status OK: Status is a date.
Joined OK: Status is a date and Joined is a "1".

There are going to be about 500 records to count. I've already implemented these counts in a Windows scripting language that interfaces with Excel, but I know there must be a more direct and faster way to do it entirely in Excel, whether with VBA or writing functions into cells.

View 3 Replies View Related

Data Validation - Summarize Three Lists In One Table

Jan 4, 2014

I have three lists and I want to summarize them in one table. I can do this by formula or using Data validation by position. Who is the best way? There is a difference between them?

View 3 Replies View Related

Index Match Vs D Function To Summarize Data

Oct 24, 2008

I have a project that has several worksheets of "summarized" data all pulled from one worksheet. The data worksheet is run through an external program and pasted into the workbook to update the information.

To this point I have used tons of index match array formula's to pull the information but I've found this to be extremely inefficient because there are hundreds if not thousands of formulas, and it takes several minutes to update. The file is also around 3MB which is way too big.

I was thinking about using VBA to simplify the worksheets, but then I came across an article about D_Functions and I'm thinking they may be the solution to my problems.

However there is one catch...I'm not sure how I would build these formulas to work in my worksheets. I understand the principle behind the D_Functions, but I can't seem to grasp the concept of integration and making it work in my scenario.

I've attached an example of this workbook, it has most of the forumla's deleted out to reduce the file size, but it should give you an idea of what I'm doing. Once you open it you'll have to unhide some columns to get to the nitty gritty of the sheet.

Feel free to take a gander...I'm open to any suggestions you may have at this point (except to trash the whole project hehehehe ). If you find a solution please explain the concept behind it. I really want to learn how to make this work rather than plugging in someone elses formulas.

View 10 Replies View Related

VBA Or Formula To Summarize Data Horizontally Into A Table?

Feb 11, 2013

Macro or formula to build a table from a data.

The data is in the following format in 4 columns: A (Customers' names), B(type of transactions: invoice,payment, Credit,Check), C (date), D(Amount).

Data is for the whole year by monthly.

I would like to build a table like this:

Column (A) 10 names of customers and in next 12 columns by month amounts and types of transactions.

View 1 Replies View Related

How To Summarize 52 Weeks Worth Of Data Into One Workbook

May 15, 2013

I am using Excel 2010 on windows 7 PC. I work for a railroad and one of my coworkers gets data once a week about how many carloads we ship to a certain company. They save each week in its own worksheet so at the end of the year they will have 52 worksheets worth of carload data. What they would like to do is take these 52 worksheets and then combine this data onto one worksheet so they can see all of it going from week 1 to week 52.

Here is what 1 week's worth of data looks like...

Here is what the 52 week summary page looks like...

And here is what the summary page looks like with all of the data on it....

Although we have compiled all of this data, it was done by copying and pasting data from 52 different worksheets (took some time as you could imagine). So, my coworker asked me if I knew of a faster way to do this. After spending multiple hours on the net researching this and trying a bunch of different options (Data Consolidation, Summary/Array Functions, and varying Macros) I have not been able to produce a worksheet that takes all of the data and displays it like the last picture above. Because I only know the very basics of excel I'm sure I'm probably doing something wrong. Trying to decipher the code for macros hasn't been easy and although I got data consolidation to work it was adding up all of the numbers instead of taking them from one sheet and placing them on another with all of the worksheets' data side by side.

View 6 Replies View Related

Compare Data-set With List & Summarize Matches

Jul 16, 2009

1) Background Info

We are trying to summarise some data that has been exported from an ancient database into a poorly delimited csv file. My colleague has imported the csv file into Excel ( attached), and we are trying to work out what to do next.

The dataset contains approx 300 records - each record being called a "sample" and having a unique sample number. NB: I have had to attach a cut-down version with only 3 samples, due to file-size - but it will hopefully give an idea.

Each sample contains 2 types of information that we are interested in (and a lot of irrelevant data besides). The relevant bits are:
- predicted occurrence of various species (given as a percentage for each species)
- observed occurrence of various species (given as positive/negative for each species, where positive is indicated by an asterisk)

2) What We're Trying To Do

We are trying to compare this dataset with a master-list of 80 species names. For each species on the master-list, we want to:
a) check whether it has a "predicted occurrence" value in each of the 300 samples (and if so, record the value in a summary sheet)
b) check whether it was observed in each of the 300 samples (and if so, record this in the summary sheet)

3) The Problem

Unfortunately, the dataset has imported into Excel as a gigantic list: 48000 rows (including loads of blanks) and only 3 columns across. To find the relevant data, it's necessary to:

a) open the attached workbook, and go to the worksheet named "Data"

b) Scroll through the rows, looking for the string "RIVPA" in column A. This tells you where each new sample begins. (The sampleID is stored in the same row as this, in column C. It is mixed up with a load of text, which we will need to separate out at some point, but that's a secondary consideration at the moment.)

c) Scroll down further until you find the text string "Predi" in column A. This indicates the beginning of the data we're interested in, for each sample (i.e. for sample 1, I'm talking about row 58). Count down a further 2 blank rows, and then you find the data itself:
- Column A contains the observed occurrence (a positive result is indicated by an asterisk)
- Column B contains the predicted occurrence
- Column C contains the species name

My colleague was trying to build a summary table, in the worksheet named "Summary". He was using the LOOKUP function to extract the data, but because there are so many samples, it's beyond unwieldy. He asked me if I could write a macro do do the job, and this is where I'm stuck.

How could I set up a macro that can identify where one sample starts and finishes, and where the data is within each sample? What is the most sensible shape for such a macro? If I could get the overarching logic worked out, I could make a start on writing the individual bits of code, but at present I just can't figure out how to begin.

To make things more difficult, the samples are not all the same length, and do not all contain the same list of species as each other. (However, at least there should be no species in the samples that are not in the master-list). The first sample begins on row 5, the second sample begins on row 173, the third on row 340, and so on.

View 8 Replies View Related

Summarize Data From Details Workings By Given Single Input

Sep 6, 2013

I have an excel file having the month wise model cost breakup e.g.Prod.Qty,CKD,Local etc.My problem is that I want to make a summary in a separate sheet showing the Input i.e. Model Code.If I give that input then automatic summary should be prepare.

Please refer attach file: Costing Table.xlsx‎

I this file,having 2 sheets.One is details and other is Summary sheet.

focus the cell no.C4 in the summary sheet.When,I give the model code then the summary should be ready from the details sheet data range, month wise.

View 3 Replies View Related

Macro To Summarize Data From Multiple Worksheets With Different Ranges?

Jun 25, 2014

I have a requirement where I need to summarize multiple work sheets. And each work sheet as different range. Column names are same in each sheet but number of rows in each are different. Like consider there are 3 sheets with employee details. Each sheet has Employee Name, Employee Number, Employee Location. But in first sheet as 10 employees and second sheet has 20 employees and third as 25. So the requirement is I need to summarize all employees.

View 1 Replies View Related

Pivot Table From Database To Summarize Data In Columns

Sep 9, 2008

I have attached a spreadsheet of an example of a database with a pivot table and a desired report
Can anyone give me a pivot tabe from my database that looks like my desired report.

I am willing to seperate the database into two seperate databases with different transaction types if necessary.

View 6 Replies View Related







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