Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

How To Add Amounts From Different Worksheets To One Single Worksh.

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Just want to know, am new at this multi worksheet



Similar Excel Video Tutorials

Helpful Excel Macros

Print Selected Worksheets in Excel
- This free excel macro will print all selected worksheets in Excel. If there are no other worksheets selected the active
Protect and Unprotect All Worksheets at Once
- This will allow you to protect or unprotct all worksheets in a workbook at once. It doesn't allow you to input a passwor
Print Preview Screen Display for The Current or Selected Worksheets in Excel
- This free Excel macro allows you to display the print preview screen or window for the current or selected worksheets in
Print Preview Display for Specific Worksheets in Excel
- This free Excel macro allows you to display the print preview mode or window in Excel for a specified Excel worksheet.
Vlookup Function That Searches The Entire Workbook - UDF
- This UDF is a Vlookup function that searches the entire workbook in Excel. The syntax and usage of the function is exac

Similar Topics







I use the following code to add team names to a column specified. However it will only add data to the active sheet when i am asking it to loop through the workbook missing out specified sheets. Would anyone be able to look over the code to see where the error is as to why it will not loop through the remaining sheets in the work book.

Thanks

Code:

Sub Addteam()
 

Dim worksh As Worksheet
Dim FR As Long
Dim LR As Long

For Each worksh In ThisWorkbook.Worksheets
If worksh.Name <> "mainmenu" And worksh.Name <> "overall performers" And worksh.Name <> "highlowperf" And worksh.Name <> "filenamedump" And worksh.Name <> "SingleDump" And worksh.Name <> "foldernamedump" And worksh.Name <> "AnalysisDump" And worksh.Name <> "individualteamselect" Then



    With worksh.Columns("A:A")
        FR = .Find(What:="AO NAME", After:=.Cells(1, 1), LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Row
        LR = .Find(What:="TOTAL", After:=.Cells(1, 1), LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Row
    
    Range("L" & FR & ":L" & LR).Value = Range("C3").Value

End With

End If
 Next worksh

End Sub





Hi,

I have a macro that someone very kindly helped me with on another thread (see below)

I need this developed a bit more to include a page break at line 70, and also to specific what sheet to apply the whole macro to, i.e. their are 60 sheets in the book, but i only want it to apply this formating to sheet 1 to sheet 50.

I am using Excel 2007

Any help would be appreciated as usual.

Lynsey



Sub test()
Dim WorkSh As Worksheet
For Each WorkSh In ActiveWorkbook.Worksheets
With WorkSh.PageSetup
.PrintArea = "$A$1:$u$190"
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.PaperSize = xlPaperA4
.FitToPagesWide = 1
.FitToPagesTall = 2
.Zoom = False
End With
Next WorkSh
End Sub


I am having some problems witha code I have adapted from some code that Rylo originally wrote for me . The original was based on a 2 column example. I have tried applying it to the proper sheet which has 8 columns but I am not getting the criteria to copy accross correctly.
Basically the code should create new sheets called team 1, team 2 etc and transfer the rows across from the sheet "PICKALERT" (SEE ATTACHMENT PA) based on the criteria in the code.
I am having 2 problems A) it is only copying the rows which have " ....." in column B across and not the cells with a code number in them. B) It is copying across rows that are outside the criteria.
If you look at the attachment PAZ, this is the result it is currently giving me. For example Team 3 should come out looking like the attachment TEAM 3 RESULT.
If anyone could advise what stupid mistakes I have made it would be appreciated. Code is below.

Code:

Sub aaa()
  Dim DataSH As Worksheet, WorkSH As Worksheet
  Set DataSH = Sheets("PICKALERT")
  For i = 1 To 8
    Sheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.Name = "TEAM " & i
    Range("A1:H1").Value = Array("Pick Loc", "Prod Code", "Product Description", "Req'd", "Ignore", "Bulk ID", "Bulk Loc", "Bulk Qty")
  Next i
  
  Sheets.Add befo =Sheets(1)
  ActiveSheet.Name = "WORKING"
  Set WorkSH = ActiveSheet
  Range("A1:H1").Value = Array("Pick Loc", "Prod Code", "Product Description", "Req'd", "Ignore", "Bulk ID", "Bulk Loc", "Bulk Qty")
 
  For i = 1 To 8
  
    Select Case i
