Macro With Unspecified Range

Feb 9, 2009

I've got a task I would like to automate but am not 100% sure on how to best do this. I have used the macro builder, but it always specifies a range of cells for my sorts and subtotals.

Essentially this is my issue, I'm responsible for an inventory report that I have to complete 3 times a week and they insist on using excel, not access. I merge my data, and always have to sort the data a certain way, then I apply subtotals to several lines, and then filter by one column containing the word total so that I can apply my formatting.

My problem is, I use three workbooks, with about 6 worksheets each and have to do it for every worksheet, but when I create a macro it always specifies a range, but the number of rows can change day by day!

Below is a copy of my macro, if you have any tips on how I can make this universal so that I can run one macro and sort, subtotal, and filter at least all sheets in one workbook and that would be great! In the example below I had 12,678 rows of data, but as stated before this changes all the time, and is different for each sheet (one sheet is all units, then each unit has their individual sheet also).

Macro:

Sub SheetTotal()
'
' SheetTotal Macro
' Total Unit Sheet
'
'
Application.CutCopyMode = False
Range("A1").Select
ActiveWorkbook.Worksheets("EAB UNIT TOTALS").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("EAB UNIT TOTALS").Sort.SortFields.Add Key:=Range( _
"D2:D12678"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal.........

View 9 Replies


ADVERTISEMENT

Unspecified Range In Vlookup

Aug 18, 2009

Im looking for a way to make the range in the vlookup below a little more dynamic by using the same kind of idea as ive shown in the MATCH part of the statement.

VLOOKUP(A1,Sheet1!F:Z,match("TEXT",Sheet1!$1:$1,0),false)

Basically i can confirm that 'Z' is my end row, and this remains consistent, however my 'start column' (F) needs to be more flexible as it may not always be column F, either by specifying a column name like using the MATCH function, or using INDIRECT

I was trying to use something like this, but im certain this is not the correct way of doing things (and it doesnt work anyway );

VLOOKUP(A1,Sheet1!(columnletter(column(match("STARTCOL",Sheet1!$1:$1,0)))):Z,match("TEXT",Sheet1!$1:$1,0),false)

COLUMNLETTER is a function to turn the column number back to the letter

View 9 Replies View Related

VBA Pivot Table With Unspecified Number Of Rows?

Mar 21, 2012

I'm new to using VBA and have been recording macros and then trying to edit them where I need to and using Google to get the answers that I need, however I have hit a snag when I create a pivot table using my current macro.

The data sheet that I am creating my Pivot Table from will always have a different number of rows from week to week, but I am unsure of how to code this into my macro. When I recorded this, I used the range A1:S10000, which gives me (Blank) as the last row in my pivot table. change my code so that it only selects the data rows to stop these blanks appearing?

Code:

'Pivot Table DATA Tab to show Call Out Times for all Centres in file
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"DATA!R1C1:R10000C19").CreatePivotTable TableDestination:= _
"PIVOT!R3C1", TableName:="PivotTable1", DefaultVersion:= _

[Code]....

View 5 Replies View Related

Random Unspecified Error On Listbox Clear Method

Oct 24, 2006

On a userform, I have a listbox named "eList" containing Employee Names.
I have 2 other listboxes named "List_eTrained" and "List_eNotTrained" which list the Training completed and Training not yet completed for the selected Employee.

When I click Name items, it updates both listboxes with information (using "AddItem").
Before it updates, I use this code to clear the info listbox items:

TrainingDetails.List_eTrained.Clear
TrainingDetails.List_eNotTrained.Clear

When I run the form and click on various entries, it works as expected. At random though, I get this:

Run-time error '-2147467259 (80004005)':
Unspecified error.
And only this line is highlighted in Debug mode: TrainingDetails.List_eNotTrained.Clear

Sometimes it happens after going through several names, sometimes it happens when clicking the 2nd or 3rd name. It's only happening on the "List_eNotTrained" line though--the other line is fine--which has me really puzzled.

I'm hoping someone else has run into this before and might know what's causing this random, unspecified error.....

View 6 Replies View Related

Macro To Change Range / Action Depending On Worksheet (range To Autofill)

