I have attached a spreadsheet...I'm trying to select Range A1:N16 using VBA code...
Range(activecell,activecell.end(xltoright)).select
Range(selection,selection.end(xldown)).select
When I use this code is only gets A1:N6...: is there anyway to get it to select the range all the way to the end of the data. I tried CurrentRegion and some other stuff but can't get it to work.: The range may go upto line 500 or 3 I just never know.
I have the following code that Ger Plante very kindly helped me with which, depending on whether there is an 'x' by someone's name in a list, creates a new workbook, copies some information to it and saves it before moving on to the next 'x'. Loop Through Rows & Copy Each Row To New Workbook
For lLoop = 2 To 251 'first row of data to last row.
If ws1.Cells(lLoop, 4).Value = "x" Then '4 = Column D
ws1.Activate
ws1.Range("e" & lLoop & ":g" & lLoop).Copy
ws1.Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Rng1.Copy
Workbooks.Add
ActiveSheet.Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.Range("A1").Select
ActiveSheet.SaveAs varPath & "Student Data Files" & ActiveSheet.Range("B1") & ".xls"
ActiveWorkbook.Close
Else
End If
Next lLoop
how I can modify the code such that any cells in the range "b1:b504" in Sheet1 of the the new workbook can't be selected or edited without a password....I have tried unsuccessfully using Protect but am not sure how to get vba to set it to specific cells and determine exactly what is allowed in those cells.
I am writing a equation in if statement...Is there a procedure to select the the second and third letter from a sentence and write a condition based on that...
I mean, say if I had 1XY in cell A1 and I want to write a if condition based on the XY from 1XY term, how can i do that?
Use a drop down menu (Combo box for example) to look at all the sheet names (these will be names of people, sheets added all the time) be able to select a name which when selected takes me to that sheet. So the amount of sheets will increase so everything will need to be dynamic.
I dont want to see all the list behind the drop down so that why i was thinking of a combo box.
What code to use to tell VB to select the "search" button on webpage "http://66.179.23.21/DP1/Metroplex/FairfaxCounty/permit/WIZ_INSPECTION.asp"?
View 9 Replies View RelatedSub test()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lastrow As Long
Dim newRow As Long
Set ws1 = Sheets("SUMMARY")
Set ws2 = Sheets("WIP")
Application.ScreenUpdating = False
With ws2
Range("A2:C2").Select
Range(Selection, Selection.End(xlDown)).Select ' Clear
End With
x = 10
lastrow = ws1.Range("C5").End(xlUp)
Do
The code is run from ws1. I put in the .select for now because I don't want to loose my info on ws1.
I trying to clear the range A2:C2 all the way down to where info is before doing the Do Loop which will repopulate the same range from ws1.
If the rest of the Do Loop code is needed, let me know.
To initialize some cells/ranges, I am copying a given range and pasting it to another given range using the. Copy and .PaseSpecial methods. However, it would seem that both methods actually select the range(s) for the operations, i.e. the given ranges(s) are activated/selected thus changing the focus on the spreadsheet. I would like to perform both operations without actually selecting the given ranges.
View 5 Replies View RelatedI am receiving a 'subscript out of range' error on the lines of code below.
I would note that all variables are declared and all seem meaningful as regards what you would expect at that point.
Below is a snippet from the immediate window which indicates what the values are:
completecashname C:CashDevelopmentMyFolderoutputCASH042706.xls
cashsheetname Formatted Sheet
cashcurrcolumn A
cashfirstrow 2
cashlastrow 876
Also the workbooks are both closed at this point (but it makes no difference)
Set CashCopy = Workbooks(CompleteCashName).Sheets(CashSheetName). _
Range(CashCurrColumn & Cashfirstrow & ":K" & Cashlastrow).Value
Set PelPaste = Workbooks(completepelname).Sheets(PELSheetName). _
Range((PELCurrColumn & PELlastrow)).Value
know the VBA script to select only specific cells within a worksheet. To be more precise, on sheet 1 - A2, A6 and A10 cells are filled with red color. I want only those cells which are highlighted in red to be selected, copied and pasted to sheet2.
View 3 Replies View RelatedI am trying to select a block of data from a remote worksheet but I keep on getting the following error
Run time error '1004':
Application-defined or object-defined error.
I have attached the file and I am working on worksheet 21 "PT Monthly Report 1"
What i'm trying to do is select a range based on the date which happens to be in the first column of the range.
For example, I have a column (A) that contains dates and column (B) that contains names.
Its easy enough to select the entire range using
Range("A1:B4").Select
I need someting that will analyse column A and conditionally selct the range based on the date in column A (ie. it will only select the rows where column A has todays date).
I have a dropdown list in C24:C50 (=CategoryList) with data validation and a sub list in D24:D50 (=ItemList) with data validation. I am looking for a way to have code automatically run after selecting an item in the data validation dropdown list in column C.
Example; I click on C24 and make a selection. I what it to trigger code that would move me to D24 and open up the data validation list in D24. After the selection in D24 I would like it to move me back and down 1 row to C25. I have not found anything directly related to this but I have found that code can be run after a selection in a valadition list.
So in my code I have two set ranges and want to select from one set range to another. The code I have now does not work because I cannot select Range("POBRF:PO"). This is the code.
[CODE]
ARow = Range("A" & Rows.Count).End(xlUp).Row
Set POBRF = Range("A1:A" & ARow)
BRow = Range("I" & Rows.Count).End(xlUp).Row
Set PO = Range("I1:I" & BRow)
Range("POBRF:PO").Select
[CODE/]
I am having trouble selecting a range in my data to copy to another worksheet. I have declared two variables (startcell and endcell), as seen below, as strings:
Cells.Find(What:=startdate, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
startcell = ActiveCell.Address
Cells.Find(What:=enddate, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
endcell = ActiveCell.Address
After this I would like VBA to copy all the variables in a range that is bounded by startcell and endcell. Here is what my code is currently for selecting the range:
Range("A2:" & endcell).Select
Instead of A2, I would like to select startcell, but I cannot figure out how. Currently I can get VBA to recognise endcell. For example, using the formula above I can copy the range from A2 to endcell. However, when I try and use startcell as the beginning of the range (in place of A2) the code does not work.
I have two tables in two different worksheets. In the first table, rows get deleted if the value in column 'S' (the first row to the right of the table) is not '1', and columns get deleted if the value in row '2' (third row above the table) is not '1'. In the second table, the number of columns stays always the same but rows get deleted if the value in column 'N' (first column to the right of the table) is not '1'.
After the rows and columns are deleted the tables get pasted in a ppt presentation.
Now the problem: I am a beginner in VBA and up til now I am only able to define a certain Range, e.g. ("A5:M17"), to be copied and pasted into ppt. But since the tables will sometimes be bigger than other times depending if rows/columns are deleted, I would rather define a dynamic Range. The top left cell of both tabels to be copied is always 'A5' but the bottom right cell changes.
I am trying to select a range of numbers based on an active cell. I then want to sum those numbers and have that total reported to a specific cell.
For example:
Say I have a column with a list of dates (Jan/04 thru July/06). I want the user to be able to click on any given field and have that field plus the 11 fields above it summed and reported. Any help?
I came up with something like
ActiveCell.Resize(12, 1).Select
This only selects the data from the active cell and goes down...I need the opposite. I need it to select the 11 cells above the active cell (plus the active cell) and sum that data.
I have a spreadsheet with Employee details and each employee has a Indicator and a Score.
I'm battling to get Excel to do a lookup on the Indicator, find the applicable Score Range, see if the individual's Score falls within the range and then inserts the relevant % into a cell in a specific row of each employee.
If we did not have more than 2000 doing it manualy might have been an option. Also, the Percentages and Score Ranges in the matrix might change a couple of times....
I am having a few problems selecting a range. What I am trying to do is select a range where the number of columns stay the same, but the rows are variable; ranging from one row upwards. At the end of the rows, there is a blank row. I currently have;
View 2 Replies View RelatedI am trying to select a range of cells. The range I need to select starts in Cell A1. I need to select every row with data in column 1 up to the first row with no data. I then need to expand the range to all columns up the last column with data, including columns with blank cells.
The problem with my code relates to the selection of columns. It is only selecting up to column F. Column G is empty, Column H has data, Columns I-J are empty, Columns K-R have data.
I want to copy a range where the row is variable (i in the Loop I'm doing) but the columns stay the same. What am I doing wrong?
Workbooks("Labor Info").Sheets("Transfer").Range("A" & i & ":E" & i).Select
I was trying to select all of the pictures/objects in a certain range (A294:L1400) in order to move them all at the same time. How do I do this?
View 1 Replies View RelatedHow would I select a range using variables? For example, say I wanted to say
myRange = Sheets(1).Range("A1:A100"),
but the column was variable. How would I do this if the column number was stored in a variable?
I need to select starting with:.
Columns A-O
Rows 1 - ( LR of ColB - Col A is empty )
I'm trying to obtain a formula that allows me to select the last 20 numbers in a range of numbers that continue to increase weekly. i.e column a3-a3000 increases by one number every week. I need the formula to sum the lowest 10 numbers of the last 20. If there is 30 numbers from a13-a33 week one. I require the formula to pick up the 10 lowest from the last 20 (a13-a33). Week two the cell increases by on to a34. The formula would then have to pick up the lowest 10 from the last 20 (a14-a44) and so on.
View 9 Replies View RelatedI'm trying to select a discontinous range, lets say B2:B30 & D2:D30 and this works fine using the code below:
Range("B2:B30,D2:D30").Select
However if I use the code below, B2:D30 is selected including column C.
Dim LastRow As Integer
LastRow = Range("a65536").End(xlUp).row
Range("B2:B" & LastRow, "D2:D" & LastRow).Select
I'm trying to write a formatting macro that will insert a gray divider (row) into a blank line. I'm going to do this with a keyboard command, but I'm having a hard time selecting to column AJ. Obviously this command...
Range(Selection, Selection.End(xlToRight)).Select
...takes you to the end of the page, but I don't need to go that far, just to column AJ.
I have a spreadsheet where I need to perform a calculation on the lowest 10 of the 20 most recent entries. Currently I am just deleting the oldest entry and putting in the new data but I now desire to keep all my data.
How do I select the new range of 20 rows and then tell it to take the 10 lowest values to perform the calculation
I have 2 worksheets where I need to transfer a certain amount of data from one to another depending on dates.
Sheet ‘A’, is the main data source. Col A holds all 365 dates from 1 Jan to 31 Dec(starting at A2), and the subsequent data for each day is held in cols B to P.
Sheet ‘B’ is the destination where the user will specify a key date in cell A3, then in A4 to A32, formulae populate the next 29 dates (ie so there is a continuous run of 30 days).
What I need to do, is to identify the same 30 day range from sheet B on sheet A, then to copy the data in Cols B to P for that range back onto sheet B (pasting in at D3)
Question 1) Is there a way that whenever I select Cell B39 that it will copy cells B39:V39 ? From there I can manually paste that selection to where I need to.
If this is possible, it would save the effort of having to select B39:V39 manually and clicking CTRL-C. I am gathering data and the copying and pasting is killing me.
Question 2) Is it possible that when I click on Cell A9 that it can paste any data that I just copied from another worksheet to cells A9:D29? The size of the selection that I would copy is the same size as A9:D29 (4 columns & 29 rows).
I have searched for a solution, but I am not having much luck since I am anaware of the proper terminology for these types of actions.
how (in VBA) to select a range within a column where all values are the same?
For example, given the following:
A B
1 ID Value
2 1 A
3 2 A
4 3 A
5 4 B
6 5 B
7 6 C
How would I select range(B2:B4) where all values = A?