Case 1
        WorkSH.Range("A2").Value = ">=AD41*"
        WorkSH.Range("B2").Value = "<=AH63*"
        Set critrng = WorkSH.Range("A1:B2")
 Case 2
        WorkSH.Range("A2").Value = ">=AJ01*"
        WorkSH.Range("B2").Value = "<=AK63*"
        Set critrng = WorkSH.Range("A1:B2")
Case 3
        WorkSH.Range("A2").Value = ">=AL01*"
        WorkSH.Range("B2").Value = "<=AL75*"
        WorkSH.Range("A3").Value = ">=AM01*"
        WorkSH.Range("B3").Value = "<=AM71*"
        Set critrng = WorkSH.Range("A1:B3")
Case 4
        WorkSH.Range("A2").Value = ">=AN01*"
        WorkSH.Range("B2").Value = "<=AP71*"
        Set critrng = WorkSH.Range("A1:B2")
Case 5
        WorkSH.Range("A2").Value = ">=AR01*"
        WorkSH.Range("B2").Value = "<=AR71*"
        WorkSH.Range("A3").Value = ">=AS01*"
        WorkSH.Range("B3").Value = "<=AS71*"
        Set critrng = WorkSH.Range("A1:B3")
Case 6
        WorkSH.Range("A2").Value = ">=AT23*"
        WorkSH.Range("B2").Value = "<=AT71*"
        WorkSH.Range("A3").Value = ">=AU23*"
        WorkSH.Range("B3").Value = "<=AU71*"
        Set critrng = WorkSH.Range("A1:B3")
Case 7
        WorkSH.Range("A2").Value = ">=AV38*"
        WorkSH.Range("B2").Value = "<=AX99*"
        WorkSH.Range("A3").Value = ">=AR72*"
        WorkSH.Range("B3").Value = "<=AR99*"
        WorkSH.Range("A4").Value = ">=AL76*"
        WorkSH.Range("B4").Value = "<=AL99*"
        Set critrng = WorkSH.Range("A1:B4")
Case 8
        WorkSH.Range("A2").Value = ">=AT01*"
        WorkSH.Range("B2").Value = "<=AT19*"
        WorkSH.Range("A3").Value = ">=AU01*"
        WorkSH.Range("B3").Value = "<=AU19*"
        WorkSH.Range("A4").Value = ">=AC01*"
        WorkSH.Range("B4").Value = "<=AD42*"
        WorkSH.Range("A5").Value = ">=AY01*"
        WorkSH.Range("B5").Value = "<=AZ22*"
        Set critrng = WorkSH.Range("A1:B5")

    End Select
    
    DataSH.Range("A:H").AdvancedFilter Action:=xlFilterCopy, criteriarange:=critrng, copytorange:=Sheets("TEAM " & i).Range("A1:H1")
  Next i
  Application.DisplayAlerts = False
  WorkSH.Delete
  Application.DisplayAlerts = True

  
  
End Sub





Hello all, I am afraid I have had to return to an old problem that I thought was solved. I had a problem with moving certain rows to new sheets. Rylo kindly gave me a solution that I am sure worked at the time but leaving me to add some more ranges. Unfortunately I had to leave it to do some more pressing tasks but I have returned to the task now and added the ranges but I have hit a problem as it is now coming up as variable not defined at the first "For i = 1 To 8".

I can't see what the problem is but I have never 100% grasped using variables. I have attached a copy of the start sheet in the workbork called return and the original post is in the link attached. The spreadsheet at the start of the original post is how the result should look after the code below is applied(If I have done the ranges correctly).
If anyone could take the time look to see what I have done wrong it would be appreciated

Code:

Sub aaa()
  Dim DataSH As Worksheet, WorkSH As Worksheet
  Set DataSH = Sheets("START")
  For i = 1 To 8
    Sheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.Name = "TEAM " & i
    Range("A1:B1").Value = Array("H1", "H2")
  Next i
  
  Sheets.Add befo =Sheets(1)
  ActiveSheet.Name = "WORKING"
  Set WorkSH = ActiveSheet
  Range("A1:B1").Value = Array("H1", "H1")
  
   
  DataSH.Activate
  Rows("1:1").EntireRow.Insert
  Range("A1:B1").Value = Array("H1", "H2")
  
  For i = 1 To 8
  
    Select Case i
      Case 1
        WorkSH.Range("A2").Value = ">=AD41*"
        WorkSH.Range("B2").Value = "<=AH63"
        Set critrng = WorkSH.Range("A1:B2")
 Case 2
        WorkSH.Range("A2").Value = ">=AJ01*"
        WorkSH.Range("B2").Value = "<=AK63"
        Set critrng = WorkSH.Range("A1:B2")
