A simple macro request

Gidday Mark,

I do something similar each monday morning....

I just did it once but recorded it in a macro whilst I did it.....

however, it only worked if the three text files are the same names each time (ie they don't change individually).... this could be overcome, I'm sure, with tinkering with the VBA coding if they are different although this is beyond me at the moment.

Also, your only other variable criteria is "value".... whilst recording, if you filter on your value variables it should remember to only paste over those conforming to your filter criteria - however, so that you don't only copy absolute rows based on your first recording, make sure that you have the "relative" button depressed whilst filtering and chopping the neccessary rows, switching it off when finished and moving on to the next step....

it's definitely achievable via recording a macro, just make sure you switch between absolute and relative recording at the correct points

try it out then change some of the original info considerably and run it again to see what it does, that was my brute force method.

*Memo to myself to carry on reading JWalk's VBA for idiots book*

Hope this might get you started in the right direction
Chris

Thanks Chris,
I have pretty much sorted it using vba and the copy row command with the following code:

============================

Public Sub CopyRows()
' Clearing last months data
Sheets("testing").Select
Range("A146").Activate
Selection.ClearContents
' Now start analysing rows
Sheets("TOTAL").Select
' Find the last row of data
FinalRow = Range("A65536").End(xlUp).Row
' Loop through each row
For x = 1 To FinalRow
' Decide if to copy based on column D
ThisValue = Range("B" & x).Value
If ThisValue < 36000000 Then
Range("A" & x & ":B" & x).Copy
Sheets("testing").Select
NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select
ActiveSheet.Paste
Sheets("TOTAL").Select
End If
Next x
End Sub
=======================

And I simply duplicated this for all 3 values. I also included the following - under insert -> name --> define I placed
=testing!$A$1:OFFSET(testing!$A$1,MATCH(9.99999999999999E+307,testing!$A:$A)-2,1)
which sets the appropriate print area.

Still, I do not know that much about recording macros, soI wa swonderinghow one would go about using your technique. i.e.how do I tell it to filter whilst recording?

adieu
thanks again
Mark

Excellent !

hmmm... telling excel to filter whilst recording is sort of the wrong way round to think of it ! You're probably better off thinking of telling it to record whilst you are filtering.

Basically, when you are finding yourself doing certain actions repeatedly, say each hour, or each morning, or whatever, you can usually turn the record macro "on" at which point it records what you are doing

(this can include just about everything you normally do in excel : ie colouring a range of cells, increasing specific column widths, "=clean"ing your text to get rid of unprintable characters, deleting rows blah blah blah oh and filtering)

as it records, it's actually writing those above VBA commands as you do it, rather than you typing them in or pasting them in from examples you've seen - in fact if you minimise both excel and vba windows, I think you can see it writing the code as it does it while you do your stuff.

When you're finished recording your mundane task, you just switch off the recorder.

My filtering point though was that if you are recording a macro and you filter on, say, everything that has a bank account number of 99995 and then delete those rows (ie a result of rows 1 to 10) it will then *always* delete rows 1 to 10 irrespective of your filter criteria the next time you run it.... however, switching to relative mode before you filter and delete while you're recording will tell it to delete the resulting rows of any future filter, not necessarily just rows 1 to 10....

to be honest, I think this is where actually writing your own code in the VB Editor is less prone to errors than having to remember to switch between relative and absolute movements whilst recording a macro

having to remember to always start in cell A5 but then always move to the *end* of any sized range in columnA then go back to cell A1 then move across to the end of the range (however big) on row 1 means switching between relative and absolute 2 or 3 times....

to me, it's easier to write this than it is to think about doing it whilst in macro record mode !

I suspect the VBA guys can explain it a bit more eloquently than me !