I need to apply borders to a certain range. Is there anything I can do to shed some fat (code) off the macro below?
Range("A8:AD100").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
I have a range of data that is added to constently. I named the range "Download" and defined it as =Indirect(Sheet1!B3) where Sheet1!B3 has another formula which produces the range of the data. I want to apply the named range to a pivot table so that when it updates, the range is updated as well but i'm not sure on how to do this. I put the word "download" in the range input of the pivot table wizard but it results in an error and doesn't complete. how can i set the pivot table's range to the named range that I defined?
View 5 Replies View RelatedI got some code from an old discussion thread
Sheets("Reference").Select
Range("d9").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Dim myBorders() As Variant, item As Variant
myBorders = Array(xlEdgeLeft, _
xlEdgeTop, _
xlEdgeBottom, _
xlEdgeRight, _
xlInsideVertical)
This ia a recorded code to draw borders around cells on a given range
I am sure it can be shortened to 1-2 sentences!
Range("J11:O16").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
have a code for restricting printing until some cells are filled; however the code is been applied to all the file. I need to apply it to one one sheet.
Thanks for your help.....
Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet
If Application.WorksheetFunction.CountA(.Range("A11:K11"), ("A13:K13"), ("A16:K16"), ("A19:I19"), ("J18:K18"), ("A22:K22"), ("A25:K25"), ("B63:B64")) < 8 Then
MsgBox "Please Complete Information"
Cancel = True
Else
'Allow printing
End If
End With
End Sub
I have a code that opens a workbook and saves it under one file name and then saves it under 2 different file names, but it keeps on saving them as password protected and I can't work out why. Would it be something to do with the code or something else? The following is the code I am using:
Workbooks.Open "C:BatteriesStore InformationStores" & StoreFile
ActiveWorkbook.SaveAs "C:BatteriesStore InformationOld Stores" _
& StoreFile & " " & Format(Now, "d-mmm-yy"), xlNormal, Application.DisplayAlerts = False
ActiveWorkbook.SaveAs "C:BatteriesStore InformationStores" _
& StoreFile, xlNormal, Application.DisplayAlerts = False
i have a macro code but i don't know how to apply it to all sheets in the same workbook
my code is
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("D5:D100"), Target) Is Nothing Then
Target.Offset(0, 1).Value = Now() [code]....
I have a number of different modules. Each module does something similar, but different to a specific workbook. Instead of running the macro(s) individually is it possible to have a parent-class that calls on each module, and further to have each module *know* which worksheet it should apply to?
Example below:
Code:
Sub Cost_Center_Information_File()
'What it does: applied to a worksheet (there are about 15 worksheets in the workbook) it
'will do some formatting manipulations.
[Code].....
how can i do the following using VBA
making each cell in column A added to each cell in column B and the result will be in the column C
for example
c1=A1+B1
c2=A2+B2
c3=A3+B3
... etc
i know i can do that simply without vba code but I just want to use this method to implement more complex formulas .
I am trying to use vlookup in vba but i receive an error '1004' "Unable to get the VLookup property of the Worksheetfunction Class". I need a way around this. I have ID numbers in the first column and I am trying to input the name of the table it corresponds to in column "O". I have 4 different tables. I am using For Each cell in Selection to go through every cell by using If statement along with VLookup. As soon as the function is forced to go to the next IF selection, it gives me the error.
Range("O3:O" & myCount + 2).Select
For Each cell In Selection
If IsEmpty(cell) Then
If cell.Offset(0, -14) = Application.WorksheetFunction.VLookup(cell.Offset(0, -14), _
Workbooks("Warehouse Inventory"). Sheets("TWGReports").Range("$a$2:$z$1000"), 1, False) Then
cell.Value = "TWG Facility"
If cell.Offset(0, -14) = Application.WorksheetFunction.VLookup(cell.Offset(0, -14), _ .....................
I have a long list of zip codes (this is just a small portion of the list) that I need to make into ranges. Is there a formula I can use?
From this list:
90080
90081
90082
90083
90084
[Code] ......
To this:
To From
9008090084
9008690089
9009190091
9009390099
9010190103
9017490174
9018590185
9018990189
9020190202
9020990213
9022090220
I need my SQL server query to update with names from a range. e.g. pinnog as shown below is a name defined range called Name1 and smithb will be Name 2.
Can someone with me with this code as I am not sur ehow to lookup ranges in VBA.
My code is below ...
I have a column containing ranges of apha-numeric codes that I wish to expand into an adjacent column as the following example:
AL 0-4
AL 0
E 4
AL 1
H 16-23
AL 2
[Code] .....
I would like the VB to distinguish and expand the different range permutations from the first column, leaving single codes intact and separating comma separated codes as shown. If it makes it easier, I manually split out the comma separated codes onto separate rows.
code to have check marks appear in multiple non-adjacent columns in a worksheet by double clicking.
The code I am using works for a single column : (The named range "Checkboxes1" is D4:D100)
VB:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("Checkboxes1")) Is Nothing Then Exit Sub
[Code]......
However I also need the code to work on two other named ranges "Checkboxes2" (E4:E100) and "Checkboxes3" is G4:G100.
Here's a nice easy one for anybody whose used excel for more thaan a week unlike me, I'm using the following code to access a popup calendar:
View 2 Replies View Relatedfollowing code so as to have certain cell ranges protected and unprotected USING COMMANDBUTTON/PASSWORD
Code:
Private Sub CommandButton1_Click()
Dim Password As String
JamesPassword = "MyPassword" '
[Code]......
I am trying to write VBA code that will print a print range that is presented in cell F3 on a "Reports" worksheet. The content of F3 will change depending on how many reports the user selects to print. For example, he could select one, two, three reports etc - up to twelve. The cell ranges of each report are named (e.g. Report1, Report2 etc) so that if the user selects to print Reports 1 and 2, the contents of cell F3 are "Report1,Report2". If I replace WhatToPrint with "Report1,Report2" the print macro works.
Sub Macro2()
Dim WhatToPrint As String
WhatToPrint = Sheets("Reports").Cells(3, 6).Value
'sets the variable to equal the contents of cell D3 which contains the formula
'summarising the print ranges I want to print
Sheets("Reports").Cells(3, 6).Select
ActiveCell.FormulaR1C1 = WhatToPrint
' pastes the variable in cell F3 - just to check that it looks like I want it to
Sheets("Reports").PageSetup.PrintArea = WhatToPrint
'uses the variable to set print area - this is where it fails!
'if you replace the variable with the contents of cell F3 the macro will work
ActiveWindow.SelectedSheets.PrintPreview
End Sub
I am employing code to label dynamic ranges that takes the form of,
ActiveWorkbook.Names.Add Name:="dms", RefersToR1C1:="=OFFSET(DMS!R10C5,0,0, COUNTA(DMS!C5),COUNTA(DMS!R10))"
'dms_j
ActiveWorkbook.Names.Add Name:="dms_j", RefersToR1C1:="=OFFSET(DMS!R11C10,0,0,MATCH("" * "",DMS!C10,-1),1)"
'dms_p
ActiveWorkbook.Names.Add Name:="dms_p", RefersToR1C1:="=OFFSET(DMS!R11C16,0,0,MATCH("" * "",DMS!C16,-1),1)"
'dms_r
ActiveWorkbook.Names.Add Name:="dms_r", RefersToR1C1:="=OFFSET(DMS!R11C18,0,0,MATCH("" * "",DMS!C18,-1),1)"
'dms_t
ActiveWorkbook.Names.Add Name:="dms_t", RefersToR1C1:="=OFFSET(DMS!R11C20,0,0,MATCH("" * "",DMS!C20,-1),1)"
The dynamic ranges are getting entered but the problem is that the range "dms" overshoots by six cells into blank cells at the bottom of the table, and the rest of the ranges overshoot by ten cells into blank cell area. I have deleted, cleared and destroyed everything around the table and re-sized the active area to no avail.
I am trying to clean up my code by using named ranges so that it will still work if/when others add columns or rows to the spreadsheet. These should be pretty easy solutions for most of you but I can't seem to find any answers online.
Here are 2 examples of the code I am trying to update:
I was thinking that replacing "A" with "namedRange" would work but I suppose that's not how the Columns application works. How can I modify the code below to work with a named range instead of the fixed column "A"?
[Code].....
For the following I would have thought that replacing "AU" with "namedRange" would do the trick as that's how things have worked for me in the past using the Range application. Unfortunately I get an error when I make the change.
[Code] .....
I use excel 2007 and have a s/s running to 350,000+ rows.
Data is in ranges of 2 to 30 rows with blank row between each range.
Column F numbers the rows in a range (if row 120,000 begins new range then F1200,000 = 1, F120001 = 2, etc).
If there are 2 rows of data in a range then, including blank row, there are 3 rows between this and next range.
What I want to achieve is a minimum of 6 rows between each range.
Does anyone know code that can can achieve this end?
On the attached sheet I would like to set the print range and then print based on 1 cell's valve. F9 is the value I would like to use:
If F9=1 I need it to print A1:M43
If F9=2 I need it to print A1:M87
If F9=3 I need it to print A1:M131 and so on.
I have a spreadsheet, but it came from another file using the detach sheet method. It has therefore taken with it, all the named ranges that are now superfluous.
It seems a bit long-winded to delete these named ranges one by one manually. Would there be an easy to understand script that would delete them all, so I can start with a "clean sheet".
I'm using Excel 2003.
I'm in the process of combining a lot of worksheets into one and found the code on the following site:
[url]
which almost does what I need. I understand how it works (i think!) but as a vba novice I can't work out how to adapt the code to copy more than one range from the source workbooks.
What I want to be able to do is copy say the following cells from each workbook:
A1
D1
d4:D7
I think what I need to do is call the getdata macro multiple times with different ranges, but I can't quite work out how to do that without simply creating multiple copies of the master file.
I have a spreadsheet that has two different data sources (i.e., A and B). The amounts of these data sources (i.e., the number of columns) varies from sheet to sheet. I have to calculate the averages of these data sources independently and together. Because this data is spread across twenty or more spreadsheets, these calculations can be time-consuming. I want to do the following:
1. Locate the last occurrence of the first data source "A" in row and then insert a column after that cell.
2. In that cell, I want to get the average of each data source for each row of data (i.e., there are always 19 rows of data).
I want to do the same two steps for the second data source "B". Then, I want to insert a column after the "B" average and this column will be used to get an average of each row of data from A and B together. Please keep in mind that the number of data sources for A and B varies from sheet to sheet.
So far, I am working on code to try to " find" the text in a range (i.e., find the last instance of "A"), but I cannot figure out how to get it to get it to stop at the last occurrence and then insert a column. I have some ideas about how to calculate the average, but any of yours are much appreciated.
Also, the row with the type of data (i.e., A or B) is named because this function is part of a larger macro. Therefore, it is relatively easy to get to it.
I have attached a version of the file that displays how I want it to look.
The code I have thus far is:
Cells.Find(What:=A, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
I have a spreadsheet containing data in coloumns A to L.
Where all the codes in column B are the same then a border must be placed around all the codes that are the same i.e the border must start in Column A and end with column L.
provide me with the code the will put borders around the codes in column B that are the same ...
I have created a 4 page chart in landscape with text in each of the 4 columns in the chart.
When I looked at it in print preview the chart did not fill the page and so to widen the last 2 columns I dragged them over to the right hand margin. I am still definitely within the right hand dotted line showing the margin limit.
The problem is that I cannot now put a border line down the extreme right hand column. Every time I click on any of the border instructions in the Font tab nothin appears on the right hand side of the box.
I am trying to write vba code that will highlight the row in the range if a field is over a certain percent. The column number won't change but the number of rows will. I'd also like the code to automatically work on all tabs of the workbook when a button is clicked.
Data Info:
Currently there are 4 tabs, but can have more/less
Columns used are A:O
Data for shading starts at A3 and should go to however many rows have data and ignore blank rows
Formula should be if data in column E is over 10.00% then the data in that row A:O should be shaded in the color off yellow & have black thin orders
If the data in column E is not over 10.00% then the data in that row should not be shaded but still have thin black borders.
Is it possible to stop excel from pasting border formating with cell data other than to select paste special-all except borders?
View 5 Replies View RelatedThe attached file works with 3 Drawers and 3 Doors but if I use 2 Drawers and 2 Doors the Borders do not line up. I think the Drawers are right the Doors appears to be the problem.
Same thing happens with 3 drawers and 4 Doors. It has and extra border on the right side.
See Attached : ozgrid cabinet error.xlsm
Also I would like for the Measurements to be in the cell to the right of the left hand border and center across each of the sections.
Change B3 and B4 to 2 then click draw. 3 and 3 work 3 and 4 don't but 4 and 4 does.???
I want to remove some cell borders for printing so that on paper it will look like 2 separate tables (ie, I want a space between the 2 tables). I have tried removing the borders and changing the colouring to white but they still show.
View 5 Replies View Related