Re: Counting number of dates with specific month
Hi and welcome to the forum.
Another possiblity is to use the AutoFilter with 'Greater than or equal' for 01/06/2012 And 'Less than or equal' for 30/06/2012 and =Subtotal(2,YourRange) for the count of items. You can also then see which items are included.
PS: Nice formula AAE. The -- part, that coerces a boolean to a number right?
Re: Extracting data from a directory of XLSM's
Quote from entilza;616959Found the problem. The line of code needs "set" to proceed the rest of it. ...
Told you to check it - well spotted! 
As regards running the macros, what security level do you have set?
Re: Dynamic .RANGE code (fixed columns, unknown rows) for VBA
Sorry, but even with the example workbook I'm still not clear. For example, you have Q2:Q3 as the criteria range but these cells are blank.
You said that the dynamic part was what you were struggling with. Well, the code above (reproduced below) covers that part. For the remainder I suggest that you use the Macro Recorder to record what you actually want to achieve and then modify the code accordingly. Post back if you are still having problems.
.Range("A2:N" & .Cells(Rows.Count, "N").End(xlUp).Row).AdvancedFilterRe: error 400/runtime error 1004
Hi Kevin,
You can't PasteSpecial after using Cut. Try doing it manually - you will see that the option is not available.
Re: Dynamic .RANGE code (fixed columns, unknown rows) for VBA
OK, would you clarify exactly what you are trying to achieve?
Re: Dynamic .RANGE code (fixed columns, unknown rows) for VBA
Hi,
Only taken a quick look but is this closer to what you want:
Sub Filter()
With Sheets("May 2012")
.Range("A2:N" & .Cells(Rows.Count, "N").End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.Range("Q2:Q3"), CopyToRange:=.Range("A2:F2"), Unique:=False
End With
End SubRe: Macro to export webpage data into the activesheet rather than a new file
Hi,
You've been here long enough to know that you should be using code tags when appropriate. Please edit your post.
Re: Extracting data from a directory of XLSM's
Hi and welcome to the forum (and well done for making such a clear first post).
I've only looked at this quickly as I'm just about to go out (ie check it carefully!) but I would have thought something like this:
Sub Generate_Summary_Figures()
' This procedure reads through a set directory looking for all XLS file varieties. (Expect XLSM).
' It reads the files for specific field locations, then rolls the data into a sheet
' for the purposes of reporting figures.
Dim myDir As String, fn As String
Dim currentrow As Long
Dim wbkTarget As Workbook
Dim wsDest As Worksheet, wsTarget As Worksheet
' Set the directory of XLSM Assessments to be scanned
myDir = "K:\your\source\directory\"
' Set the file type on the end of the directory
fn = Dir(myDir & "*.xls*")
' Set the starting row for output of raw data lifted from the directory of assessments
currentrow = 6
wsDest = ThisWorkbook.Worksheets("Sheet1")
'the worksheet that receives the data - amend name to suit
Application.ScreenUpdating = False
Do While fn <> ""
' Read the data in from each source file
Set wbkTarget = Workbooks.Open(myDir & fn)
Set wsTarget = wbkTarget.Worksheets("Field Assessment")
With wsTarget
wsDest.Cells(currentrow, 1).Value = .Range("E3").Value
wsDest.Cells(currentrow, 2).Value = .Range("E4").Value
wsDest.Cells(currentrow, 3).Value = .Range("E54").Value
End With
wbkTarget.Close False
fn = Dir()
' Increment the row number for the next line
currentrow = currentrow + 1
Loop
Application.ScreenUpdating = True
End SubRe: Run-time error '91': Object variable or With block variable on the following.
Hi and welcome to the forum.
Please remember to use code tags when posting code to the forum (as you agreed to do when joining). I've added them for you this time.
Re: Macro copy first row on sheet and paste everything on active row.
Hi,
Perhaps I'm missing something but isn't it just a matter of combining the two routines that you have like this:
Sub CopyrowCombined()
Rows("1:1").Copy
ActiveCell.EntireRow.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End SubRe: Filter out all words ending with 's' (plurals)
I've got 2003 and can't see that feature. How about insertuing a column and entering the formula =RIGHT(A1,1)="s". Copy down and then filter on True or False as needed.
Re: Macro button delete
Hi and welcome to the forum.
Please remember to always use code tgas when posting code to the forum (as you agreed to do when you joined). I've added them for you this time.
Re: Macro copy first row on sheet and paste everything on active row.
Hi and welcome to the forum.
My advice, because it is something that will help you often in your future coding, would be to use the Macro Recorder to record your actions. This should give you the code that you need (even if you have to tidy it up a little afterwards).
Re: Uploading a spreadsheet onto another Exc. Spreasheet Automatically?
Hi,
Yes, this is possible. I'm not sure that I understand what you want to do with the data in csv file so you will have to add some further code but the general structure could be something like this:
Sub Import_Data()
Dim wsData As Worksheet
Const strSource = "C:\Documents and Settings\domgilberto\My Documents\data-summary.csv"
'amend path as appropriate
Dim wbkImport As Workbook
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
'speed up the import process
Set wsData = ThisWorkbook.Worksheets("statement(1)")
'the sheet that we are updating
With wsData
If Dir(strSource) = vbNullString Then
MsgBox "Data summary has not been downloaded to expected directory.", vbCritical + vbOKOnly
GoTo TheEnd
End If
Set wbkImport = Workbooks.Open(strSource)
'check whether data file downloaded, if so open
'
'do whatever you want to do with the data here
'
wbkImport.Close SaveChanges:=False
'Get data from file then close it
End With
TheEnd:
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
'reset settings
End SubRe: Custom message with mouseover
I do like post #6 on the other forum.
Re: Can't Find/Replace values from a column with Macro
Hi and welcome to the forum.
Please remember to use code tags when posting code to the forum (as you agreed to do when you joined). I've added them for you this time.
Re: Looping solver not working
Hi and welcome to the forum.
Please remember to use code tags when posting code to the forum (as you agreed to do when you joined). I've done it for you this time.
As regards your question, see if the information here helps : http://peltiertech.com/Excel/SolverVBA.html (in particular the section 'Preparing Solver for First Use')
Re: For Loop/While Loop to copy and paste workbooks into worksheets
Hi,
One way of doing this would be to make an array of the names that you want to work with. Something like this, for example.
Sub Main()
Dim vWbklist As Variant
Dim lLoop As Long
vWbklist = Array("emp.csv", "sal.csv", "report.csv", "band.xls")
For lLoop = LBound(vWbklist) To UBound(vWbklist)
MsgBox vWbklist(lLoop)
'replace this with your code
Next lLoop
End SubYou can either call your existing routine and pass the name as an argument or simply incorporate the loop within the existing routine.
Re: Using a Placeholder / Wildcard When Referencing a Filename VBA
Hi. PLease use code tags when posting code to the forum (I've fixed it for you this time).
As regards your proble, have you tried using a Workbook variable when opening the workbooks in question? This will make your referencing a lot easier.
Re: Workbook_WindowDeactivate not triggered when changing applications
Hi Aidan,
I haven't actually done what you are trying to achieve but it strikes me that if it is possible it's likely to be by use of Application-level events. Are you familiar with those? There is plenty of info on the Web but two good starting points would be:
http://cpearson.com/excel/AppEvent.aspx