Case 3
        WorkSH.Range("A2").Value = ">=AL01*"
        WorkSH.Range("B2").Value = "<=AL75"
        WorkSH.Range("A3").Value = ">=AM01*"
        WorkSH.Range("B3").Value = "<=AM71"
        Set critrng = WorkSH.Range("A1:B3")
Case 4
        WorkSH.Range("A2").Value = ">=AN01*"
        WorkSH.Range("B2").Value = "<=AP71"
        Set critrng = WorkSH.Range("A1:B2")
Case 5
        WorkSH.Range("A2").Value = ">=AR01*"
        WorkSH.Range("B2").Value = "<=AR71"
        WorkSH.Range("A3").Value = ">=AS01*"
        WorkSH.Range("B3").Value = "<=AS71"
        Set critrng = WorkSH.Range("A1:B3")
Case 6
        WorkSH.Range("A2").Value = ">=AT23*"
        WorkSH.Range("B2").Value = "<=AT71"
        WorkSH.Range("A3").Value = ">=AU23*"
        WorkSH.Range("B3").Value = "<=AU71"
        Set critrng = WorkSH.Range("A1:B3")
Case 7
        WorkSH.Range("A2").Value = ">=AV38*"
        WorkSH.Range("B2").Value = "<=AX99Z"
        WorkSH.Range("A3").Value = ">=AR72*"
        WorkSH.Range("B3").Value = "<=AR99Z"
        WorkSH.Range("A4").Value = ">=AL76*"
        WorkSH.Range("B4").Value = "<=AL99Z"
        Set critrng = WorkSH.Range("A1:B4")
Case 8
        WorkSH.Range("A2").Value = ">=AT01*"
        WorkSH.Range("B2").Value = "<=AT19"
        WorkSH.Range("A3").Value = ">=AU01*"
        WorkSH.Range("B3").Value = "<=AU19"
        WorkSH.Range("A4").Value = ">=AC01*"
        WorkSH.Range("B4").Value = "<=AD42"
        WorkSH.Range("A5").Value = ">=AY01*"
        WorkSH.Range("B5").Value = "<=AZ22"
        Set critrng = WorkSH.Range("A1:B5")



    End Select
    
    DataSH.Range("A:B").AdvancedFilter Action:=xlFilterCopy, criteriarange:=critrng, copytorange:=Sheets("TEAM " & i).Range("A1:B1")
  Next i
  Application.DisplayAlerts = False
  WorkSH.Delete
  Application.DisplayAlerts = True
  DataSH.Rows("1:1").Delete
  
  For i = 1 To 8
    Sheets("TEAM " & i).Rows("1:1").EntireRow.Delete
  Next i
  
End Sub


HTML Code:

http://www.excelforum.com/excel-programming/755740-moving-areas-around-a-spreadsheet.html





Hi,

I am no Guru at this but need some understandable advice. I have more than 30 worksheets in a single Excel File. Each sheet represent the turnover report from respective stores for each day.

I want to create a summary Sheet which can automatically populate the figures for all stores for everyday into the Summary worksheets. To make it more understanding this is what it looks like -

In 01.12.10 sheet -> S9:V9 represents the amounts for store named X
In 01.12.10 sheet -> S9:V9 represents the amounts for store named Y
In 01.12.10 sheet -> S9:V9 represents the amounts for store named z

In 02.12.10 sheet -> S9:V9 represents the amounts for store named X
In 02.12.10 sheet -> S9:V9 represents the amounts for store named Y
In 02.12.10 sheet -> S9:V9 represents the amounts for store named Z

I want the summary sheet to populate those figures automatically from each sheet (i.e 01.12.10, 02.12.10, 03.12.10 etc)

Is this doable other then copying and pasting the values each day manually ?

Thanks,
R


Hi, I have a problem. I want to be able to supply multi cells with only filling in one single cell. like if i fill in an ID number it will fill in Name, Product amounts. For example

Worksheet 1

ID:
Name:
A............B............C
Product...Amount....Price
??..........???...........???


Worksheet 2

A......B.........C............D.............E
ID.....Name...Product...Amount....Price
112...Sony....TV..........200..........800.00