Sep 17, 2013

Currently my Macro should: Turn off any filtersNumber column A from 1 to 1000 (starting in A14)Drags formula from K14-O14 down to last row of data shown in column Athen puts cursor in last empty cell in column B ready for user to enter data

On point 3 - I want the range to be K14-O14 if active worksheet equals "EXCHANGES" but if its on the "VALUATIONS" tab the autofill range should be L14-P14

I have found bits and pieces of macros on the internet and put them together so if my macro below is not the most effective for my needs but here it is in it's current state:

Here is my macro:

Sub AddNewEntry()
'TURNS OFF FILTER IF FINDS ONE ON
Dim wks As Worksheet

[Code]....

View 8 Replies View Related

Autofill In Macro Range Is Constant How Can I Code To Be A Variable Range?

Feb 19, 2010

I am trying to write a macro which will autofill specific columns. The macro will set the range from the start of my autofill to the end of my autofill as a constant range.

The problem I need to get around is the end of my range can always change each time I run the macro. For instance, the first time I run the macro I may only need to autofill from row 4 to row 15. The next time, I may only need to autofill from row 4 to 23 (because of user updates). How can I make the end of my range not be a constant address but variable?

View 6 Replies View Related

If Statement In Macro: Macro To Change A Range Of Cells Colours Based On A Single Cell?

Mar 16, 2007

1st - Need a macro to change a range of cells colours based on a single cell having a value greater than 0.001. ie. cells A1 - G1 need to change to grey based on cell F1 having a value greater than 0.001 entered in it?

2nd - Also a macro for deleting the text contents of cell C1 based on cell F1 having a value greater than 0.001. Therefor if cell F1 has a number greater than 0.001 it changes the colour of celss A1 - G1 and also deletes the text in cell C1?

View 2 Replies View Related

Run Macro When Range Value Not Blank, If Not Run Other Macro First

Oct 10, 2008

I wanted to modify the below macro but don't know how. Could someone help me please? Please see the bold text below.

Dim shortmonth As String
Dim todaysdate As String
Dim enterdate As String
'GET DATE AND CREATE VARIABLES
enterdate = InputBox("Please enter the date in the following format: MMDD")
shortmonth = Left(enterdate, 2)
todaysdate = Mid(enterdate, 3, 2)

Workbooks.Open Filename:= _
"S:CMU Trade VerificationTrade Discrepancy Report " & [shortmonth] & " " & [todaysdate] & ".xls"
Sheets("Daily Archive").Select

***Do this step when when range A2:N2 is not blank, otherwise, run the "Daily Data" macro first and then do the below steps.

View 9 Replies View Related

Macro To Name A Range?

Mar 17, 2009

Im writing a macro to assist me in creating a group of dependent lists. I need the syntax for naming a range with the content of another cell. For example: Cell A1 contains a word. I want to name the cell range B1 through B5 with the word currently populating cell A1. Is there a command I can use?

Right now, starting from A1 I use the copy command to copy the content of cell A1. I then select the range B1 through B5, place the cursor in the NAME BOX and press paste. See my crude macro below which performs everything but pasting the name.

View 2 Replies View Related

Add Range To A Macro

Dec 15, 2009

I have a macro i need to add a range too but not having much luck, i need to add columns D, G, J, M, & P to this code if poss.

View 2 Replies View Related

Macro For Updating A Range?

Apr 14, 2014

I just started learning VBA and I can't figure out how to create a macro that will update my date range. I need it to automatically add the next date in the next blank cell so it would be like this:

1/1/13 1/2/13 1/3/13 1/4/13

Here is an outline of my work book: GANTT4-1.xlsm

Id like a date to be added if the maximum date in row 6 is exceeded by a date in column k.

View 3 Replies View Related

Macro Not To Run In Specific Range

Mar 5, 2014

I am using the below macro to delete row:

HTML Code: 

Sub REMOVE()
ActiveSheet.Unprotect Password:="P@ssw0rd!"
Set Rng = ActiveCell
ActiveCell.EntireRow.Delete
ActiveSheet.Protect Password:="P@ssw0rd!"
End Sub

