Create A New Worksheet And Move All Data Below And/or To The Right Of That Cell To The New Sheet
situation: 3 rows of data, row 1 has HDR in several cells (always in A1).
task
1. search row one for next example of cell containing HDR, excluding cell A1.
2. IF found, create a new worksheet and move all data below and/or to the right of that cell to the new sheet.
3. Repeat steps two and three until the search does not turn up another HDR except in A1.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Create Macro To Sort And Move Data
I have sheet 2 with a table with data in A2 thru X500, A1 thru X1 are my headers, A1 data is either Forecast or Sold, B1 data is Month, C1 is Value. I first need to Filter the data by Forecast, then by a specific month then by the value in decending order (keeping in line the data thru x which are headers I don't need to sort by). Next I need to take the 1st 10 lines by a specific month; A thru X and copy it onto a defined section on Sheet 1. I then need to do this for the 2 more times for the 2 proceeding months. So If we start with Jan, I need the macro to do Feb and march as well. The end result is that I have Sheet 1 with 3 sections on it each section is 10 lines and the resulting data is my top 10 forecasted deals for Jan, Feb, March. The macro must be able to do this for each month, so if June is chosen the 3 months are june, july and Aug.
View Replies!
View Related
How To Create Macro To Move Multiple Horizontal Data To Vertical
I need to create a macro to move variable multiple horizontal data to vertical format with certain infomation on horizontal will be duplicated following that variables. It's looks like below where you can see variables data in column F, G, H and I are moved vertically and at the same time column A, B, C, D and E will be duplicated following the variables allocation. I've tried to use transpose but it too manual and now looking suitable macro to help on this function Original DataAccountDim 3Dim 4AmountCurrencyV20228V20242V20211V202044006003300BXXX 9.4USD0.591.923.343.554006003400BXXX 88.17USD5.5118.0331.3233.314006003500BXXX 7.27USD0.451.492.582.75Process to automateAccountDim 2Dim 3Dim 4AmountCurrency400600V202283300BXXX 0.59USD400600V202283300BXXX 1.92USD400600V202283300BXXX 3.34USD400600V202423300BXXX 3.55USD400600V202423400BXXX 5.51USD400600V202423400BXXX 18.03USD400600V202113400BXXX 31.32USD400600V202113400BXXX 33.31USD400600V202113500BXXX 0.45USD400600V202043500BXXX 1.49USD400600V202043500BXXX 2.58USD400600V202043500BXXX 2.75USD
View Replies!
View Related
Copy Data From Cell In One Worksheet, Add Text, And Paste To Cell In Another Sheet
I have the following code in another workbook that is used to populate a cell on the same sheet based on input to cells in column 'A'. Is it possible to modify this for the attached workbook to select a cell with data (numbers) on the Input Data sheet in column 'E', add text to the beginning, ('CG' in this case), and paste the result to the Import Template in the corresponding cell of column 'A'? I currently have a formula copied to dozens of cells in 'A' but since the number of rows for the Input Template is variable, there are usually cells in 'A' that contain CG but no corresponding data in the rest of the row.
View Replies!
View Related
Create Sheet/Worksheet Navigation Menu
Im building a Menu list for my customers. I want to do it in XL sheet format. Can i know how to create tabs (like hand should apprear,when cursor come to the tab and should be like button, where we can name it) in the main sheet?. I have lot of innovative Ideas to come with beautiful menu file i need your assistance further to build my menu list.
View Replies!
View Related
Create New Sheet For Each Row Of Another Sheet Auto Populate Data.
i have a sheet called 'sample database'. it consists of 56 columns, each with a specific title in row 1. i.e. name, surname, mobile_number, and so on. from row 2 onwards the data has been populated for roughly 200 rows. i have another 'Capture Sheet' which has the same titles as 'Sample database' except it's in a different format. its a printable form that is given to new employees when they start. once they have completed it it gets captured into the 'Sample Database' sheet. my wifes boss now wants 1 new sheet for each row in the 'sample database' sheet based on the format/layout of the 'capture form'. how can i get the 'capture form' to auto populate the data from the 'sample database' sheet and create a new sheet for each row? i know this is possible i just dont know how to do it.
View Replies!
View Related
Macro Pull Data From Each Sheet And Create A Summary Sheet
I need to create a macro that will scan a spreadsheet for the number of sheets and then pull data from the same places on each sheet in order to create a summary sheet. Let me try to explain a little better. The spreadsheet I'm working with has a separate sheet for each new deal our company makes. Each of these sheets is in the same format - we use a template and fill in the data based on that whenever a new deal emerges. The sheets contain basic info about the deal in the first few rows and columns, then some narratives with dates describing the progress of the deal, and then a list of issues and whether or not they have been resolved. The problem is, each of these sheets contains too much info for a quick, high-level overview with the bosses so they've asked me to create something that will pull the basic info, the most recent narrative, and any unresolved issues from each sheet. This way, each time there's an overview scheduled with the bosses, the macro can be run and it will create a new sheet with data from each sheet in the workbook.
View Replies!
View Related
Automatically Move Data To Another Sheet
I have Excel 2003. I need a formula that will automatically move data in a row to another sheet, in the same workbook, based on either a two digit department code or possibly a date. I enter information on a master sheet, (8 columns of data: Text/$/digits) and I need that information to separate out to the other sheets when that department's two digit number is entered on this sheet. There are a total of 6 sheets in this workbook, counting the master. The data I am entering will be about 40-50 rows down of information, 8 columns across. Will this take a macro? Loop?
View Replies!
View Related
Move Data By Criteria To New Sheet
I've been working on a spreadsheet for the past 12 hours on a sunday when I should be home eating or something (damn projects). Basically, what I need to do via a button on 1 sheet, is select all rows of data that have a certain variable in a fixed column. E.g. "CLOSED" in Column AV for example. Then I need to CUT all of those rows out, and paste them into a worksheet with the exact same column layout, under the last used row. r = 6 For i = Selection.Rows.Count + 1 To 1 Step -1 If Worksheets("Live"). Range("AU" & i).Value = "Closed" Then Worksheets("Live").Range("AU" & i).EntireRow.Copy Sheets("Closed").Cells(r, 1) Worksheets("Live").Range("AU" & i).EntireRow.Delete r = r + 1 End If Next i I dont know whats goign wrong here. Its not doing anything! As far as i understand the code above, its doing the following: Sets the loop to increment in steps of 1 Searchs the range AU1 for "Closed" If the value is "Closed", copy the row, and paste it into the sheet called "Closed" in the cell: 6,1. If the value isnt closed, then proceed to AU2.
View Replies!
View Related
Move Data By Date To A New Sheet
find attached my workbook. I need an easy way to move each football teams last six matches to there relevant named sheet ie Bury in this example. The database sheet gets updated weekly so I would need some way for this info to automatically update. At the moment I sort each team one by one and copy and paste the last 6 matches, I am sure there is a better way?
View Replies!
View Related
Create A User Friendly Sheet Index For Worksheet Navigation
Want to go to a particular sheet in the same workbook by selecting the sheet name from a list in sheet 2 and clicking a command button. Sheet 2 has a list of all sheets in the workbook and sheet names are in two cells in column A & B (e.g. tdm-216). User to select two cells which are the desired sheet name (all sheet names are unique) and then click macro to jump to that sheet in the workbook. There will be over 100 sheets in the workbook.
View Replies!
View Related
Move Data & Duplicate Current Sheet
I have only recently started to learn VBA and have been putting it to practice in creating userforms to help people at my work to be able to timetable more quickly. I have realised however that in many cases the basic code that I have been using could often be improved. However in the sections below I have not been able to work this out for myself. This needs to be done as after a large number of worksheets exists excel gradually gets slower and slower when running the macros.
View Replies!
View Related
Automatically Re-name Sheet To Cell Value & Create Copy Of Hidden Sheet
I have a workbook with a hidden sheet ("Template") and a visible sheet("New Job"). I need code so when cell F1 in "New Job" is populated: 1-the sheet is renamed to the value of F1, 2-a new tab is made (a carbon copy of the hidden sheet "Template") 3-the new tab is named "New Tab" and marked as unhidden. Public Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.Name = Range("F1").Value End Sub Function WorksheetExists(SheetName As String, _ Optional WhichBook As Workbook) As Boolean Dim WB As Workbook Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook) On Error Resume Next WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0) End Function..................
View Replies!
View Related
Move Rows Into Worksheet Depending On Cell Value
One of the worksheets contains all of the data and the rest are empty. I need to do is move entire rows of from the main worksheet in to worksheets named the same as the value in column C of the row. for example one row may have TEST in column C so i want to move it to the worksheet called TEST. The next row might have TESTING in column C so that would go to the worksheet called TESTING. and so on.
View Replies!
View Related
Move Numerous Data To Any Favorite Place In Other Sheet Quickly ?
Let`s assume that I have numerous "Serial Number" in "Sheet1" : http://i40.tinypic.com/2iqbspt.jpg Now I want to create some cards in "Sheet2" that each card have a serial number that related serial number there is in "Sheet1" : http://i43.tinypic.com/34fz690.jpg. Need easy solution for doing it while when I drag one card to copying it then related serial number in sheet1 would be appear in required field. I mean first card has first serial number of sheet1 and second card has second serial number of sheet 1 ; but I want to doing it quickly with dragging.
View Replies!
View Related
Move Range To Last Cell On Another Sheet
Im trying to create a macro that when ran it copies all information from that row and puts it on a different worksheet. It needs to be put on the next available line in the new worksheet. and then the information deleted off the original and all rows moved up to fill in the gap. the rows all contain 2 formulas i'd like to keep specific to the row number Also it contains formulas id like to keep but change the cell numbers according to which row it is copied over to.
View Replies!
View Related
Move Rows To Another Worksheet Based On A Cell Value In That Row
I currently have a consolidated worksheet (thanks Bill!) called " Dashboard" that contains closed items that are marked by a validated column that can only contain "Closed, Open, or In-Progress." Is there a way to move the rows with a value of "Closed" to another worksheet called "Completed"? Also when this move is done, that row is no longer necessary in Dashboard and should be removed. So I'm guessing its a lot like a cut and paste and then a delete row/shift cells up? here are some additional information: The worksheet has a locked header that is 6 rows deep (the values for the "Status" column begins on row 7 and on.) The "status" column is at column 11.
View Replies!
View Related
Move Rows To Another Worksheet Based On A Cell Value In That Row ...
I have a excel file to keep track of tasks or actions that need to be performed. What I am looking for, is an automated utility or code that will allow excel to automatically move entire rows (so an entire task) of completed tasks to another sheet called, "Completed Actions". In Actions sheet I have a column for " status" and here you have to select from a drop down menu, either "On-going", "Urgent" or "Done". What I would like, is that once you have selected "Done", the entire row or entry, will be automatically moved to the "Completed Actions" sheet.
View Replies!
View Related
VBA Macro To Move Data To Summary Sheet Based On ID's
I have had several intro programming classes, but not with visual basic. I've noted the problem lines with comments below. The problems are probably very easy syntax error. Sub list() Dim counter As Integer Dim counter2 As Integer Dim id As Double Dim id2 As Double Dim palletnum As Integer ActiveSheet. Name = "Pallet Detail" Sheets.Add ActiveSheet.Name = "Pallet Summary" Sheets("Pallet Detail").Select
View Replies!
View Related
Cut & Paste (Move) Filtered Data To 1st Empty Row In Another Worksheet
I am trying to find a macro that will filter on Column F (non-blanks - just cells with dates) cut just the filtered data (A:G inclusive)without the Header Rows and then paste/move this data to the first empty row on another sheet called "Complete" So that I get the cells in columns A:G with dates in moved to the "Complete" tab and they are no longer in the sheet "Deliverables"
View Replies!
View Related
Create New Workbook From Existing Worksheet Data
I am working on this estimating template. To be used on Excel 97, 2000 and XP systems. I have a few issues i am unable to resolve. There are 2 of the worksheets (Short Quote and Stores Req) which individually need to have a button on them so i can create a new workbook as an exact coy of that sheet. but with just the values and not the formulas. Also i am unsure on the macro on the workbook currently( as i dont know how to do macros) i dont know what it does?
View Replies!
View Related
Macro To Create Pivot Tables And Sort Out Data In Other Worksheet
The first sheet is the row data given to me, the second one is where I want my data to be analysed automatically by macros. I managed to create a pivot table manually to give me the info I want but then I still have to copy all the results from the pivot table sheet to my analysis sheet. How can I create a macro able to do all that by it self? I tried to record a macro using the recorder but then when I try to play it it gives me an error straight at the beginning in the definition of the pivot table I think. An example of what I want the pivot table to do is: in the first sheet, go in the small table in column K and L, take the value of L1, then in the row data in column A to J, in column C look for the value in L1, once found, look for the value 1 in column J then do the average on the values in column E and put the result in my second sheet in cell F5 then do the same but look for 2 in column J and put it in F8, then 3 in F11, ... until 7 found (the data is in ss.000 and so the results in the second sheet should be formated the same way) then do the same all over again for value in L2 etc etc etc this should be done for the 6 values in column L, each having values up to 7 in the J column. Then I need to do the same for other columns and not only averages but minimums as well but I can adapt the code I think.
View Replies!
View Related
Create New Sheet For Each Group & Copy Needed Data To Each
I have just inherited an Excel spreadhseet that displays job titles and what types of documents each one should be trained on. What I am trying to achieve is a formula, or something to that sort, that will look at each job individually and tell me only those documents that they need to be trained on and list it on a seperate sheet. If you look at the attached document you will see that the first 2 columns contain a document number and description. Across the top you see each of our job descriptions. In the middle you see an X that indicates that person should be trained on that document. So once more, I would like to take each Job Description (seperately) and list only the documents that have an "x" in their column on another sheet. I already have the sheets created, I just need to know if there is some kind of formula or macro that can do this for me.
View Replies!
View Related
Looking Up The Same Cell On Each Sheet To Create A Summary
I have 52 sheets in my workbook. Is there a simple way of creating a summary page of the data contained within specific cells which are the same reference on each sheet ? The slow way is to edit the Sheet reference in the formula, but I sense this is not the most elegant way to do it. Sheet 1 Cell A1 = 453 Sheet 2 Cell A1 = 234 Sheet 3 Cell A1 = 543 Sheet 4 Cell A1 = 945 I start off with this formula ='sheet 1'!A$1 Copy it down my summary page, then edit the sheet reference for each page. ='sheet 1'!A$1 ='sheet 2'!A$1 ='sheet 3'!A$1 ='sheet 4'!A$1 This is simple enough to do, but is laborious for lots of references.
View Replies!
View Related
Create Macro To Chart Data With Location As Object In Active Sheet
I have been trying to create a macro in excel to chart a selection of data and to output the chart on the active sheet where the data was taken (as opposed to a named sheet). So basically, I have about 300 worksheets with data, and I would like to have a button on each page that automatically charts that data when clicked, and outputs the chart to the page where the macro was clicked. However, I have not been able to figure out a relative reference that will allow me to make the LocationasObject reference simply the ActiveSheet as opposed to a specifically named sheet. See my code below, which references an output to a worksheet called "Charts". Right now, all of my charts are outputting to the sheet called "Charts", as opposed to the active sheet. Sub ConsDiscChart() ActiveCell.Offset(29, 11).Range("A1").Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlDown).Select ActiveCell.Offset(0, 1).Range("A1:B1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Offset(0, -1).Range("A1:C24").Select Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.Location Where:=xlLocationAsObject, Name:="Charts" With ActiveChart .HasTitle = False .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With End Sub
View Replies!
View Related
Macro To Move Data With Trigger Cell
I have a single workbook with five spreadsheets. My goal is to move data along a path (or work flow) from one sheet to the next by using a "trigger" pulldown menu choice. Sheet1 = Prospects Sheet2 = New Sale Sheet3 = Upgrade Sale Sheet4 = Won Sheet5 = Lost Sheets 1, 2, and 3 use the same data layout for column A - K. Sheets 4 and 5 have the same A - K as above and also have columns L - R in common. The last column in sheet 1, 2 and 3 is a pulldown menu (New, Upgrade, Won, Lost). Data rows on each sheet start at row 7. The goal is to use the pulldown choice to remove the data from the current sheet (ex: Prospect) and add it to the next open row in the required sheet (ex: New or Upgrade). I also need to be able to make a similar move from New/Upgrade to Won or Lost.
View Replies!
View Related
Move Data Based On Cell Values
I would like to know if the following problem can be solved using only Excel without any operator intervention. Can a formula be created? If yes I would appreciate it very much Problem: If B5 = X Move B6 thru K6 to B5 thru K5 And Move B7 thru K7 to B6 thru K6 And than Blank out B7 thru K7
View Replies!
View Related
Move Cell Data To New Line After X Characters
I am using Excel to create a parts list for a manufacturing assembly. I intend to have 6 categories (columns) for each part or instance. My problem is that the last (furthest right) entry will sometimes contain more characters than others. I want to limit the number of characters in the cells of this last column, and I have seen how to bump the remaining characters down to the next row(s) in the same column using the LEFT and MID formulas. This actually works very well for that purpose. However, I need to know how to force the next instance (row of 6 cell) to start together on the next available row. The following images should better illustrate my question. First, I show the source data with the contents of cell F2 without further formatting. This cell contains more characters than I want making the column too wide for the printable space allowed. http://www.ozgrid.com/forum/attachme...1&d=1198374241 The next image shows the desired result with the extra characters from cell F2 moved to F3. (This data could actually fill up to 4 or 5 cells vertically.) Here, I have manually entered cells A4 through F5 for this illustration. I need to know how to automatically have A, B, C, D, & E move along with F. http://www.ozgrid.com/forum/attachme...1&d=1198374241
View Replies!
View Related
Pulling Data From List If Cell Blank Then Move To Next
Trying to pull data from a list and if the cell above is empty then pull the data from the cell below and so on........ I have some code for it..but its just a little bit too long and I can't use it This is what i have so far, trying to simplify it as its way to long to fit in a singel cell....i did think about breaking it down in to collums of either 8 or 6 and having it in chunks that way...but if i did that then i would still have to eventually use this forumla below as i don't really want huge gaps within the form i'm doing.
View Replies!
View Related
Move Lots Of Data From A Cell To Multiple Lines
I have a problem that i don't know how to solve. I have one cell with values like this: 127;71;512;458;1250;722;2154;742;29;350; and what to put in lines like this: 127 71 512 458 Has i have a lot of data in the cell and everyday it changes, i take several hours doing it manually. I have tried using the command data/ text in colluns but because i have huge data it leaves a lot out. Do you have any idea how to put that values in lines automatically?
View Replies!
View Related
Open File And Move Cell Data To New Workbook
I created a new workbook that contains a list of additional design requirements than our old checklist. These requirements are answered by placing a 1, 0 or .5 in the cell next to the requirement (1 = yes, 0 = no and .5 for half credit). I can't simply go to the old workbook/worksheet and copy the results into the new workbook/worksheet as the rows of requirements don't match up since some requirmements have been deleted and new ones added between the old and new checklists. I created a command button in the new checklist and what I want it to do is: - open a dialog box that lets me select the old checklist file (these are all excel files, but with different names). - go the the proper worksheet in the old file (DFT Checklist) and copy a column of results from the old checklist ("DFT Checklist" worksheet) and paste them into the proper cells of the new checklist ("DFT Checklist" worksheet). - The macro would know (i'll have to tell it) which requirements and cells are still valid answers between the old checklist and the new one. It would disregard requirements that are no longer in the new checklist. - Once complete, it would close the old checklist without saving.
View Replies!
View Related
Macro To Move A Range Of Data Over 1 Cell, If A Certain Condition Exists
I am trying to create a macro that will look in Column B for the word "RPLCASE." Whenever "RPLCASE" appears in Column B, the cells in that particular row will move over (Right) one space. I am simply trying to align the data from a file that I FTP'd from Net Term to Excel. You can see how the data should look in the 2nd example. 14-Feb-08JERRPLCASE671150536:116:56RPLCASE78308147:077:54RPLCASE89431228:228:57RPLCASE910592289:159:59RPLCASE10114732310:0010:48RPLCASE1112146811:4511:58RPLCASE12136563612:0612:56RPLCASE13144122113:0313:58RPLCASE14154642314:0114:23**********--------------TOTALS4632228LDLPKGPIK671361126:046:58PKGPIK781461247:007:56PKGPIK8963598:168:55PKGPIK9101591309:019:59PKGPIK101115113910:0010:54PKGPIK1112968711:3211:59PKGPIK121319916012:0012:59PKGPIK131412310313:0013:59PKGPIK141518716414:0014:59PKGPIK15161119715:0015:54PKGPIK16173316:3016:31**********--------------TOTALS13741178
View Replies!
View Related
Sort And Move Code, Create Equipment Lists`
I have been using Excel to create equipment lists for my work. The forum has helped me by creating a visual basic macro to create a 'final list' sheet compiled from the other source worksheets. On my 'final list' sheet I need the first 10 rows to be left out of the macro’s so that I can add header information like job reference etc. I have tried altering the visual basic code by adjusting the row numbers in it so that it starts at row 10 but this keep's on producing error codes. This is due to my limited knowledege of what each line does. I also wish to be able to paste new equipment into the final list sheet. When I do this at the moment the code does not recognise it is there and does not carry the information accross back into the source sheet. It is the same when I block select an area and delete the code does not recognise I have done this which is fustrating.
View Replies!
View Related
Copy Sheet & Create New Monthly Sheet From Present Sheet
I want to create a macro button that can create copy, insert, paste and rename the new sheet in next month's name, like if the active sheet's name is January, I want to copy the whole sheet of January, insert new sheet, paste the new sheet and rename the new sheet to next month like February? Also rename the new sheet (February) cell B3 the same as new sheet's name (February) So if month of February is near end, the macro button in February will create the same way as Jan did which means the next sheet will be named March and so on.
View Replies!
View Related
If Cell="x" Then Move Row To Another Worksheet
I have a spreadsheet that contains rows that my sales team complete for their pipeline. I have a drop down at the start of the row (column a) that has the status of the sale. If they put the status to "Lost" I'd like the entire row to be cut and moved to another worksheet (lost) so that I can do additional analysis at a later date of all lost sales.
View Replies!
View Related
Insert Row On Sheet & Move Active Cell Row To It
I would like to create a macro that could archive entries from one sheet and insert them in another. I created one but the problem is that the entry has to be the same row each time. Example: Sheet 1 – is current jobs and sheet 2 is old jobs. My macro moves an entry from Row A-5 of Sheet 1 and moves it to the top of Sheet 2. I would like to be able to scroll through each entry select it and have it moved to the top of the Old Jobs sheet.
View Replies!
View Related
Data Validation Using List In Another Worksheet - Works For All But One Sheet!
I am running into an odd issue and hope someone might be able to shed some light. I have created several lists on one sheet in a workbook and have assigned named ranges to each individual list. So far, so good. On various other worksheets, I have created Data Validation rules to allow users to select the relevant data from drop-down lists. (using formulas such as =List1, =List2, etc. in the Source box to capture the named ranges I had created). Everything works perfectly...EXCEPT for one worksheet, which continues to give me the error message, "You may not use references to other worksheets or workbooks for Data Validation criteria." Can anyone explain why this error would only occur on one worksheet and work fine for all the other sheets? I'm perplexed!
View Replies!
View Related
|
TRACKER