i would like to input the ID number in worksheet one and it auto put in the Name, Product, amount, and price from worksheet 2.

On the second worksheet all the info is on a single row.
i was hoping there was a way to link column with cells and fill in based on using one of them cells in a row.


Hello,

I am using Windows XP and Excel 2003

I tried the search for my issue but couldn't find anything to fit my purpose or anything I was able to adjust for my specific purpose properly.

I would like to automatically set the used range of all sheets in a workbook as the print area

As a starting point I used the code suggested he
http://www.mrexcel.com/archive/Printing/30490.html

This works fine for a single sheet

But I couldn't get it to work with multiple sheets in a way like this:

Sub test()

Dim lastCell As Range, LR As Long, WorkSh As Worksheet

For Each WorkSh In ActiveWorkbook.Worksheets

Set lastCell = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0)
LR = lastCell.Row
Do Until Application.Count(Range(Cells(LR, 2), Cells(LR, 256))) 0
Set lastCell = lastCell.Offset(-1, 0)
LR = lastCell.Row
Loop

With WorkSh.PageSetup
.PrintArea = Range(Cells(1, 1), lastCell).Address
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.PaperSize = xlPaperA3
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

Next

End Sub

I am not sure in which order to arrange the loop and for each to make this code work. There's probably also something wrong about the PrintArea command... :\

Any can help me fix this?

Also, is there a way to make the print area start at its actual used starting point, i.e. the range to print might as well start in Cell(2, 2), which means Row 1 and Column A should not be part of the actual PrintArea?

regards


Dear Experts,

Please can someone give me the coding for running macro using dynamic ranges in multi worksheets.

I am okay using Offset function for single entry but I have items with 100 rows and columns of 36 entries. I want to use the macro to copy from multi ranges and worksheets to a single sheet for loading data to access.

Your asisitance will be very much appreciated. Many Thanks
Cheers
Simon


I have a pricing model with about a dozen worksheets in Excel 2003, so that
there are more worksheet tabs than can be seen without scrolling. How can I
set up a "dashboard" which lists all the worksheets and allows the user to
click a button to go to that worksheet?



I need to recap all information from all worksheets on one worksheet. How is
the best way to accomplish this? Is it best to put the info first into the
all-inclusive worksheet and then use a formula on each individual worksheet
to pull certain information?



in a list box, how can i make the selection types multi and extend work?

i know how to use it for selection type single, in which case the value of a certain cell can be changed as per the single selection one has made in the lsit box.

but how do i make it work for multi selection. ideally this should mean that there are as many cells linked with it as the number of rows in the input range.

can you help, please?


in a list box, how can i make the selection types multi and extend work?

i know how to use it for selection type single, in which case the value of a certain cell can be changed as per the single selection one has made in the lsit box.

but how do i make it work for multi selection. ideally this should mean that there are as many cells linked with it as the number of rows in the input range.

can you help, please?


Is there a way to format margins and page orientation for all worksheets in a
workbook at the same time without the hassle of doing it worksheet by
worksheet?



This is kind of hard for me to explain.

I have a data table with different categories as columns and also multiple years amounts as columns. Each row is a data entry.

For example: each row would have different data to categorize the entry and at the end amounts for each year for 20 years.

I need to build a pivot table to present summaries based on different combinations of categories, as identified in the columns, as well as their amounts for the next 10 years.

I have no trouble in presenting results for 1 year, but it's nightmare when other years are put into it.

Right now my solution is to run parallel pivot table, each to accommodate one year and do another table to summarize the result.

Is there any way in pivot table that I can present multi-year results for the same category?

Thanks a lot!

Hi All,
Can someone tell me how to do the following:

I have several worksheets of data in a file.
All the worksheets have exactly the same Columns.
I want to create a single worksheet with all the data from all the existing
worksheets.

Up to now I have been copy/pasting the data into a single worksheet.
Is there a programmatic way of doing this for me?

Thanks,
John.





I have a worksheet with 2,248 different dollar amounts from a checking statement that include Credits and Debits. I have been able to import the data into a workheet containing three columns (date, amount, credit/debit). What I need to do is weed out any amounts that do not have a match so I can research them and see where the offset is. Is there any formula or VBA coding that can do something like this? Get the matched amounts and move them to another worksheet or move the unmatched amounts to another worksheet.So far I have been able to sort by descending dollar amounts. This helps a bit but have to go through the whole spreadsheet and copy/paste and amounts that do not have a match, which is pretty long. If I am not being clear enough, please let me know and I will try my best to make things clearer.