However I would like not to apply that macro when I am in the row 12:26! btw, I am running my script by pressing CTRL+D.

Last thing would it also be possible to "extend" the exception to another range ? (name manager -> row 84:276 called "NEXT") if possible to define two name manager for the exception to implement to the existing macro.

View 1 Replies View Related

Macro Range Application

Jan 14, 2009

I have a worksheet using several columns and 100 rows. Column B and column C can contain a value "x" in either coulmn but not both in a specific row. The macro illustrated works perfectly for what I need but there must be a way to point the macro to a range of rows rather than using an "if" statement for each row as I've done.

View 5 Replies View Related

Macro To Add Cell Value To A Range

Mar 24, 2009

I have a worksheet with a range of data that will change each month. Columns a,b,c and d hold data. Row 1 is my header row and the last row contains totals.

I need the range of column E to equal the value of Z1, which is declared as the last day of the month.

View 6 Replies View Related

Macro To Rename Tab With Range

Sep 10, 2009

From my research on trying to apply this function, I have peiced together several codes and am unable to get my end result. I want to add a worksheet and rename it with text formula on my main "Macro" sheet from cell D1. The name in cell D1 is a date that changes based on a formula. I can get the macro to add the worksheet but then it stops at renaming it:

Sub NewTab()
'
' NewTab Macro
' Macro recorded 9/10/2009 by andrea_1
'

'
Dim wsNew As Worksheet

Set wsNew = ThisWorkbook.Worksheets.Add
ActiveSheet.Select
ActiveSheet.Name = Worksheets(Macro).Range("D1").Value

End Sub

View 2 Replies View Related

Resize Range - Macro

Oct 20, 2005

Macro to CHANGE number of rows “3” to a variable range
(end.xlDown) on the macro below.

Sub GotToRangeResizeCopyPaste()

Application.Goto Reference:="MyRange"
ActiveCell.Resize(3, 10).Select
Selection.Copy
Range("OtherRange").Select
ActiveSheet.Paste

End Sub

View 11 Replies View Related

Print Range Macro

Jun 10, 2009

setting up a print range macro:

I have a sheet where:

1) column A (rows 1-54) needs to be printed with every printing
2) other columns (b-e) or (f-i) etc need to be printed out with each printing

Is it possible to setup a macro to do this as long as I know which columns need to be printed every time (will be same number of rows every time)

Basically, there is info in column A that needs to be printed every time, then I want to be able to print out a months worth of data (b-e for january) for each printout. It is essentially a calendar that I need to print up a hard copy of.

I know zero about vba and much of what I have found on the internet has only confused me more.

View 14 Replies View Related

Macro To Format Range

Nov 20, 2009

i am getting is it only formats H11:AE12 & H14:AE15, It does not format the first range or any others. Am i doing something wrong? I recorded the first one H8:AE9 then just added the ranges for the others, have i missed something?

View 3 Replies View Related

Run Macro When Click In Range

Apr 12, 2008

I want a macro I have made to run when I click in a particular range.

The macro I want to run is named: SortSizes

I want the macro to run when I click any cell in the range: A12:A4000

with some code and instructions to do this.

View 9 Replies View Related

Dynamic Range In A Macro

Nov 17, 2011

I am trying wihout suceess to create a range to copy in a macro each time the macro loops. This is thus a dynamic range. My failed attempt is listed below. How is this done?

ColumnAdjust=2
For I = 1 To 282
Range ("B5").Select
Range(ActiveCellOffset(0,ColumnAdjust),ActiveCellOffset (0,ColumnAdjust+1))
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
.
.
.
ColumnAdjust= ColumnAdjust=2
Next I

View 4 Replies View Related

Macro To Name Range That Isn't Pre-defined

Mar 2, 2012

I need the macro to create a named range based on a last column and row that can change. It won't change once the report is set up, but since this is a template that will be used for hundreds of projects, it needs to be flexible to accomodate a varying numbers of columns or rows

The starting cell will always be A10, but the ending cell can change depending on how many columns and rows are in the table.

Right now, the code I've created is

Sub NamePivotData()
Dim LCol As Long
Dim LRow As Long
Dim RngEnd As Long

