Moving Data From Source To Destination With Open / Close
May 12, 2014
My first shot at a function! hows it look?
Code:
Function wbIsopen(wbname As String) As Boolean
On Error Resume Next
WbIsOpen - (workbooks(wbname).Name = wbname)
End Function
[Code]...
This is also my first shot at using a DIM and Set. I am attempting to move the data from 1 workbook sheet to another after it has been sorted. But if i only have the source open i want it to open the destination workbook and sheet then paste and close the source sheet. If the source is open I want it to go to the next command.
View 3 Replies
ADVERTISEMENT
Feb 1, 2010
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 11 Replies
View Related
Jan 17, 2014
I have extracted data from multiple sheets and now want to mark this data as "Paid" and for the macro to go back and change the value in the original sheet on a row in column "AR" to "PAID.
All the columns have the same data type in.
The extracted data contains the original sheet name and each row has a unique ID No to it.
I know I probably need to use INDEX and MATCH and then Offset to post value.
Attached worksheet shows the data and columns.
View 14 Replies
View Related
Dec 10, 2009
I am sure this is simple but cant get me head around it (tried paste & paste link).. I am wanting to copy formulas in file (A) to file (B) with data source linked in the file (A).
i want is when i copy the formulas to file (B) sheet 2 and it should retain the original path say H:excelfile A[sheet1]$G$1+....xls and source from which the data is being used.
View 7 Replies
View Related
Jul 29, 2008
I'm positive that this is a dumb question that's been answered elsewhere, so pardon my n00bness -- this is a one-time project for a non-programmer.
My manager needs to edit values in a spreadsheet. This person is non-technical and put off by spreadsheets, so I want to create a custom view of the data to show only the editable data (with pretty colors and fonts, etc.)
I have a source worksheet and a display worksheet. I need the display worksheet to show the values in the source worksheet, and I need changes in the display sheet to change the referenced data in the source sheet. For example, if cell Source:A1 = "thingamabob", then cell Display:A1 = "thingamabob". If user changes cell Display:A1 to "hoodgie", then cell Source:A1 changes to "hoodgie".
Essentially, the user has to be able to edit the source cell via the display cell.
Is that possible?
Again, please excuse my ignorance -- for all I know, this may be an automated function in Excel... but I've been trying to figure it out for two hours and so I thought I'd ask you nice people.
View 9 Replies
View Related
Oct 27, 2011
I have a source data sheet that has around 50 columns. I want to copy and past selected columns to a destination sheet.
View 1 Replies
View Related
Jun 29, 2013
I am pulling data from a database and want to do an average based on data that was input per day, basically I did a SUMPRODUCT in a different file to avoid having the source open, but I can't make it average the data and disregard if there were ZEROS in that day.
e.g.
Column A Column B Column C
6/25/2013 A 1.3
6/25/2013 B 1.45
6/25/2013 C 1.9
6/25/2013 D 0
In the other file I would need to summarize the average of the Data from COLUMN C based on the day but without counting the zeros
View 1 Replies
View Related
Oct 9, 2009
I currently have a macro that goes into a specified folder and opens all the files within that folder. This works great for me, but if i have to change the location of the folder i need go into the macro and change the destination code.
I was hoping to let other people use this, but not necessarily people who would be able to go in and manually change the folder location. What i was wondering is if there is a function out there which would pop up a browse box which would allow the user to specify the folder location in which the files are kept? This would mean any user could use the tool to open the files without having to have the spreadsheet setup up on their machine.
View 9 Replies
View Related
Jul 17, 2014
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
View 9 Replies
View Related
Jan 15, 2009
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 Related
Jun 6, 2014
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 Related
Sep 7, 2006
I have a report that was created for 2005 that contains two worksheets: a "source data" worksheet and a " pivot table" worksheet. I cleared out the 2005 data in the "source data" worksheet and replaced it with 2006 data...after this I refreshed the Pivot Table and everything seemed fine. When looking at the file size I noticed that it was almost twice its original size....upon further investigation I found that the Pivot Table was internally holding onto the old source data (the "Show" functionality of the rows/columns in the table lists the 2005 row/column headers as well as the 2006 headers....even though no data from 2005 is shown in the Pivot Table).
Does anyone know how to purge the old data from the internal Pivot Table memory?
I hope this is enough information....let me know if you need more.
Thanks in advance for any help,
Jon
View 9 Replies
View Related
Apr 14, 2013
I want to fill all a column bottom-up but without specify cells in format "A1500:A2" like
VB:
Range("H1578").Select
Selection.AutoFill Destination:=Range("H2:H1578")
{H1578 have a formula so...}
I have data in the first 7 columns, with a variable(Range) for the first 5 columns
VB:
Dim oRange As Range
Set oRange = ActiveSheet.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(89, 4))
{The first cell in it is A1}
My question is : How to fill Range("H2:H1578") using my ''oRange'' variable?
I try with OffSet but my code didn't work. I need to do the same with the 6 columns to the right and then plot in a chart.
View 4 Replies
View Related
Apr 14, 2008
Can I take
Workbooks.Open ("N:Data ManagementDashboardLOLTickler Codes.xls")
and do something like this?
Workbooks.Open ("N:Data ManagementDashboardLOLTickler Codes.xls") Not _
Workbooks.Open ("N:Data ManagementDashboardLOLTickler Codes.xls")
I have a userform that I would like with a click of a button to open the workbook and then click the same button to close the workbook after it has been viewed.
View 9 Replies
View Related
Jun 23, 2006
I've run across something a little strange. I have a query (in, say, Workbook A) that links to a table in another Excel workbook (call it Workbook B). If somebody happens to have workbook B open (it's on a network) and I try to refresh the query, it actually opens workbook B to refresh the query. If it's not being used, it just refreshes without opening the file.
The issue is that I'm refreshing using macros, and when another workbook pops open, the macros break when they try to use other sheets and ranges and stuff. Obviously, the simple answer is to just use a whole bunch of "ThisWorkbook" statements to make it work. However, I never wanted the workbook to be open in the first place. What's more, if I refresh the macro again, an additional VBA project opens for workbook B (so, now I'd have 1 project for WB A, and 2 projects for WB B). This just seems odd.
Anyway, my question is this:
1) has anybody seen this before, or is it explainable?
2) Is there a way in VBA to determine if a file is in use prior to opening it?
View 3 Replies
View Related
Jan 16, 2009
When i open excel it give an error and it close. My computer up to date about office upgrades. And i try to repair my office but it doesnt solve my problem.
View 7 Replies
View Related
Jun 3, 2014
Below is the code to open a csv file and paste it in the workbook "ResultFile". When i click on a button, a csv file will open to copy the values and paste it in "Resultfile". Now, i want to close the csv or excel file to be closed once copying has done.
where to put the close file.
[Code] ....
View 2 Replies
View Related
Jul 27, 2006
I'm creating a template that will be opened by users and saved to a new name based on info provided by the user. I have macros triggered when opening and closing the workbook. The Before Closing sub does some data transfer between workseets. I'm running into a problem with the Open sub, however. I wanted to give the user a chance to abort the opening, giving a dialog that allows canceling. I wanted to just close the workbook.
However, if I include an Active.Workbook.Close line in the Open sub, it of course triggers the Before Close sub, but it leaves the On Open sub hanging. The workbook closes, but the next time I open it, the dialog in the Open sub doesn't come up. Close again, and it works ok or quit Excel between openings and it is ok. If I let the Open macro complete, bypassing the remainder of the macro if the user indcates canceling, how to I trigger closing within VBA? If the Open macro completes without an Activeworkbook.close statement things will just sit there until the user initiates closing.
Is there a way to terminate the Open sub from within the Before Closing sub if I create a flag to indicate closing started during the open sub or some other way around this problem?
View 5 Replies
View Related
Feb 25, 2008
I made an Excel based program that uses multiple workbooks with two main workbooks (“Master List” and “Products”) that all the others pull information from. I run into a problem when a user clicks the close X in the upper right hand corner and the entire application closes. I found some code that will let me close all the workbooks but one (the code is put in the “Products” workbook in the BeforeClose event).
For Each wb In Workbooks
If Not wb Is ThisWorkbook Then
wb.Close SaveChanges:=True
End If
Next wb
Is there a way to modify the code so it will close all the workbooks but the two main ones if someone clicks the close X button in the upper right hand corner?
View 3 Replies
View Related
Apr 10, 2008
When i open an excel file, i need it to print 3 copies and then close itself without saving. This is because i have a file that must have 3 copies printed everyday. I know its lazy =) but i'm sure it will be very useful and i have seen it on a file before however i cannot successfully replicate it.
View 3 Replies
View Related
Nov 14, 2008
Having some issues moving between 2 open workbooks. I just want to add a new workbook, select the first workbook and active sheet then move that sheet to the new workbook that was just made and lastly save the new workbook with the name of the sheet that was just moved. I have some code but it is not working correctly.
View 5 Replies
View Related
Sep 29, 2011
I am currently pulling in all cells from various worksheets, into a specific tabs in a workbook. Each source is pulled into a seperate tab such as Workbook A is pulled into tab Company A using the following formula which works fine.
Code:
"Path[Workbook.xls]Worksheet!ReferenceCell"
Note: This is done due to the use of indirect in the next formula and its inability to work on closed workbooks. Within a summary tab, I am then pulling in specific fields from each of the aforementioned worksheets using the following formula
Code:
=IF(OR(M$220="",M$218=""),"",IF(ISNA(VLOOKUP($B223,INDIRECT
("'"&M$217&"'!"&M$220),COLUMNS(INDIRECT(M$220)),FALSE)),"",VLOOKUP
($B223,INDIRECT("'"&M$217&"'!"&M$220),COLUMNS(INDIRECT
(M$220)),FALSE)+M$222))
M217 - Worksheet Name
M218 - Worksheet Date
M220 - Worksheet Range
M222 - Additional Fee
B223 - Lookup Value
The issue is that one company out of 14 pulls into this workbook (using the first code) just fine with all values visible, but ONLY when the source file is open. Even if I manually update the link, the values do not change.
Is there a setting or something to check, maybe in the source workbook? I have ensured that automatic updates for links is on and there are no macros in the source workbook.
View 2 Replies
View Related
Mar 5, 2007
I've got a 'Control Panel' spreadsheet which is all based on UserForms. When a user clicks a particular button, it opens one of many separate 'Regional' sheets which they work in.
When the user closes the 'Regional' sheet, I would like the 'Control Panel' sheets userforms to show again. However I can find no way of doing this as it simply switches back to the 'Control Panel' sheet without loading the forms (as I had to hide the 'Control Panel' useforms to give them access to the 'Regional' sheet).
View 9 Replies
View Related
Jul 10, 2012
I use the below code to close ALL open Internet Explorer pages. Im trying to adapt this to close ALL Windows Explorer Folders.
Dim Shell As Object
Dim IE As Object
Dim i As Variant
Set Shell = CreateObject("Shell.Application")
i = Shell.Windows.Count
On Error Resume Next
Do While i > 0
i = i - 1
Set IE = Shell.Windows(i)
If TypeName(IE.Document) = "HTMLDocument" Then IE.Quit
Loop
View 3 Replies
View Related
Feb 11, 2013
I have a batch script which runs the following line to open my excel sheet:
start m:exportad.xls
when this sheet opens it automatically refreshes from a csv file.
The problem is that I want this sheet to open, refresh and close. To close Excel I have been using the taskkill command in my batch script, but I have found that using this method doesn't update the sheet properly - the sheet only updates properly if I close Excel in the correct way, but this means manual input and I want this to be automatic.
View 3 Replies
View Related
Feb 21, 2013
I opened a file, by file I mean a "file" with no extension that has commas separating it.
So I recorded a macro to open up the file and this works fine.
How do I close it? I tried
wk1 = ThisWorkbook.Name 'Main Worksheet
...
Workbooks(wk1).Activate
ActiveWindow.Close
But the problem is that "wk1" is not set equal to the name, probably because the file is not an excel file, it has no extension.
View 2 Replies
View Related
Apr 30, 2013
I know the shortcut for grouping columns in excel is Alt + D + G + G or Shift + Alt + Right Arrow. Is there a shortcut to actually open and close grouped columns (i.e., the equivalent of clicking on the + sign at the top) without using the mouse?
View 1 Replies
View Related
Aug 6, 2013
I use the following code to open an existing open workbook in a new instance of Excel...
Everything works fine... What I would like to do is close (when I say close I mean, completely exit/quit the application). I'm not able to do that, the original Excel instance stays open with no spreadsheet open. I would just like it force the application to quit so that after running the code I would just have one Excel instance open (with the desired workbook, which works fine now)...
Public Sub Re_open_workbook()
Application.DisplayAlerts = False
ThisWorkbook.Save
[Code].....
View 1 Replies
View Related
Apr 10, 2014
Is there a way to automatically open or close all grouped rows on a specific worksheet or for specific rows?
View 5 Replies
View Related
Mar 22, 2006
Assume that the workbook Book1.xls opens a user form named myUserForm.
I'm trying to show only the user form on the desktop when I open Book1.xls, with Book1.xls minimized on the Taskbar.
And , if the Cancel button on the form is clicked, then unload the form and close Book1.xls.
That simple!
In ThisWorkbook, I have the event:
Private Sub Workbook_Open()
ActiveWindow.WindowState = xlMinimized
ShowTheForm
End Sub
On the Form, I have the Cancel button:
Private Sub btnCancel_Click()
Unload Me
ThisWorkbook.Close True
End Sub
The above procedure shows the form, with Microsoft Excel blank window in the background, and produces a small bar at the bottom left of the screen for Book1.xls
Click the Cancel button, and both the Form and Book1.xls close, but the blank Excel window remain open !
I suppose I would not be able to edit this version of Book1.xls, but that is fine for now!
View 9 Replies
View Related
