Open Workbook, Find Sheet That Contains Cell Value From Active Workbook
I'm trying to figure out a way to find a specific sheet in a workbook that does not contain the macro. Within the macro I have a cell which holds the name of the specific sheet I would like to find but I can't get it to work for some reason...
'Dim officen As Integer
'Dim thiswb As Workbook
officen = Range("A2").Value
Set thiswb = ActiveWorkbook
' Open the Active Info file
Workbooks.Open "C:My DcoumentsActive 20080616.xls", , , , "xxxxxx"
' Dim sourcewb As Workbook
Set sourcewb = Workbooks.Open"Active 20080616.xls"
Sheets("officen").Select
RowCount = ActiveSheet.UsedRange.Rows.Count
Range("B2").Select.............................
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Find Active Sheet In A Workbook
I am creating a macro that will open another workbook and take all the contents off a page in it and paste the contents to itself. The problem is that sometimes those workbooks will only have one page and it will have the data I need, but sometimes there will be two or three pages in the workbook, but again, with only one sheet that has data on it. How can I have the macro find the one page with data on it?
View Replies!
View Related
Close Active Workbook And Open Another
Two workbooks named"test1.xls"test2.xls I have a userform in "text1.xls" workbook when I click on commandButton it validates Staff name and Staff number stored in sheet1 of "test1.xls" workbook and opens test2.xls file which is in different folder. Problem is how do I code to close or unload "test1.xls" after opening test2.xls Private Sub goButton_Click() F$ = "D: estFiles est2.xls" ' can I use Path so I don't need to type the full directory path Workbooks.Open Filename:=F$ End Sub
View Replies!
View Related
Open Workbook, Find Cell Value & Input To Cell Next To It
I have Quote Master.xls open I have a value in AA1 that carries a number I want to open Quote Log.xls I want to find the cell in Column A (Quote Log.xls) that AA1 (Quote Master.xls) directly corresponds to I want to offset from that found cell 3 cells to the right and insert from T7 (Quote Master.xls) into this offset cell. Here is the code so far Private Sub InputIntoQuoteLog7_Click() Dim CostSheetBook As Workbook Dim QuoteLogBook As Workbook Set CostSheetBook = Workbooks("Quote Master.xls") Set RFQQNumber = CostSheetBook. Sheets("RFQ").Range("AA1") Set RFQQStartDate = CostSheetBook.Sheets("RFQ").Range("T7") Workbooks.Open ("\ACT3ENGVAULT EngineersLsheriffDocuments (2008)Quote System MashQuoteLog.xls") Set QuoteLogBook = Workbooks("QuoteLog.xls") Set vOurResult = .Find(What:=RFQQNumber, After:=[A1], _ LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3) vOurResult.Value = RFQQStartDate.Value End Sub I get an un-numbered error on the .Find
View Replies!
View Related
Copy Data From Open File To Current Active Workbook
I want to create a standard macro that will copy values from a Master workbook to the current, active workbook I have open. The problem I have run into is when I record a macro it also records the name of the active workbook I'm copying data into and I would like it so when I bring up a workbook I just run the macro and it copies the values in. I'll be doing 8 different macros that each copy different values from the Master spreadsheet
View Replies!
View Related
Find Last Row In Non-active Workbook
I have a number of files which I am opening, manipulating and copying the result to a master workbook I am attempting to use code like this Selection.EntireRegion.Copy Destination:= Workbooks(Master). Range("A" & LastRow) Is it posible to define the value of LastRow without making Master the active workbook?
View Replies!
View Related
Save Active Sheet To Another Workbook
Copy the active sheet that is open in a work book and save it to another file with the date in the name? For example Report_11-03-08.xls The only problem I see with this. How could I also make it append a letter to report if the file already exits Example saved as Report1_11-03-08.xls. Then just continue incrementing. Sub CopySave() Application.ScreenUpdating = False ActiveSheet.Copy Application.DisplayAlerts = False ActiveSheet.SaveAs Filename:=ThisWorkbook.Path & "/" & "Report_" & Format(Date, "mm_dd_yy") & ".xls" ActiveWorkbook.Close Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
View Replies!
View Related
Save Active Sheet As New Workbook, Formats & Values Only
I would like to link a macro to a button on the active sheet, that saves the active sheet In a New Workbook, with Format And Values Only. Preferably saved in the same location as the origonal workbook. This is my main goal. If possible I would like the new workbook to be named from a chosen cell (E19) on the active sheet with date added.
View Replies!
View Related
Automatically Open New Workbook After 1st Workbook Open 4 Seconds
I have a presentation that I open from a short-cut. After the "Welcome Page" is opened, I want to open a second workbook in a new instance of Excel after 4 seconds. I think that I can open the new instance of excel, but I don't know how to activate the macro after 4 seconds. I'm sure there is a function someplace for this that can be used in a macro. Then, after the second Workbook is opened, I want the Welcome Page "Workbook" closed, leaving the second Worbook open.
View Replies!
View Related
Make Workbook Open- Open Correct Month Workbook
I have Monthly sales sheets that import my cash register data into them. I wanted to set them up to do everything without being there. So I have my task manager open excel at 9:30pm everyday and it runs the macro to import the data into the correct day of the month. Here is the workbook open macro- Private Sub Workbook_Open() Dim dTime As Date dTime = Time If dTime >= TimeValue("9:30 PM") And _ dTime < TimeValue("9:40 PM") Then ImportData End If End Sub This is in my July spreadsheet only. So is there a way to make it know which month spreadsheet to open on the 1st of the month? So come August 1st it will automatically open the August workbook and input the data for the first day? By using the date?
View Replies!
View Related
Copy Worksheets From Active Workbook To A New Workbook
I am trying to code a Macro so that i can take all the worsheets and save them as individual Workbooks. I wrote a macro that appeared to work, but, after it saves the first sheet as a workbook, i get a debug error. MS VB Script error: Runtime error '9': Subscript out of range Any advise would be greatly appreciated. Thank you Code is below.. Sub saveall() ' ' For Each ws In ActiveWorkbook.Worksheets ThisFN = "C:Documents and SettingsUserDesktop" & ws.Name & ".xls" I = I + 1 Sheets(I).Select Sheets(I).Move ActiveWorkbook.SaveAs Filename:= _ ThisFN, FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False Next ws End Sub
View Replies!
View Related
Making Workbook Opened From Other Workbook Active
I have a workbook that is a formatted report that I need to insert data from a name variable "CSV" file. I have created a macro in this workbook that calls an "Open" dialog for CSV files that I can use to browse to and open the source file. The macro then is supposed to "select all" copy and close the workbook then make the original workbook active and paste the data starting at row 2. The macro "seems" to be working perfectly except when I do the open the macro then makes the original workbook active, selects all copies then wants to close the original workbook. How can I tell the macro that the csv file that I just opened should be the active workbook, baring in mind that the name won't be known before the time it's opened so I can't hardcode the "active. workbook (NAME) "route.
View Replies!
View Related
Open Workbook, Find Value Add Date To Row
The following code is supposed to open a workbook, find txtVinput.value and put todays date (now()) in column C. it doesn't generate any errors, but it doesn't add the date either. I'd post the file, but its just over the limit and I don't want to get into trouble (50K) The whole prog is a userform that the operator enters a receipt number and it posts to another workbook, with the date. The operator also has the option to insert a date when the paperwork is returned. This is the part that is causing me trouble. Sub InsertReturnInDatabase() Application. ScreenUpdating = False Application.EnableEvents = False
View Replies!
View Related
The Workbook Need To Be Open To Move Sheet
I am working on code to move worksheets to existing workbooks based on the filename in a cell value. Here are my questions... 1.Does the destination workbook need to be opened before I can move a worksheet there? 2. How can I write the code to take the sheet name which is also in column A of "Data" sheet and the path or destination to move the sheet name to the path that is in column B of the "Data" sheet? 3.Can someone help me with the code? All the worksheet names and copy destination is in the "Data" sheet. Here is what I have for code... _____________________________________________________ Dim wbBook As Workbook Dim qsSheet As Worksheet Dim dsSheet As Worksheet Dim wsSheet As Worksheet Dim dpStart As Range, dpData As Range Dim ptStart As Range, ptData As Range Set wbBook = ThisWorkbook Set qsSheet = wbBook.Worksheets("Query") Set dsSheet = wbBook.Worksheets("Data") Set wsSheet = wbBook.Worksheets With dsSheet Set dpStart = .Range("A2") Set dpData = .Range(.Range("A2"), .Range("A65536").End(xlUp)) Set ptStart = .Range("B2") Set ptData = .Range(.Range("B2"), .Range("B65536").End(xlUp)) End With For Each wsSheet In ThisWorkbook.Worksheets If Not wsSheet.Name = "Data" And Not wsSheet.Name = "Query" Then wsSheet.Copy
View Replies!
View Related
Open A Workbook With Only One Sheet Visible.
How do I get a workbook to open with a selected sheet visible, or preferably all hidden? I already have a userform that opens upon opening the workbook that the user 'Enters' the workbook with. My untidy method would have been to use an If -Then statement for each sheet in the Private Sub Workbook_Open. Is there a neater way of doing it?
View Replies!
View Related
Open Workbook, Find Last Row & Paste, Save And Close
What i need to do is Open a Closed workbook (which i have done in the code below) 1 . When this book opens there is a msgbox that needs to have "template" pasted in to then ok automatically clicked ( i have no clue if this can even be done) 2. Then go to Sheet("contactor info") find last row then paste certain info. Below is a peice of code i have from the open workbook but of corse it doesnt work. The red is where it hangs up. openpath = ActiveWorkbook.Path & "" Set wb = Workbooks.Open(openpath & "Template.xls") With wb.Sheets("Contractor Info") . Cells(Sheets("Contractor info").Rows.Count, "B").End(xlUp)(2, 1).Select ActiveCell.Value = Msg1 ActiveCell.Offset(0, 4).Value = Msg2 ActiveCell.Offset(0, 5).Value = Msg3 ActiveCell.Offset(0, 3).Value = Msg5 ActiveCell.Offset(0, 2).Value = Msg4 ActiveCell.Offset(0, 1).Value = Msg6 ActiveCell.Offset(0, 6).Value = Msg7 Application.Calculation = xlCalculationManual..................
View Replies!
View Related
Workbook Save, Make Sure Active Cell Is A1
When saving a file that has 5 sheets that extend to the right of the users screen, sometimes the left part of the report is left somewhat hidden because the last person to save it was using the part to the far right. Is there a way to move all the active cell to A1 and make sure rows A:D or so are showing?
View Replies!
View Related
Find Active Cell Value On Another Sheet
I am having problems getting a VBA search function to work... this is the code I am currently using with very little success: Sub FindApp() Selection.Copy Sheets("Applications").Select Cells.Find(What:="ADT32109", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate End Sub What I am trying to achieve is to copy the contents of the active cell in the first sheet and find it within another sheet but I am unable to get the What:= section of the code to take the value of the copied cell.
View Replies!
View Related
Import Data From Several Closed Workbooks To A Single Sheet In An Open Workbook
I need to import the data from a specific sheet (same named sheet on all closed workbooks) to a sheet in an open workbook. All the columns are identical in every workbook but the number of rows is variable, so the data from each subsequent workbook must be appended to the end of the current data. Whenever a button is pressed, this macro will clear the sheet, then import the data starting in A3. The workbooks are in different folders but they all have the same name, so some sort of explorer window will probably be needed to actually select each file.
View Replies!
View Related
Abort Workbook Open If Workbook Is Read Only.
I've got several workbooks that users will be using to keep track of individual projects. These workbooks will talk back to a master summary sheet that has the status of all projects on it. The possibility exists that someone else on the network will have the summary book open when the program tries to update it, which would cause an error when it opens as read only. So I want to do something like this.
View Replies!
View Related
Copy From Closed Workbook To Open Workbook
I'm trying to write a macro that accomplishes the following: " Book 1" is already open. The user runs a macro that lists all .xls files in directory "d:measurements" The user selects the desired file from the list or box the macro copies from this "book 2" " sheet 3", " range A6:I107 and pastes (values only) into "book 1", "sheet 5", "range A6" End of macro. I want to accomplish this without opening the selected file (book 2)
View Replies!
View Related
Find Sheet Name Within Workbook.sheets
I'm currently writing a macro to check for changes between versions of a spreadsheet. I'm having a problem looping through the sheets and selecting equivalent (named the same)sheets in both sheets. My attempt at code (stripped down a little) is here. Option Explicit Sub Difference_Checker() Dim Check As Boolean Dim Row As Integer Dim Column As Integer Dim ColRef As String Dim MaxLength As Integer Dim MaxWidth As Integer Dim i As Integer Dim j As Long Dim Shtname As String Dim StartTime As Variant MaxLength = 100 MaxWidth = 256 'Set Application Settings Application. ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual....................
View Replies!
View Related
Open Workbook From Cell Value
say that in Sheet1 ( i renamed it to "Input Data") contain data in 2 cell: Q5 = 08-AUG-2009 Q6 = 30 I want to use this cell to look up a folder name i.e: "Z:Shift ProdSouthCOMBINED LOGS 20098-AUG-200930" and use the code below, but didn't work.
View Replies!
View Related
Open Workbook With Name In Cell
I would like to create a macro (in a destination spreadsheet) which will open up a (source) spreadsheet. The file path and spreadsheet name will be manually entered into cell A1 (on the destination spreadsheet) so the macro would need to refer to this cell to find the correct source spreadsheet to open. I would then like to attach this code to a command button so that the action above is executed when the button is pressed.
View Replies!
View Related
Open Workbook Based On Cell Value
I am trying to make a spreadsheet that has a macro that will allow me/user to open a specific workbook in another folder based on a specific cell value in the active spread sheet. I.E. if cell B2 has a value of 7101010 the macro will open that .xls file when used or any other part number typed into B2.
View Replies!
View Related
Use Cell Data To Open File/Workbook
I have a cell that is pulling data from a separate file, no problem there. However, I want the name of one of the folders within that path to change monthly (eg. by monthly name). How can I make a file path incorporate a cell link within a filepath (eg. instead of it saying 'Aug 06' I want it to refer to cell A1 which reads 'Aug 06').
View Replies!
View Related
Open Workbook Using Name In Cell & Copy Data
I have a VBA-coded macro in an Excel workbook that is to open another existing workbook -- a .xls file, not .csv -- copy data from it, paste that into the original workbook where the macro resides, then close the source workbook. If I use the debugger to step through the macro -- putting a breakpoint at its first executable line, run the macro, then F8 step through it, it works fine. (With delays at the open, close statements.) Free running, it doesn't. I suspect it's a timing issue, that the macro runs full tilt even though the new workbook isn't yet open -- but I don't know how to remedy that. Here is the situation and the simplified code (the actual macro does a lot more, of course): Main workbook: Two sheets, "Parameters" that has the fully-qualified filename for the desired source .xls in A1; and "Data", into which I want to paste data from that source. Source workbook: Single sheet, "Sheet1" Macro code. For simplicity, it is trying to copy/paste all of the source worksheet's content: Sub Go() ' ' Go Macro ' Test of getting data from another spreadsheet ' ' Keyboard Shortcut: Ctrl+Shift+G ' Dim SourceFile As String Dim HomeBook As String Dim OtherBook As String Sheets("Parameters").Select SourceFile = Range("A1").Value HomeBook = ActiveWorkbook.Name right after the open, hoping that might cause a dwell for the open to complete. When I step through it, the Locals view has all the parameters, getting set properly. If I try to simply run it, it stalls at the opened source workbook, the Locals view is empty, no data has been transferred into the initiating workbook. If I place the breakpoint after the open, it is not reached. (I have the VBA open.) Hitting F8 then yields a VB error window, "Compile Error" "Expected: To"
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
Open A Workbook And Run 1 Of 4 Macros Based On 3 Cell Values
I'd like to automatically run 1 of 4 macros depending on some criteria. Every workbook created has, unfortunately, the same worksheet name, so that leaves the only differences between the 4 possible loaded workbooks in the cells area and even those can be similiar in many ways. But....I found some criteria to separate all 4 workbooks...Here they are... run macro 1 if this 1. Worksheet name says "Screen" 2. Cell H1 has the word "Lead" run macro 2 if this 1. Worksheet name says "Screen" 2. Cell B1 has the word "records" somewhere in the cell 3. Cell H1 has the word "Lead" run macro 3 if this 1. Worksheet name says "Screen" 2. Cell B1 has the word "records" somewhere in the cell 3. Cell N2 has the word "Delivered" run macro 4 if this 1. Worksheet name says "Screen" 2. Cell B1 has the word "records" somewhere in the cell 3. Cell N2 has the word "Bevel" I don't know if this can be done, but if so, that would be fantastic! I'm thinking that the macro would have to be "global" and in the user's personal workbook?
View Replies!
View Related
Import CSV File Using Cell As File Name & Active Workbook Path
I am trying to import some csv files so I can combine them, but am having probs with the filename and location. Sub test() Dim wsName As String wsName = ActiveCell Sheets("Data").Select With ActiveSheet.UsedRange LastRow = .SpecialCells(11).Row End With With ActiveSheet.QueryTables.Add(Connection:="TEXT; &thisWorkbook.Path &" " & wsName &", Destination:= Range("A" & LastRow)) .Name = wsName .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells.....................
View Replies!
View Related
Open Workbook Where Named Cell Contain Name & Path, Copy Range And Paste Values
I am successfully opening a .csv file using a variable value stored in a named range in my Main file (the variable includes the directory and path). I copy data from the .csv file to the Main file then I need to close the .csv file without saving but I want to do that by using the Windows("xxxx").Activate command where "xxxx" is the namedrange in my Main file which stores the .csv filename (without the directory and path prefix). I can use the ActiveWindow.ActivatePrevious command but if I have another workbook open, this one closes instead of the .csv file I opened from the macro. I realise this is probably very basic and I've searched the forums but can't find any identical postings.
View Replies!
View Related
|
TRACKER
