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.
I have a spreadsheet called contacts which is as it says, I have another sheet called project management, I want to have a drop down list which refers back to the contacts spreadsheet, it this possible as they are two different files?
View 4 Replies View RelatedI'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.
I was given a spread sheet that prepares a file for export to some ones system. The macro in the spread sheet wouldn't run as I'm using excel 2013 which when you open a new work book it only opens sheet 1 and there was a bit in the macro that wanted to send information to sheet 2 which wasn't there. I added a bit of code which created sheet 2 and hey presto it worked fine. I made the mistake of telling the people who supplied the original workbook to me what I had done so that they could pass the modified sheet on to other users in case they had the same problem.
Now I've been asked if I could do a bit more work to the existing macro so that instead of the new workbook just being called Book 1 could I add a name and date to the new workbook being created.
I've had a look at the original code and it looks like it would be quicker to start from scratch as I cant follow the original code.
The existing workbook has 3 sheets, I want to copy sheet2 to the new workbook. I want to name the new workbook as "a fixed name" with the "time and date " .xlsx
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?
I am working with multiple workbooks with several tabs in each one. I need the forumula to update the "sheet name" from the source workbook even if the destination workbook is closed.
this is a portion of the forumula I am working with:
=SUM(IF(AND([L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet25!$I$4>=182,[L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet25!$I$4=182,
[L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet26!$I$4=182,
[L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet27!$I$4
I want see if it's possible to take the name of the source workbook and put it in a cell in my destination workbook and then use that as the reference in formulas. The source document title includes a date that changes yearly, or in some instances changes entirely, and I want to keep the formulas in the destination workbook intact. I don't want to have to go to every instance where I have used the source workbook and change the title. Instead I would like to copy the source workbook title, paste it into the destination in the same cell and have that propogate the needed changes into the destination workbook formulas.
View 2 Replies View RelatedI have a table of company data, and I want to be able to select different companies data and create a chart automatically perhaps by pressing a 'create' button.
The table data contains three values I want to populate in the chart: red & green sales which I would like shown as a stacked bar, and red as a percentage of total sales which I would like on a different axis as a line.
I tried to create a macro but my VBA skills are almost non-existent.
I'm looking to create a graph where the data source is two columns:
Column A (From A2 down to last but one populated cell)
Column ? (Last populated column on the right, from ?2 down to last but one populated cell.
all I have at the moment is the generic code for creating a graph (which I reverse engineered from a macro I recorded).
Code:
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("1_bth_x_wk").Range("A1:H40"), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="1_bth_x_wk"
Given a pivot table, is there any way that I can determine the source data that was used to create the pivot table?
I suspect that the pivot table was created using only part of the data, but I'm not sure.
I have created a pivot table in a workbook which relates to data from 2011 - 2012 and this works perfectly. What I now need to do is to copy the pivot table (without the data source) to a new workbook for data which will be collected from 2012 - 2013. Unfortunately when I copy it and try and find the new data source it does not update the filters according to the data in the new workbook.
View 5 Replies View RelatedI have two tables (Table 1 and Table 2). Table 1 has names of all employees in my organization ('Name' column) and corresponding information in the second column ('Textinfo'). I need to create Table 3 from table 1, but only select those employees who are in my team, ie matching names in the Table 2. I also need to extract corresponding 'Textingo' column information. I tried vlookup but it did not work because if there more that one name in the table 1, vlookup confuses it (i think). Would you know what function or code to use?
Sample file is attached : excel question 1.xlsx
The "Sum" sheet can change its number of rows. The pivot table is based on it. I'm having trouble with the SourceData portion of the code in my macro ....
View 9 Replies View RelatedI have been working on a workbook that uses as a source information on Sheet 1.
Sheet 1
Each item reports a number of different details from column A to column BY.
Each row is for a different item.
25 rows of information.
Sheet 2
This is my master formula sheet, the one I've been using to create the page that reports and formulates information on a single item. The source for this sheet is taken from the cells in Sheet1!Row2.
Now that I have the sheet pretty much finished, I want to paste the same report on each succeeding page for a total of 25 sheets. I can copy Sheet2 and paste into each successive sheet, but then I need to change every single formula so that where it said AT2 it changes to AT3, where it was H2, H3, etc., from A to BY.
Problem: Can I use something that will automatically take the information for succeeding sheets and take as its source the succeeding row from the source page?
I don't know that I'm describing that sufficiently, so:
Sheet 3 would take as its source Sheet1, row 3.
Sheet 4 would take as its source Sheet1, row 4.
Sheet 5 would take as its source Sheet1, row 5.
Sheet 6 would take as its source Sheet1, row 6.
Etc., etc.
In the past I used the find/replace function, but this sheet seems way too complicated for that. As an example, one cell alone on my master sheet is
=IF(ISBLANK(ConnectMLS_export!S2),(IF(ISERROR(LEFT(C15,FIND("X",C15,1)-1)*RIGHT(C15,LEN(C15)-FIND("X",C15))),"",LEFT(C15,FIND("X",C15,1)-1)*RIGHT(C15,LEN(C15)-FIND("X",C15,1)))/43560),(ConnectMLS_export!S2))
so I would need to change S2 to S3 everywhere it appears, but there are probably more than a hundred places on the sheet that I'd have to manually change 2s to 3s, etc., for 25 sheets, which is why I'm wondering if there isn't a better way than going cell by cell, sheet by sheet.
I manage to copy a worksheet into a new workbook, but how can I change to the old one and close it without saving
View 9 Replies View RelatedIs it possible to have a list from a different workbook as the source list for a validation dropdown on a cell? If so, what is the syntax.
View 2 Replies View Relatedi just want to generate a list validation where source resides in another workbook?
i have used named range concept ?but not working can anyone help..
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 3 Replies View RelatedI'im using an index - match - match formula with links to an external file.
Everything works fine until I close the source workbook. At that moment the liks break. The error I'm getting is #REF!.
I suspect the reason is that the source data are in form of an official table. I tried some formulas with data in the same workbook but outside the table and they keep working after the source file is closed.
I really want to keep the source data in a form of an official excel table.
I built a template which pulls data from the source file using formulas. the problem is formulas doesn't give any error it updates the right value when the source workbook is open.
I Thought problem could be with the formula and tried just Source A1=destination A1. it also updates only if the source file is open. when the source file is closed it shows the last captured value.
The source file is on the (Work) Network Drive and is accessible to few people. my template is password protected (tabs) so formulas cannot be deleted.
I currently have a macro to import user selected .Dat files into a new workbook, each on its own worksheet. My problem comes in trying to save this new workbook in the same folder as the imported .Dat files. I was thinking there should be a way to gather the file path from the imported files and use that in the Save As command.
[Code]......
I am trying to combine sheets from various workbooks into a single file named "Combine". The goal is to have the sheets in Combine indicating their origin.
For example, with:
"File_1" having 2 sheets, "Sheet1" and "Sheet2"
"File_2" having 1 sheet, "Sheet1"
the goal is to have a file "Combine-forum" with the sheets named:
"Sheet1(File_1)", "Sheet2(File_1)", "Sheet1(File_2)"
I have grabbed pieces of code from various sources and read on the forums, but the result is different from what I want; I don't succeed to add the file name to the sheet name. Instead of the file name, the second part of the sheet name appears to be the order in which the workbook have been opened.
To be clearer, what I get is:
"Sheet1(1)", "Sheet2(1)", "Sheet1(2)"
Attachments: To_be_combined.zip 3 compressed files to be combined. I suggest to copy them in C:TEMP and to copy this path in the InputBox (see below). Combine-forum.xlsm Best working file that I have been able to create
I am importing external data to "Workbook A" from the source workbook, "Workbook B." Workbook A is set up to refresh every 15 minutes. Workbook B is shared. I am recieving an error saying either, the 'file is in use', or 'the file is locked,' depending on the senario.
Scenario 1:
1. I open A and enable data connections.
2. I try to open B, but receive the message: '(File location...) is in use. Try again later.
Scenario 2:
1. I open B.
2. I open A.
3. I go back to B to edit information.
4. When I try saving the information I get the following message: 'File is locked. Try the command again later.'
I need to be able to have A open and periodically refreshing. I also need to allow users unhindered access to B at the same time.
I have a pivot table in spreadsheet A. I have some formulas in another spreadsheet B. The formulas look up data in the pivot table in A and use that data in B to return and answer. Is there a way to refresh the pivot table in A from B without having to open A and refresh the table?
View 3 Replies View RelatedI am new to the VBA editing and I am missing some steps to get my files correctly generated and then ideally saved in PDF format in the same folder.
I have two files; one is an invoice template with the following fields:
Invoice Reference: Line 8 column D
Issue date: Line 9 column D
Client name: Line 11 column E&F
Client address: Line 12 column E&F
Product name: Line 16 column E&F
Product details: Line 18 column E&F
Production date: Line 20 column E&F
Delivery date: Line 22 column E&F
Units: Line 24 column E&F
Total Units: Line 26 column E&F
Total Invoiced: Line 30 column F
Each sheet in the "template" workbook should be named after the Invoice Reference.
Secondly I have the source file in which the data is organized as follows: (Both are in the same folder)
Invoice Reference: column A
Issue date: column B
Client name: column C
Client address: column D
Product name: F
Product details: column G
Production date: column H
Delivery date: column I
Units: column J
Total Units: column J
Total Invoiced: column E
One invoice needs to be created per line in the source workbook. I tried many times the macro recording without getting the correct outputs.
I've created a chart in sheet 1 in a workbook. I want to copy and paste that chart into another sheet (lets take sheet 2) of the same workbook. I am using Excel 2010 version.
Whenever, I try to copy a graph and want to do "Paste Special as Link picture". The problem I am facing as "Paste Link" option is inactive.
I am attaching the Excel for your reference.
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.
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?
So I've got Sheet 1 with say
____A___B___C
1___m___i___c
2___r___o___s
3___o___f___t
I would like to create a button that can create a new sheet and paste A1 to C3 at the same location on the new sheet
and I need this to create a new sheet and do that everytime the button is pressed.....
[URL]....
I have 4 workbooks, all of them contain data that is managed by other teams at my company. I need to take all of this data, organize, and concatenate it into one sheet for myself, that I will then reference in other workbooks to various OTHER departments in our company that need the data for certain projects. I have the organization laid out in a lot of very complex formulas so that most of this is automated, and not manual.
I need a solution that can pull data from the 4 workbooks without locking them for use, to allow me to keep my document open all the time, and they can update theirs at their leisure. Everything works fine, until I hit the refresh button. Once this is hit, all of the linked documents are locked and cannot be opened, even in "Read Only" mode. Once I close the master document, the files are free to be opened. I know a workaround for now is that i can not "refresh" the document, but rather close and re open it for changes.
Some steps I have already tried :
1. Create an intermediary file that no one uses, and can be locked all day without a problem. This doesn't work for me since the intermediary file needs to be open to refresh, which locks the original source doc, leaving me in the same place as before.
2.Modify the connection string to display "Mode=Read;" instead of "Mode=Share Deny Read"
3.Create a new connection with "Read" only selected in the Advanced Tab