Hi there,

I have two worksheets of data that are connected by a common field. I want
to merge these two worksheets (similar to how one might link up two DB
tables).

Is there a way I can do this in Excel.

Therefore the end result would be a single worksheet, and whenever the value
in the column that I choose from each worksheet matches, a single row in a
new worksheet is created.
(and where there is no match a new row is added, but does not have all the
columns populated).

Hope this makes sense.

Kind regards

Dave





Ok so had a quick search around and could not quite find what I am after but if it is there point me over.

I have some data entry people, currently they create a new daily workbook for each day, using only a single worksheet in each workbook. They then at the end of each reporting period do a cut and patse of the five daily worksheets into a single weekly summary workbook and worksheet.

I would like their to be a situation where they either still use a daily workbook or use different sheets (slaves) in the same workbook. But all this information is appended/joined into a single worksheet either in the same or a different workbook each time it is opened or by activating a macro.

I know this must have been done before, anyone got some ideas?

Thanks inadvance,
Steve


Good afternoon ~

I have a financial spreadsheet that a user can click on a button to copy the worksheet and move it to the end with a naming convention that the user chooses.

I now need to summarize all of the GL amounts on a single 'Summary' worksheet for reporting purposes.

The problem that I am running into is that I need to have a SumIf like function across all of the worksheets regardless of the naming convention that the user inputs.

Can this be done using VBA?


I have a few hundred multi sheet workbooks that have address data in one of the sheets that I wish to collate into a single worksheet to use as a mail merge with word.

Any help would be gratefully received.

I'm using Excel 2003, The 1st worksheet is our project staff schedule the
other 8 are individual's timesheet that feed from the 1st worksheet. I don't
want others within this group to see someone else's timesheet. Is there any
way to grant permissions to an individual to open the schedule (1st
worksheet) and there timesheet without opening anothers worksheet?



I am in a workbook with an unknown number of worksheets.
I need to perform 2 updates to each worksheet.
1. I need to add the header row from the SHEET1 worksheet to all the other worksheets
2. I need to add a summation cell to column J of each at the end of the existing data and there are varying amounts of rows in each worksheet.

Thanks,
If an example would help I will send it along.


Hi,

I've got a workbook with about 38 tabs of data each with a different name in one workbook. All tabs are laid out the same way but with a different last row number.

I'm trying to figure out how to build a macro that would copy the two columns of data (A:B) and paste it into a corresponding worksheet in aonther workbook.

Attached is a mock up of what I'd like to be able to do.

In the file "Multi" the first tab would be moved to the file "Destination Multi" into the tab "Red". I know to put that first tab into "Red" because Cell A3 is "Tea". Tab 2 ("123456123456") which reads "coffee" in cell A3 of "Multi" would always go into tab "Blue" in "Destination Multi" and so on.

It would copy the dat from the first sheet and paste into the related worksheet At the end of the sheet (leaving what is in the sheet alone)

I've been able to do some basic code within the confines of a sheet or workbook. But The idea of looping through multiple worksheets into corresponding multiple worksheets in another workbook is way out of my league.

Thanks in advance for any help!

Just noticed I can't attach a file.

Here's is a copy paste of one of the three tabs:
The Tab name is the same as cell A5 (and the top left cell of below is A1

For Month Ending: 12/31/2010 Sales Summary Tea Product # 654321654321 Purchaser Quantity Jack 1 John 2 George 3 Mike 4 Robert 5 Steven 6 Nicholas 7 Jeffrey 8 Andrew 9 Shelly 10 Total 55


I have created a new worksheet within a workbook. Currently I have 5 other sheets within the same workbook, each with multiple products. However, some of these pages have the same listing and description from the same company (ie. hand towels, soap, etc.) and it is done that way because it is for that particular worksheet (ie.sports equipment like baseball, basketball, track, etc.) I need to create a way to post that single product appearing in multiple worksheets into a single worksheet, which would also give me the total number of orders for this particular product, unfortunately, I'm not sure how to build a formula to do so. Any thoughts on how can I do this?


How do I specify a single column of a multi-column named range within an
argument in an IF statement? This multi-column named range works perfectly
in a VLOOKUP but IFs need only a single column. I'd like to NOT have to
define yet another range nor use longhand cell reference.