Open Another Worksheet Using VB
Jul 30, 2008
I am trying to write (but not very well as bit of a novice) some code to open another Excel workbook called 'Sweep Log'
once this is open copy certain data from my already open workbook to the 'Sweep log'. Save this log and then close it.
Once that has been done a Msg box to say 'data saved in sweep log'
result leaving my original workbook open
Private Sub CommandButton1_Click()
Dim oExcel As Object
Set oExcel = CreateObject("Excel.application")
oExcel.Documents.Open "R:ConnexSweep Log.doc"
oExcel.Visible = True
Sheets("Sweeplog").Select
With Selection
Range("A5:K100").Copy
Sheets("database").Select
With Selection
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Range("A5:J1000").Select
View 9 Replies
ADVERTISEMENT
Jun 14, 2008
is it possible to write vb code to open the workbook, scroll through everysheet? extract the values from each worksheet?
let's just say, I only know the workbook name, but not the sheets of the name inside. So the vb code could scroll through every worksheet without knowing the worksheet name, only the workbook name(workbook.xls)
View 9 Replies
View Related
Nov 20, 2006
Need a code to select cell A1 on a worksheet called "Welcome"; this needs to either be on opening the workbook or exiting the workbook
View 7 Replies
View Related
Feb 20, 2013
I want to be able to run the following lines of code to edit all xy-scatter plots on worksheet that is currently open by clicking and running a .VBS file. This is the code that works in excel in my macro. I'm having trouble getting the .VBS file to execute and run correctly. I realize I don't need the dim statements for the .VBS file, and have been able to get it to run without errors, but it doesn't edit the chart.
Code:
Sub ChartLineWidth()
Dim c As Chart
Dim s As Series
For Each co In ActiveSheet.ChartObjects()
Set c = co.Chart
For Each s In c.SeriesCollection
s.Format.Line.Weight = 1
Next s
Next co
End Sub
View 1 Replies
View Related
Jun 7, 2009
I have an excel 2007 worksheet named "Invoice" that I would like to be able to navigate with using a command button that will open a separate File Folder or sub folder called "Report" that stores all my reports in for viewing.
My excel worksheet named "Invoice" and File Folder or sub folder named "Report" are located in the same sub folder that is named “Marketing Report”.
This report will be used on different computers and I’m sure different location / Path on their computer.
Workbooks.Open Filename:=ThisWorkbook.Path & "Report"
and
Private Sub CommandButton3_Click()
Dim fn As String
ChDir ThisWorkbook.Path
Report = Application.GetOpenFileName()
Workbooks.Open(Report)
End Sub
I also tried to record a macro but would not record going to file folder.
View 9 Replies
View Related
Jul 8, 2014
Is it possible to update open worksheet which would stay open from different computer without doing anything to it.
View 8 Replies
View Related
Jul 22, 2008
I know of the password protect feature to open a excel file or workbook, but does anyone know if this can be done for opening a worksheet.
I want to be able to view a worksheet after a password is entered, so therefore different worksheets have different passwords.
View 9 Replies
View Related
Feb 13, 2014
When I open my workbook, I'd like to go at the first empty row of the worksheet.
View 14 Replies
View Related
Apr 17, 2014
What would be the macro to open a specific worksheet upon startup?
View 2 Replies
View Related
Apr 28, 2014
i have one page with 6 different links, each link will open one worksheet , i want to set password to each link to open there worksheet , i can update my file if it's required .
View 14 Replies
View Related
Feb 1, 2014
I am currently putting together a Directory and want each of the 19 programs and the worksheets to be maximized when they are opened.
View 1 Replies
View Related
Sep 13, 2007
I've written a macro and i've added a button to the toolbar so that it's easy for users of the spreadsheet to run the macro. the problem i'm running into is that the sheet that the macro's written in is a template. the macro formats some downloaded junk. soooo, every time someone downloads stuff, they copy-paste into the template, push the button, and it formats it.
my problem is that when they Save As, the button's assigned macro gets reset to the name of the file they saved as. therefore, when someone else comes along and press the button, it doesn't run the macro out of the original template sheet which they have open, instead it tries to find the last sheet that was saved and run the macro through it.
View 10 Replies
View Related
Nov 23, 2008
I have 2 worksheets in my workbook.
One is named "Link Menu" . . . The other is "Setup"
I have an object on "Link Menu" that can be clicked to run a macro.
On the Setup sheet, I have a workable link that opens my email. Whatever link that the user would type into cell D6 on the Setup Sheet would be opened.
I want to beable to click my object on "Link Menu" and have the macro open the link from cell D6 on the Setup sheet.
View 6 Replies
View Related
May 24, 2012
There used to be a way to open an Excel spreadsheet and have it go to a specific sheet....... I want to open Grants.xls and have it always go to the "index" sheet....
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 11, 2008
How do I create a msgbox that tells me that the worksheet is currently open? I dont mean opening the worksheet and the msgbox popping up upon opening but I need the msgbox to show when the page is currently open?
View 9 Replies
View Related
Jun 1, 2008
I have been trying for the past four months now to get my workbook to automatically open a new worksheet every 1st of the month.
but I have failed each and every month. I have asked others to assist but to no avail.
this is the code in my workbook as it looks now.
Dim strValue As String
Dim Sht As Worksheets, foundSheet As Boolean, TEMPLATE As String
Dim rng As Range
Dim irow As Long
Dim myDate As Date, newDate As Date, oldSheet As String, newSheet As String
Dim ws As Worksheet
Dim strName As String
Dim wsh As Worksheet
i keep getting an error on the activesheet=newsheet.
View 9 Replies
View Related
Feb 11, 2009
I have multiple worksheets that call one userform.
Each worksheet has a specific word in cell J1 that matches a worksheet name.
How do I select a a sheet based on a cell value in J1?
Code: .....
View 9 Replies
View Related
Jun 7, 2009
I am using excel 2007 and I’m trying to open a file folder from a worksheet using a control button. They are both in same file folder. I’ve have tried
Workbooks.Open Filename:=ThisWorkbook.Path & "Report"
I also tried to record a macro but would not record going to file folder.
View 9 Replies
View Related
May 2, 2006
1) I cannot figure out how to write VBA so the "Show Results" button will open the hidden worksheet when clicked.
2) When I use the userform to add new info, it does save to the worksheet, but when I cose the userform and try to open the spreadsheet, I get the following message, "Userform is already opened. Reopening will cause any changes to be disguarded". If I answer yes, I lose my changes, if I answer no, the changes are saved. I want this message to go away and data from the userform to automatically be added to the spreadsheet.
3) I want to make sure the "Account Number" field has a 9 digit number in it before it can be saved. If there is not a 9 digit number I would like an error message saying "Please enter a 9 digit account number" as a pop up.
4) I want to make sure both the "Account Number" and the "Assignment" fields have been populated before the data can be saved.
View 9 Replies
View Related
Jul 16, 2006
I have a macro that 'opens all' workbooks in the specified folder. I've copied it over, but need to only open all the wbk's if a specified worksheet exists w/in the wbk..I know there is a dim ws statement that can be used, but how do i use it w/ the current macro?
Option Explicit
Sub recTestOpenAll()
Dim x As Integer
Dim WB As String
Dim wbk As Workbook
For x = 1 To 100
WB = "G:Rule Test FilesREC " & x & ".xls"
On Error Resume Next
Set wbk = Workbooks.Open(Filename:=WB)
On Error Goto 0
If Not wbk Is Nothing Then
End If
Next
End Sub
View 4 Replies
View Related
Aug 30, 2006
I'm trying to avoid using macros in my workbook, however, I'm unable to get my worksheets to change name (based on a named range) without use of them, as follows.
I've written the macro:
Sub MyMacro()
ActiveSheet.Name = Range("MyRange")
End Sub
and then dropped the following into the worksheet
Private Sub Worksheet_Activate()
Run "MyMacro"
End Sub
This works fine, but I'm trying to avoid using macros. When I try to place the following script in the worksheet private code, nothing happens:
Private Sub Worksheet_Activate()
ActiveSheet.Name = Range("MyRange")
End Sub
View 9 Replies
View Related
Feb 2, 2007
i am tring to make any workbook i open when i writ in it, i want what i wrot to be in centerd
Private Sub Workbook_Open()
'*Center
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
With sh. Cells
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Next sh
End Sub ............
View 5 Replies
View Related
Jan 23, 2008
1. I receive a weekly report 'Over Due Orders_1_14_08.xls' for example. This is sent every Monday and there is a Worksheet on this report called Raw Data.
2. I want to copy Raw Data from that Workbook but the name of the report changes w/ every Monday's date.
Any way of doing this - like just looking for "over due orders" in the Workbook name?
View 5 Replies
View Related
Jan 4, 2010
I am trying to open a stored file on a network drive (with the file location stored in cell E4). When I try to run the macro, I get an error message on Line 3. (Sheets(E4.Value).Activate)
Does anyone know what I can do to fix this, or have a better method to open the closed files?
I've read that it's possible to reference information from a closed sheet, but I don't think that I'm experienced enough in VBA to take on that effort yet.
View 7 Replies
View Related
Oct 26, 2013
Is there any setting or technique to limit a macro to only effect the open sheet in the workbook that it runs from?
View 1 Replies
View Related
Mar 17, 2014
I need to populate combo boxes from a list of data on a separate workbook. I set everything up then found out the way I did it the seperate workbook must be opened before the drop downs are populated with the information. Is there a way to not have the linked workbook open?
Also these workbooks are all contained in their own file that must be duplicated on to other machines and into other larger files. I need that linked information to always follow the workbook contained in the same file. Another words if I have the original file "A" and duplicate it and now have a file "B" I want the combo box in file B to be linked to the workbook in file B.
View 2 Replies
View Related
Aug 8, 2007
Need vba code to automatically open sheet one when a workbook is opened? At the moment it opens on the page on which the workbook was last saved.
View 9 Replies
View Related
Apr 27, 2006
I have a formula that uses the OFFSET function so that subsequent row adds will automatically adjust the formulas. For example, cell A1 contains the value 1 and cell A2 contains the formula:
=offset(A2,-1,0) + 1
When I insert a row above row 2, the formula above adjusts to reference cell A3, which is what I want.
What I am running into is that when the workbook is opened, something in the workbook changes, causing the "Do you want to save the changes..." popup to appear even though all I do is immediately close the workbook.
I have traced the problem to my use of the OFFSET function, as when I change the formula to
=A2-1
and repeat the scenario, the workbook closes without the "save" popup.
Does the OFFSET function cause something to change (no cell values appear to have changed) and is there a way to get around this?
View 7 Replies
View Related
Sep 2, 2006
I have exported some raw data into excel. On another sheet in the same workbook I have a formatted sheet which will be my final report. I would like to know how I can copy data from one sheet to another via VBA code using the auto_open() function.
View 2 Replies
View Related
