VBA To Create New Workbook For Each Row On Source Sheet
I have one excel file with several columns and rows of data.
Then I have a template excel with titles and some tables (no formulas, no macros, etc).
I need to generate a new workbook (based on the template) for each row of data from the source excel.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Save Sheet With Same Path As Source Workbook
I've go a shared Workbook, which will be distributed among several users and stored in different places. The workbook uses the following Sub savemeas() Worksheets("data").Visible = True Sheets("data").Copy Application.DisplayAlerts = False ActiveWorkbook. SaveAs Filename:="d:" & Range("a1").Value, _ FileFormat:=xlText, CreateBackup:=False Application.DisplayAlerts = True ActiveWorkbook.Close SaveChanges:=True End Sub to save the sheet "data" as a text file with a name based on the value of cell a1. All I need is to modify the code so that the target path would not be ActiveWorkbook.SaveAs Filename:="d:" & Range("a1").Value but be the same as the source Workbook's - so that I wouldn't have to modify the code for each user separatly, because the sheet would alwayas be saved in the same folder as the current path of the source workbook.
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
Create A New Workbook By Vba Of Excel
I want to the following steps to be done by vba of excel. 1. copy ranges of cells in the active worksheet (sheet3) 2. add a new workbook and paste the copied ranges to sheet1 3. save the new workbook with the new file name (e.g. date) 4. close the new workbook and clear the contents the copied ranges of cells in old workbook. following code and make it more simpler? Worksheets("Sheet3").Activate If Range("l1").Value <> "" Then Range("l1").Value = CDate(Date) End If asdate = Range("l1").Value 'date entered by user resp4 = MsgBox(" Case particulars of " & asdate & " will be backed up and deleted from this sheet! Proceed? (Y/N)", vbYesNo, "Deletion of Data") If resp4 = 7 Then Exit Sub
View Replies!
View Related
VBA To Create New Sheet ...
I would like to be able to have a macro, linked to a button, so that when you press that button it creates a new named sheet based on data in a particular cell. I.e. There is a table with the following data: Apples (A1) Oranges (A2) Pears (A3) There are also the sheets Apples, Oranges & Pears. You enter 'Grape' in cell A4, then when you press the button it creates a new sheet named Grape. Also when a new sheet is created, the 'input cell', in this case A4, is moved down one so that when the next sheet is needed to be added, it doesn't just add the same sheet (i.e. in the example above, we don't end up with a new sheet named 'Grape' each time we click the button).
View Replies!
View Related
Can VBA Create A Named Sheet In One Step?
My vb code currently creates a new sheet via "Sheets.add". This new sheet has the default name of "sheet1" which I rename as "tempSheet". I then populate tempSheet with data, save as a CVS file and then I remove tempSheet. If I stay in my workbook and try to execute the code again, the default name of the sheet it now creates is "sheet2" and not "sheet1" - this is a problem becuase my code is trying to rename "sheet1" "tempSheet" and of course it cant.
View Replies!
View Related
Copy Values From A Worksheet To Another Workbook. Source Workbook Name Unknown
I need to copy a range of values (actually two ranges). The ranges are of fixed size. Le't say A2:D20 and E2:H20. Both workbooks have the exact same layout, and they both have the same named worksheet (in this case it's called Entry). However the actual filename of the source workbook is unknown. I know numerous users have changed the filename of the xls file. I want to instruct them to open the old and new workbook, and open a third workbook containing the copy macro, they run the macro and it copies the data. How can I reference a known worksheet name but of an unknown workbook name esp when the source and destination share the worksheet name?
View Replies!
View Related
Getting The Scroll Row For A Sheet Just Before Workbook Is Deactivated
to retrieve the scrollrow and scrollcolumn values for a worksheet just before its workbook is deactivated. I am currently using the following Private Sub Workbook_Deactivate() msg = MsgBox(ActiveWindow.ScrollColumn & " " & ActiveWindow.ScrollRow) End Sub Unfortunately, this isn't working the way that I intended. If, for example, I deactivate the workbook containing this code by switching to another open workbook, the msgbox displays the scroll row and column from the active sheet in the new workbook that is opened. How can I "catch" the scroll settings for the workbook containing the above code before it is deactivated?
View Replies!
View Related
VBA Macro To Copy Entire Row To Another Sheet
I have written Macro to copy every row in "sheet1" 24 times into new sheet called "NewSheet". but it keep giving me error message. Actually, I don't know why. Can any one please help. here is my macro Sub CopyRowsBook2() Worksheets.Add().Name = "NewSheet" Sheets("Sheet1").Select ' Find the last row of data FinalRow = Cells(Rows.Count, 1).End(xlUp).Row ' Loop through each row For x = 2 To FinalRow Worksheets("sheet1").Cells(x, 1).Select ActiveCell.EntireRow.Select Selection.Copy ' Loop to copy every row 24 times For i = 1 To 24 Sheets("NewSheet").Select NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1 Cells(NextRow, 1).Select ActiveSheet.Paste Next i Sheets("Sheet1").Select Next x End Sub
View Replies!
View Related
Copy Worksheet To Another Workbook With Links To Source
I am try to copy a worksheet "exactly" from one workbook to another, i.e. all formulas and range names reference the new workbook and not link back to to the workbook they were copied from. The code below brings the formulas and named ranges over still referencing the source workbook: SourceWB.Sheets(sht).Copy after:=TargetWB.Sheets(sht)
View Replies!
View Related
Row Source For ComboBox
Below is my code. I need the row source for my combobox to be dynamic. I created a named formula and referenced the formula in my code. It doesn't work. This is the formula "listname" =OFFSET(MyList!$C$2,0,0,COUNTA(MyList!$C:$C)-1,1) Private Sub ComboBox1_Change() ComboBox1.RowSource = Range("ListName") End Sub
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
Another Sheet As A Data Source
I want to design a spreadsheet report, but instead of importing the data from an external source, i would like the data to come from another sheet within the same workbook. The goal is that the data sheet will contain only raw data with no formatting, and the report sheet will contain all the formatting.
View Replies!
View Related
Each Chart Source Row As A New Series
my problem is: I have a whole list of data which changes according to the users preferences It comprises of a NAME, X Value, and Y Value and the addition of X and Y. (these all have auto filters on them) The user inputs under these fields, as many rows as they wish (which will be around 20ish) I want it so when i click a button "CREATE GRAPH" it selects the highest 10 values (if there is 10 values, if not then all the values) from the 4th column (X+Y) and then creates a graph. i want each row to be a new series...
View Replies!
View Related
Changing A Chart Source Sheet
I have a sheet ( Graphs A) with 50+ graphs on all linked to a datasheet (Data A). I now want to copy these graphs to another sheet (Graphs B) which links to a different data sheet (Data B). The data is in the same layout format etc. The problem is that the find-replace function will not replace the sheet name in the charts series formula from Data A to Data B Is there an easy way to change the source sheet for the new graphs?
View Replies!
View Related
Links Updating When New Row Is Entered Into Source
I have a master price list that will have items added or removed from it occasionally. I have sixty workbooks that contain detailed data on mixes of multiple products from the master price list. All the corresponding prices throughout the sixty workbooks are linked to their specific slot on the master price list. The problem: if the sixty workbooks are closed and anyone adds a new row to the master price list, when I open any of the workbooks the values update but the referenced cell does not. So if the price for Product 25 is located in cell K118, and two products are added in above K118, when I open any of my mix sheets that contain Product 25 the linked cell should be to K120. But it's not doing it that way. It is holding on to the referenced K118 cell and updating the value to the wrong price. I've played around with the VLOOKUP function but haven't been able to make it work. Linking the cell as I have has worked great, if I could just get it to adjust the reference as new rows are added to the source workbook. I've figured out that it will update the workbooks when I add a row if all that are required are open when the addition is made. But my boss will not open sixty workbooks every time a change needs to be made to the master price list, which he controls.
View Replies!
View Related
Show Row Source Data In Listboxes
I have used a userform found on this forum (I cannot find the link) that displays row data from a range within a listbox. Unfortunately though you cannot determine the column width. As such I wondered if it was possible to show the data within other listboxes. So for example in listbox1 you can see 5 columns accross (from a range) however one of them is very wide. Looking at Listbox1 you can only see the firsy 20 characters approximately in each column. Is it possible therefore that once this row of data is selected that the contents of the columns is duplicated in other listboxes on the same userform that can be expanded to accommodate larger text?
View Replies!
View Related
Changing Chart Source With Vba
I'm trying to create a chart with different source ranges e.g if i used With ch.Chart .SeriesCollection.Add _ Source:=Worksheets(1).Source:=Worksheets(1).Range("C1:C8,J1:J8") End With That works fine but I want to modify the columns it looks at. I was going to use the cells(x,y) method but I can't get it to work. icol = 3. Range(Cells(1, iCol), Cells(8, iCol)). This works for a single range(C1:C8), but how do i reference Range("C1:C8,J1:J8") in the same way.
View Replies!
View Related
Creating A Source Sheet To Pull Information From.
I'm trying to do is create a macro that will autopopulate information from a source sheet. What I have is a different route sheet for 25 or so technicians within my company on one worksheet. I want to be able to have a source sheet that I can change daily and have it autopopulate the information needed into the appropriate cells/sheets from this one source sheet. I am pretty sure there is a way to do this, but I'm not familiar enough with creating macros to do this myself, although if I had an example of one cell and a simple explanation of how to fit this to the information needed I think I could do this on my own.
View Replies!
View Related
Macro To Create New Workbook And Sheets Inside Each Workbook
I have a spreadsheet which has all the names of trips from a warehouse, the day that they operate (1,2,3 etc) and the job line allocated to each trip. It looks something like that: A B C DAYTRIPCUST I would like to create a macro that will be creating 7 new workbooks and then in those workbooks as many sheets as the trips. In these sheets, the customers should be displayed. How do I write it? I could not find how to have a "dynamic choice" in the macro. I.e. not to have the criteria as "1", "trips1" but to choose from the range of inputs that are available.
View Replies!
View Related
Manipulating Chart Source Data With VBA
I have a chart that I am trying to set up with dynamic data. I have the chart created, but the problem is that I will have a variable number of categories based on the data that is loaded, so I need to use VBA to change the range for the source data and labels on the fly (otherwise, I will end up with 20-50 categories that are all zeroes, putting a bunch of gobbledegook at the top of the chart). What commands to I need to enter into VBA to get this to work? I have put a formula in 2 cells to determine based on the current data what the source data values range should be, and what the category labels range should be. The cell that holds the value range is Graphics!L6, and the cell that holds the labels range is Graphics!L7. The current values that these cells hold are: L6: =Graphics!$J6:$J9 L7: =Graphics!$K6:$K9 Oh, and the name of the chart is "Chart 1" What do I need to put into VBA to get the chart to change the data source values and category labels to the ranges I have listed in those 2 cells? (and for that matter, have I listed the ranges correctly in those 2 cells so that they are usable?) If possible, I would prefer to do this without having to use code to select the chart, because I want the updates to the chart to be invisible to the user.
View Replies!
View Related
Using VBA To Open And Close Source File
I am working with a bunch of files to do a dashboard. I have the main file which shows end results and what my users will see "Dashboard". I have about 10-15 files which I export weekly from MS Access. and I have the "Dashboard Data" file. In this file I have a sheet which links all the information for the files exported from MS Access. In here I have named ranges and dynamic ranges plus additional columns created to look up information in other tables. I use this file as my source data for my Dashboard File so that I can reduce the size of the actually dashboard. Dashboard Data file is about 5.5 mb and my Dashboard is 300kb. I have 12 branches that need to view the data over a radius of a hundred miles so the smal file are better. My problem: I can get the Dashboard Data file to update without opening all the 10-15 other files. I cant however make the Dashboard file update without opening the Dashboard Data file. I have it to auto update without prompting but its still tells me that it cannot update. I was wondering if there is some VBA code I could use that would work like this: I open the Dashboard File, Dashboard Data file opens so that Dashboard file updates and then close Dashboard Data File so that the user never has to see the Dashboard Data file. I dont want them to see it or have access to it.
View Replies!
View Related
How To Save HTML Source Code From VBA
I would need to get HTML source code from any given page. I know how to open a HTML page from Excel and I can do it with VBA, but how to get for example this page's source code? I would start with making a sub that takes a string (the address) as an input parameter and finish with saving the source code of that address as an text file like c:code.txt So something like Dim webaddress as string Sub GetSourceCode(webaddress) 'then some code to save the source code End Sub
View Replies!
View Related
Getting Web Page Source (HTML Or XML) In VBA
I would like to retrieve contents of a web page, be it HTML or XML, into VBA variable! Later, I would chop, cut, parse or extract the data I need. Both importing as XML or WebQueries is unsatisfactory for a certain number of pages I need. XML has bad schema, WebQuery tells me it can't find any data. I tried with WinHTTPRequest, but Excel gives me back error "undefined user type" in other words it doesn't recognize that object. Basically I want the source of web page to become a string in my VBA code. In other words that would be replication of funcionallity of
View Replies!
View Related
Copy From Closed Workbook Then Create New Workbook
I've got a problem with this code, have been wracking my brains about. Here is the process I am trying to do: 1) Copy a range (a2:av1000) but (ideally) find the last populated row from a closed workbook (with a different password) 2) Create a new workbook and paste this data into it at A2 3) Close all the workbooks but only save the new one.
View Replies!
View Related
Concat Two Cells In The Source Worksheet And Vlookup Into The Defs Sheet
I've used vlookup before but his #REF problem has me stumped. I want to concat two cells in the Source worksheet and vlookup into the Defs sheet, but I keep getting #REF(G4:G6) when I want to return the 2nd column in the Defs sheet. What's curious is when I modify the vlookup to return the 1st column I correctly get the lookup cells(G6:G9). What's wrong with the formula in G4. My requirement is I can't change the format of the Source worksheet because it contains macros that work with ERwin. And it is MUCH bigger than the attached sample so I will copy/paste the formula down 6000+ cells.
View Replies!
View Related
Copy Data On New Lines From A Source Sheet Based On Criteria
I have a source sheet which contains data. Within this data there is a column (column 3) called Name and I would like to take the data from this sheet and paste it onto a new sheet but in grouped format. So take all instances where it finds criteria 1, then 2 and so on and just paste it on a new line each time it finds a criteria. So far I have the following code to search Sub loopy() Dim rngTemp As Range ' the range variable declaration Dim intCounter As Integer 'counter as integer ' the following loop goes through all the cells in column a up until the last used cell in the column For Each rngTemp In Range("a1", Range("a" & Rows.Count).End(xlUp)) 'increment the counter if the cell has value fridge If rngTemp = "Fridge" Then intCounter = intCounter + 1 Next rngTemp MsgBox "There were " & intCounter & " cells equal to Fridge" End Sub
View Replies!
View Related
Macro To Copy Specific Cells From Row From Source & Stop When Next Row Cell = Specific Value
I have a protected template and unprotected source worksheets - - - what I would like to happen is for the macro to start and if the source worksheet cell B3 equals "Report Total" then stop - otherwise copy template worksheet then copy 6 specific cells from the source to paste values to specific cells on the newly created worksheet (B_ to C7, D_ to I7, E_ to C9, F_ to K9, A_ to C11, M_ to K11 and then K13=F13-30) After that then start all over again unless the next row’s cell (B4, B5, B6, . . .) is "Report Total" then stop - - - the row count could be from one to a couple hundred. Here is what I have so far but I know that with each copy the name will change and as it goes down the source file each row will change and I also need help with that. Sheets("ee template").Copy After:=Sheets(3) ActiveSheet.Unprotect Selection.ClearContents Range("I7").Select Selection.ClearContents Range("C7").Select Sheets("source").Select ActiveCell.Offset(0, -11).Range("A1").Select
View Replies!
View Related
VBA To Choose The Source Cells For A List With Data Validation
when you choose Data Validation for a specific cell, you can choose list. Once you have chosen list for "Allow", you can select the source cells for what will be in the drop-down menu. I want to be able to use VBA (or just use Excel) to modify those source cells. For example: There will be 3 lists with drop-down menus using Data Validation. List 1: Choose first option in list -> List 2 uses source A Choose second option in list -> List 2 uses Source B etc... List 2: Choose first option in list -> List 3 uses source C Choose second option in list -> List 3 uses source D
View Replies!
View Related
Create Multiple Entries From Each Row (truncating Rest Of Row)
I have a spreadsheet with 4 columns of information, then a column for a primary name, and then many columns of secondary names (with a different number of secondary names for each row). For rows with more than 1 secondary name, I need to create a new row with a copy of all the columns up to the secondary name for each secondary name in the row. A row in the spreadsheet looks like this: data data data data PrimaryName SecondaryName1 SecondaryName2 SecondaryName3... SecondaryNameN I need this row to be replaced by the following set of rows: data data data data PrimaryName SecondaryName1 data data data data PrimaryName SecondaryName2 data data data data PrimaryName SecondaryName3 ... data data data data PrimaryName SecondaryNameN
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
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
|
TRACKER
