Scroll Area Not Saving When Workbook Closed
Nov 15, 2006
Ive tried disabling scroll on sheet 1 of my workbook by entering the cell range into the properties window in VBA. However when I save the sheet and reopen it, it seems to reset and are able to scroll again.
I tried using the following:
Private Sub Worksheet_Activate()
Worksheets(1).ScrollArea = "A1:Q51"
End Sub
and a few variations of the above but this dosnt seem to be working. The only other code I have in the workbook is under "thisworkboko" which is this:
Private Sub Workbook_Open()
Application.DisplayFullScreen = True
End Sub
View 5 Replies
ADVERTISEMENT
Jun 27, 2012
I use all of column A, B, and C, but i only use D1 to H44 other than that. Is it possible to hide everything to the right of Column I and below D44:H44, but leave the scroll area for columns A B C able to scroll to the bottom?
View 6 Replies
View Related
Apr 3, 2009
i get an excel file with dynamic data everyday. is there away i can use a macro on this file to store this dynamic data to a specfic closed workbook and add the data onto the old data in that workbook?
View 4 Replies
View Related
Nov 15, 2012
I have the macro that is supposed to save to the next empty row to a closed workbook in a shared network drive. But I am getting an error saying that Excel cannot open the workbook.
Code:
Private Sub CommandButton5_Click()
Workbooks.Open Filename:="phgbsorgad03.ph.gbsorg.netmembersmigsCall_Form_Dbase.xlsm"
Sheets("Database").Activate
Application.ScreenUpdating = False
[Code] ........
View 2 Replies
View Related
Oct 29, 2007
I set the scroll area on a worksheet and saved the file. The next time I open the worksheet the scroll area is no longer set.
View 7 Replies
View Related
Sep 14, 2013
I have a spreadsheet form with a bunch of cells receiving and writing data via vba. On the form is a search box. The data is on the same sheet as the form to prevent flickering by activating another sheet. The problem is that I cannot select the search area and set the scrollarea or the "Find" quits after the first find:
if I have 2 jacks then it will find the first and not the second.
If I don't set the scrollarea of course it goes to the selected cell and moves the form out of the picture.
If I select a cell in the form area after the search, the "Find" is reset to the beginning and locates the same cell again.
Here is the code:
Code:
On Error GoTo DataNotFound
Cells.find(What:=txtFindText, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate
[Code]....
View 9 Replies
View Related
Feb 28, 2007
Can a scroll area be locked at any size using VBA code? I am able to lock area A1:J20 in the Private Module of a worksheet but nothing larger.
View 6 Replies
View Related
Jun 16, 2008
I want users to be able to scroll top-down or sideways up to a certain point only.
What is the VBA code to accomplish this? Let's say for example I want to limit scrolling from A to L rows 1 to 50 only.
View 2 Replies
View Related
Apr 21, 2007
I have a worksheet that serves as a navigation page for the workbook and use the following code to restrict from scrolling off the viewable content. The problem is that the code doesn't kick in until either a click if the mouse or keyboard input. This initially leaves the worksheet open for scrolling until input is received.
How do I make this code activate when the wb is loaded? Code in the Thisworkbook module already makes the menu the visible sheet, and code limiting the scroll area is in the sheet module.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveSheet
.ScrollArea = "A1:L11"
End With
Range("A1").Activate
End Sub
I've also tried using "Range("A1").Select", but it doesn't work either. How can I get this to work correctly?
View 2 Replies
View Related
Apr 26, 2008
Is there code that will take certain data from one Excel sheet to another named file in a different place on the network? Example Copy cell aa47 from "Recent Faxes.xls" that sits in "correspondence" folder. Then paste into cell B25 "Current Documentation.xls" in the "Sales Contacts" folder
View 2 Replies
View Related
Feb 27, 2014
I've been using the following code to bring in individual cell values from one closed workbook to an active one. I would like to modify this is possible to bring in multiple cells at once and also pull them into a different worksheet in the active workbook. Basically, my command button is on Sheet1 but I'd like the data to pull into a cell on Sheet2.
Private Sub CommandButton1_Click()
With Range("Q9")
.Formula = "='C:Users[Workbook Name.xlsm]Worksheet Name'! N27"
.Value = .Value
End With
View 6 Replies
View Related
Aug 21, 2014
I have an open workbook (A) and this is where the code should reside. I want to use VBA to copy the content of an entire worksheet from a closed workbook (B) to an existing worksheet in workbook A. How would you accomplish this?
View 7 Replies
View Related
Feb 6, 2013
I have some vba that opens a closed workbook, copies data from a named range and then pastes it to the active workbook.
However, what is happening is that the closed workbook is opened and only part of the data is pasted. What I would prefer to happen is this:
Open the closed workbook-->copy the named range-->paste(append) to next empty cell in column B.
Heres the code that I have got.
Sub Workbook_test()Dim wb As Workbook
Application.ScreenUpdating = False ' turn off the screen updating
Set wb = Workbooks.Open("G:WAREHOUSEPlanningSmartNew Training Plan raining plan.xls", True, True)
[Code]....
View 5 Replies
View Related
May 27, 2014
Please see attached files.
Book1.xlsx
test.xlsm
I am trying to copy the emf image object from "Book1" into the "test" workbook whilst the test workbook is open and Book1 is closed. The code I have put together currently sort of works, I mean that it copies the text data over but does not copy the object which is what I require.
View 2 Replies
View Related
Dec 16, 2008
im looking for some code to transfer cells a3,d6,f9,i6,k10 and i18 from open workbook named "hello"
to
closed workbook named "goodbye" and input to next available row
a3 to a1
d6 to a2
f9 to a3
i6 to a4
k10 to a5
i18 to a6
View 14 Replies
View Related
Jan 4, 2007
I have a spreadsheet which contains about 800 rows. Each row contains a column with the name of a different workbook. I need to scroll through each cell containing the name of a workbook and open that workbook, extract two specific cell contents from each workbook opened, and paste that information into the two cells next to the original cell containing the workbook name.
Example:
Summary Workbook column C: contains names of workbooks.
For each workbook name, I need to open the respective workbook.
Then in the first tab of the workbook I need to extract the information in E6 and K7 and copy that information back into the summary workbook in the two cell to the right of column C.
View 11 Replies
View Related
May 30, 2014
Trying to create a new workbook from another open workbook, then copying all the sheets that aren't called "Summary" to that new open workbook and then saving it. I get a subscript error on this line:
[Code]....
View 3 Replies
View Related
Jan 31, 2007
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 9 Replies
View Related
Apr 25, 2007
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 4 Replies
View Related
Nov 24, 2007
I want to open an excel file whose name is a value in a spreadsheet, and then pull a value from the spreadsheet I queried into my existing workbook. Here is the syntax i have so far:
to note: I'm calling the workbook in which the macro is located testproject.xls, and I'm using [integer].xls as my list of files that the macro will query. Ultimately, this code will go in a loop.
Set currentCell = Worksheets("Sheet1").Range("A1")
varCellvalue = currentCell.value
Workbooks.Open "path_of_file" & varCellvalue & ".xls"
Windows("testproject.xls").Activate
currentCell.Offset.(0,1).Select
ActiveCell.FormulaR1C1 = "=[3.xls]Sheet1!R1C1"
The problem is, I want [3.xls] to carry the sale value as varCellvalue with .xls appended, and not be kept static at 3. I tried inserting & varCellvalue & ".xls" into the brackets, but with no luck. My only difficulty is getting [3.xls] to vary along with varCellvalue.
View 6 Replies
View Related
Oct 11, 2012
I currently have one spread sheet. At the top of that spread sheet there is an object that hyperlinks to cell A180. When i click that hyperlink, it take me to cell 180, but it appears at the bottom of the spreadsheet, rather than bringing it all the way up to the top of my current viewable screen.
View 3 Replies
View Related
May 11, 2007
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 3 Replies
View Related
Feb 21, 2014
i have a workbook blank template for my colleagues to fill in, they fill in this blank template and save it under the serial number of the item they are creating. However a lot of errors exist when they input the serial number inside the workbook where the serial number is a duplicate of a former serial number.
Anyhow i was wondering is there any way when they input the serial number into the worksheet that i could get it to save in a separate workbook and then have conditional formatting to check those serial numbers in the workbook to see if they match?
View 4 Replies
View Related
Jan 1, 2009
I was wondering if there's any way to sum inside a closed workbook by usin' VBA? I think I figured out how to do it just with formulas, but I was hoping to be able to make like a macro or something to do it, 'cause its kind of time consuming having to go back and forth to all these workbooks.
View 9 Replies
View Related
Feb 22, 2008
Is there a formula or vba code that would allow me to get the max value from a column in a closed file/workbook?
View 4 Replies
View Related
Apr 6, 2013
I have created a spreadsheet some time ago and have been asked to improve on it but I'm rusty with VBA.
I have an automated ordering system that saves each sent order as the date e.g "05-04-2013.xls" but the management team want a graph with the data for the last 4 weeks compared. I have created a seperate workbook called "consumables report.xls" which has a column with the products listed followed by columns "Quantity" and "cost" which is repeated for the 4 weeks of the month.
I want to add a button to prompt the user to choose the saved order e.g "05-04-2013.xls" (all orders saved in same directory) to copy and paste the quantity and cost columns (c8,D69) into "consumables report.xls". I got this to work earlier but it would only paste the formulas and not the values. So I need
A prompt to open workbook
Copy range (c8,d69)
Close work sheet
Paste special .value (c8,D69)
I dont care if it has to open the workbook to copy the data as this will only be used once a month so it dosnt matter how slow the code is.
week 1 week 2 week 3 week 4
Product
quantity
cost
quantity
cost
cost
quantity
cost
1
2
3
4
5
6
7
8
View 7 Replies
View Related
Apr 6, 2014
i want to open closed workbook
here is path of my workbook D:ExcelPurchasesPurchases Transactions With StockItemsVouchers - Purchase Transactions With StockItems.xlsm
i want to set this path as default
View 4 Replies
View Related
Feb 20, 2009
I am trying to use this code to pull data from a closed workbook. The code will be in workbook Book1 and I will be pulling information from closed workbook Book3. I need to pull data from cells A1:A4 from Book3 and place it into Book1 on Sheet2 in cells A1:A4. I receive an "Subscript out of range error" on this line of code
View 4 Replies
View Related
Apr 2, 2009
I display a message box in Workbook before Close event.
If user click yes I shouldnt close the workbook if NO i should close the workbook.
In the Yes part, How can I stop excel from closing the workbook.
If i dont write any ocde it automatically closes in the event.
View 2 Replies
View Related
Mar 31, 2007
I have the following formula to extract a specific cell from a closed workbook. It works fine. I want to be able to make the file name refer to a another cell so I can create a spinner to change it. For example:
='C:Documents and SettingsTom Desktoplabor[01_032407.xls]Stats'!A4
Cell A1 would be 01_032407, and I would replace [01_032407] with [A1]
but it does not work.
View 9 Replies
View Related