[Code].....

I need it to refer to ='Calendar Setup'!$A$10:$*$** where * is the last column in the table and ** is the last row in the table.

View 4 Replies View Related

If / Then For A Range Of Cells Within Macro

Apr 10, 2012

I am trying to create a macro such that in a given column (AB 0 -100), if the cell entry is "1", then another cell in the same row will be copied (C) and pasted to a third column (F).

All of the entries in column AB are 0 or 1.

So, for example, if AB23 = 1, the contents of C23 are copied, and pasted in to F23.

If AB24 = 0, nothing happens.

Code:

Dim i As Integer
For i = 1 To 100
If Range("AB" & i).Value = 1 Then
Range("C" & i).Select
Selection.Copy

[Code] ...........

View 6 Replies View Related

VBA Macro To Sort Range?

Sep 12, 2012

I am trying to write a macro to sort a range of data by another column. Here's the code that I already have :-

Code:
Public Sub sort_data(sort_range As Range, key_range As Range)
' MsgBox sort_range.Address
' MsgBox key_range.Address
sort_range.Sort Key1:=key_range, Order1:=xlAscending, Header:=xlYes
End Sub

However I get a run time error 1004 stating that the sort reference is not valid.

View 6 Replies View Related

Macro To Hide A Range?

Jan 24, 2004

1. I can create basic macros .. but how do I write a macro to hide a range (of 10 rows) named "dmargin". That for my button no. 1.

2. How do I write a macro to unhide the named range "dmargin" for my button #2.

I tried using VB codes to achieve both conditions -- it works but it either continuously protects or unprotects my ws -- so I think it is better to use a low level codes (macro buttons).

View 5 Replies View Related

Macro To Find Next In A Range

Mar 17, 2014

I have a macro which pairs data.

The macro loops by going Next i in a range.

However if the number comes up twice it will only find it in the first instance, how to find the number on the next line instead of the next different number?

View 4 Replies View Related

Subscript Out Of Range - VBA Macro

Aug 1, 2014

I am getting he Subscript out of range error when trying to run this macro. I have the macro on a macro workbook and I am referencing another spreadsheet.

Sub Dater()
Static Count As Integer
Dim Wb As Workbook
Dim Ws As Worksheet
Dim FileStr As String
Set Wb = Workbooks("Pending Approval by Responsible User")
Set Ws = Worksheets("Pending Approval by Responsible")

View 4 Replies View Related

Formula In A Macro To Sum Same Range All Ws

Jun 5, 2007

I have a workbook that will have a different number of sheets every time. All of the sheets have the same format. I have a summary sheet in which I would like to sum the total of Sheet1 thru the Last sheet in every cell from B12 to B24, then I am going to move on to other columns.

I am getting the same error everytime "Object doesn't support this property or method" and the error is #438.

I have tried different formulas, but none of them are working. Here is my code......

Sub FillSumSht()

Dim wb As Workbook
Dim sSheet As Worksheet
Dim LstShtNm As String
Dim LstSheet As Worksheet
Dim TotalSheets As Long
Dim s As Integer
Dim n As Integer

View 9 Replies View Related

Macro To Copy Specified Range

Jan 24, 2008

I have a workbook and I am looking to write a macro that will do the following in a sheet named Monthly Chart.

Could someone assist me (or let me know if this is possible) in writing a macro to do the following: First search in column C for the last row with a value, then select the range C(last row with value):P(last row with value), then copy that set range into a worksheet named Chart2.

View 9 Replies View Related

Run Macro When Click In Range

Apr 12, 2008

I want a macro I have made to run when I click in a particular range.

The macro I want to run is named: SortSizes

I want the macro to run when I click any cell in the range: A12:A4000

View 9 Replies View Related

Macro To Add Row In Named Range

Dec 30, 2008

Macro to add in a row in a named range, at the bottom of the range. the code i currently add in only to a certain row i need to add into a named range at the bottom.


Sub AddPersonnel()
With Sheets("DOR")
.Unprotect Password:="techedit"
Rows(56).Insert
Rows(55).Copy Destination:=Range("A56")
.Protect Password:="techedit"
End With
End Sub

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved