I had in my macro toolbox a macro where in column A I listed names for worksheet the macro would then create and name the worksheets from the list in Column. But unfortunately our share drive where I kept the toolbox crashed and all the data was lost.
View 1 RepliesI need to create a Worksheet within a macro that has 10 tabs. I had something running, then I made some changes and I got Application-defined or object defined error.
Sheets("Sheet1").Name=Sheetname(1)
Sheets.Add after:=Sheets(Sheets.Count)
I was looping thru this up to 10 now its not working. This isn't too complicated,
I am trying to create a workload spread sheet for work I need to have the same looking spread sheet 365 times but also need each sheet to be dated, EG (tue,01/04/2014 through to tue,31/03/2015) I can create 365 tabs that have the same spread sheet on and I can create dates but not do both at the same time, it isn't fun doing copy and paste 365 time.
View 1 Replies View RelatedI work with contracts and have to report the sales for each contract by customer number, name, address, product code or sku, sales amount, quantity, and invoice date. Each contract number is associated with a product group and within that product group are a laundry list of product codes or skus.
Customers are often on different contracts for different products and may purchase some or all of the products on that contract. When I run a query from the system to extract this data, I get one sheet that contains 20,000 lines that have to then be sorted and seperated into different tabs based on the contract number. Keep in mind that a customer may be listed multiple times because each line in the query represents a product code and an invoice date.
I currently have a macro that will format the reporting such that it sorts the data, however, what it doesnt do is look for each instance of a particular contract number and separate it into different tabs.
So below are two examples: April and May 2008 Reports
April 2008 Report
Contract 1 contains 5,000 lines of data - extract into new tab
Contract 2 contains 5,000 lines of data - extract into new tab
Contract 3 contains 5,000 lines of data - extract into new tab
Contract 4 contains 5,000 lines of data - extract into new tab
Total lines = 20,000
May 2008 Report
Contract 1 contains 7,500 lines of data - extract into new tab
Contract 2 contains 5,500 lines of data - extract into new tab
Contract 3 contains 8,000 lines of data - extract into new tab
Contract 4 contains 9,000 lines of data - extract into new tab
Total lines = 30,000
So for the April Example, I would like excel to take this one list and break into 4 new tabs based on the contract number and carry over all of the data into that tab.
Where this may become difficult is that there may be more lines of data one month and fewer another. Im not sure if there is a function that will tell excel to stop when it reaches the last line for one contract number and to start a new tab for the next contract number. (see the month of May example)
I have a summary page that includes the titles for each tab within the excel 2003 workbook. I want to use the names of the tabs in the summary page and create it into a formula to lookup fixed cells within the various tabs. Sorry for not uploading an excel doc but I was at work earlier and the thread did not load for some reason, so I am reposting it.
View 3 Replies View RelatedIs it possible to create tabs within a tab? If so how?
View 1 Replies View RelatedI have a list of pairs of 406 pairs of currencies like the list below and I need to create a tab for each one of them and will take ages to do it manually, I am looking for a macro that can do it.
AUDCAD-AUDCHF
AUDCAD-AUDJPY
AUDCAD-AUDNZD
AUDCAD-AUDUSD
AUDCAD-CADCHF
AUDCAD-CADJPY
AUDCAD-CHFJPY
AUDCAD-EURAUD
AUDCAD-EURCAD
AUDCAD-EURCHF
AUDCAD-EURGBP
AUDCAD-EURJPY
AUDCAD-EURNZD
I have tried to use the one in other thread but I am not that good in editing the code:
Sub addsheet() Dim newsheet Set newsheet = Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet) newsheet.Name = "ww16" End Sub
I have a column with multiple currencies. Is there a way to write a formula or some code that will look to that column, and create a new Tab for each new currency name it finds, and then name the Tab that currency?
For example, C5:C15 has an assortment of entries either USD, GBP, AUD. I'd like a search that looks to C5 and creates a new tab labeled whatever the first currency is, then move down the column and do the same thing every time it encounters a new currency, but pass over a cell if it's a currency that already has a tab made for it.
Well I am here at this fork in the road again.
I need to create 3 new tabs... 10,4, and 1
I have tried this and it creates the worksheet, but does not copy the "sheet1" as its format.
I need to do this 3 times.
For i = 1 To 1
Set ws = Worksheets.Add
ws.Name = "10"
Worksheets("Sheet1").UsedRange.Copy wsnew.Range("a1")
Next i
I'm trying to reduce some manual labor that I do each month by figuring out a way to create additional tabs for every different "branch" within a given column from a master table. The branch numbers may not necessarily be the same each month as some old may go and some new ones may come.
I have attached two excel workbooks. One shows the master "Data Table" and the other shows what I want it to look like "After Sorting". As you can see, I have to create a separate tab for each of the branches listed in column A on the master table. This becomes really cumbersome, especially when we really have about 20 branches but I'm only using these 6 for the example.
Data Table.xlsxAfter Sorting.xlsx
I don't know VBA code but i am familiar enough to know that a code can be written to separate out information into different tabs on a worksheet.
For example, I have a standard worksheet with say 20 columns going across and 20,000 rows going down. At each "name" change in row 1, I would like a macro to copy into a new tab. Basically the end result will have a tab for each name and the corresponding columns that go with the name.
I have a large spreadsheet and want to sort by Column "L" and copy all the rows where Column L has the same value into a new tab named with the value in Column L.
View 2 Replies View Relatedi got a workbook made by one of the members in which on clicking of a button (make sheet)---->tabs of all the data in column A is made in that sheet. but if i want to copy a specific data that is present on main sheet to all the sheets created on click of button.
View 4 Replies View RelatedI need to create a formula using 2 different tabs within an excel workbook. I need to search column A on the first tab and column A on the second tab and if they match I need to compare column F on the first tab to column F on the second tab. (This column contains my staff names but the problem I am running into is on the first tab their names are listed first name last name and on the second tab they are listed last name, first name). If column A and F match I need to search column H on the first tab to column H on the second tab. If all columns match up to this point I need to take the data in column Q on the second tab and in put it into column K on the first tab. Is this even possible?
View 2 Replies View RelatedI have seen it accomplished where all worksheet tabs are arranged in a drop down list, and was wondering how to make that happen.
View 6 Replies View RelatedI would like to combine 2 tabs into one spreadsheet. I was not able to do so, because it exceeds the max rows allowed in excel. I have installed powerpivots but not sure how to combine data using powerpivots.
View 3 Replies View RelatedIs it even possible to create an IF/AND formula that draws data from multiple tabs?
For example,
(assume there are tabs named exactly the same as each of the data entries in column A)
IF A2 = "ARI" then D2 = ARI!H3. Is this possible?
I want to create a macro that creates a new .xlsx document based on cell values. And input 4 tabs into each document with specified tab names. I have a document that pulls from external sources and fluctuates with amount of data per day. I envision something that will make a new document named for the contents in cell A1, then function as a control+down to create a new document for A2, then A3 until there is no content left (should mention these are lookup formulas, and if there is no data it pulls a value of "0")
Is there a way to put in the code, the tab names it would create in each new document, or would that have to look to a cell value for the naming? The tab names would be "GS", "MYSS", "COLL SHEET", and "WIRE".
I am working on a macro that copies tabs from the spreadsheet the macro (button) is located in, creates a new book for them, and then pastes values so that no formulas still exist. The problem I am having is with named ranges. The named ranges do not reference the new sheet, but rather the old spreadsheet. This is fine because the new file is solely for presentation, however, one macro (which selects print ranges based on user selected checkboxes) tries to reference the old spreadsheet once it runs. I would like the macro that I currently have working, to also make this macro in the NEW spreadsheet so that it can still highlight the correct print area when used. The macro does not reference the name of the workbook at any point, it is purely "Range("ai10") type references so I don't understand why it is trying to open the old sheet back up.
View 2 Replies View RelatedI need to create a macro that can create a dynamic copy/paste loop. So far what I have is horribly inefficient. Each row in colmn A(minus the header) has a unique number in it. For each unique number, I need to paste it based on the number of column headers in row 1(minus column A). So, if there are 20 column headers, I need to copy cell A2 and paste it 19 times in another sheet. Then, I need to move to the next number in column A and do the same thing. Here's what I have:
[Code] .........
You can see that this is not dynamic. If I add another row to my table and rerun the macro, it will not catch it. I've attached a sample file to show you the big picture of what I'm trying to do. The data that I have is in Sheet1, and I'm trying to get it into the format in Sheet3. Rows/columns will be periodically added to the table in Sheet1, so the macro needs to be dynamic to catch that. The data in Sheet3 will always remain, and the macro will add the updated data below the old data in Sheet3.
FC_Macro_Sample.xlsm
Is it possible to make multple worksheets from a selection of multiple cells?
This would mean a selection of 10 cells would generate 10 sheets titled with the cell conent.
I'm desperatl seeking help as im trying to get the following macro to run on all available tabs in my workbook.
Sub Scroll()
Application.GoTo Reference:=Range("A1"), _
Scroll:=True
End Sub
I've tried this script but it does not seem to work
Sub Test()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Application.GoTo Reference:=Range("A1"), _
Scroll:=True
Next ws
End Sub
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!
I have a worksheet that I am always adding sheets to by duplicating an already existing tab. The tab ,"Current Invoice", is nested between sheets called "first" and "last". All duplicate sheets will also be nested between the "First" and "Last" tabs. What I would like to do is have a macro that affects all tabs between the "First" and "Last" only, even when the bookend tabs are hidden.
This is what I've started with.
HTML Sheets(Array("First", "Last")).Select
ActiveWindow.SmallScroll Down:=18
Range("U29:U190").Select
Selection.Copy
Range("AA29").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("W29:W190").Select
Application.CutCopyMode = False
Selection.Copy
Range("AB29").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
I would like this Macro to run on 6 different tabs in a worksheet as one Macro. It is currently only running on the active sheet. The tabs that I need it to run on are labeled "Totals" "New" "Used" "Service" "Parts" "Other Income-Ded"
Sub ExpenseAnalysis2012()
Dim rngSource As Range
Dim rngDestination As Range
Set rngSource = Range("D3:E90")
Set rngDestination = Cells(3, Columns.Count).End(xlToLeft).Offset(0, 2)
rngSource.Copy
rngDestination.PasteSpecial (xlPasteValues)
End Sub
create a macro that will delete tabs that ends with the word "OPEN" from a workbook?
View 9 Replies View RelatedI'm new to Macros and need your help. I've read online but can't find instructions on completing what I want.
I have a workbook with about 60 tabs that has various numbers of rows. I want a macro that will combine all the rows from each tab onto a new tab.
Is there an easy marco that i can do to acommplish this?
is there any way that i can adjust this code so that if there are not enough worksheets in my workbook to support my list that more worksheets will be made and renamed? Specifically I would like to copy the first worksheet and then rename it based on my list. also... If I have more worksheets than my list requires it can delete unused worksheets?
Sub NameSheets()
Dim i As Long
Dim ws As Worksheet
i = 1
For Each ws In ActiveWorkbook.Worksheets
If ws.Name "Sheet Names" Then ws.Name = Sheets("Sheet Names").Cells(i, 1)
i = i + 1
Next ws
End Sub
I have a list on "Sheet Names" that changes the tab names on each sheet when i run the macro
I am badly in need of a macro that will create 3 tabs using a value in sheet1 cell a1.The tabs created should be unique in the workbook and no sheet has to be duplicated or cancelled.After creating the tabs the macro should also create a list of the 3 tabs created in a sheet called list.
View 8 Replies View RelatedI have a spreadsheet with a table of data. Each row needs to have its data on a separate tab, so they can be presented in idividual reports. Each tab then needs to be given a name which corresponds to the values in the column A from the spreadsheet.
I can generate a new tab with the following:
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet1").Select
Sheets("Sheet1").Name = ""
But I have a few issues with this... Firstly, the number of new tabs required will change, so it needs to replicate the formula for the number of entries in the reference table. Secondly, this relies on the new sheet being "sheet1" which I'm not happy with in case someone accidently creates a sheet 1 by hitting the new tab button. I then don't know how to give each one a separate name based on column A from the reference sheet.