Limitations of Macro Recording

[sabbathstevie]

Hi everyone,

Hopefully someone can help me with an issue I have with macro recording.

I'd like to record a macro which:

1: copies the text string in a cell
2. filters a list based on that string
3. opens a pre-created workbook whose filename is based on the original string
4. copies the contents of the filtered list into the newly-opened workbook
5. closes and saves the newly opened workbook.

The problem I have is that, after recording the macro and then running it, it follows all the steps above but based on the text string that was entered in the cell the first time I recorded the macro, rather than the updated contents.

Quite difficult to explain the true nature of what I'm looking for here but basically does anyone know who a macro can be used to

1. Copy text from a cell
2. paste that text into a box (such as "custom filter", "open" or "saveas") to perform these functions based on the contents of that first cell?

Many, many thanks to anyone who can help.

 

[Andrew Poulsom]

Welcome to the Board.

Are you using all the text in the cell? If so you can use that cell's value like this:

Selection.AutoFilter Field:=1, Criteria1:=Range("A1").Value

Change the Range reference to suit.

 

[sabbathstevie]

Andrew,

Many thanks for your prompt response. Yes, I am using all the text in the cell - as a relative newcomer to macro's, presumably I can record the macro in the normal way and then edit the code as per your instructions.

That effectively deals with one half of my queury - filtering the list. The next part is more tricky - I need the macro to go on and open another workbook with a filename the same as the text in a cell, so that the filtered data can be copied into that workbook, saved and closed.

Ideally, the macro would also copy the opnly sheet (a template) that exists in that new workbook and rename that sheet with the contents of another cell.

Any thoughts?

 

[Andrew Poulsom]

To open a workbook with the name in A1, eg:

Workbooks.Open("C:\Temp\" & Range("A1").Value & "*.xls"

 

[sabbathstevie]

Again, excellent information.

Apologies for banging on about this but can the same principle be used for:

1) renaming a worksheet
and
2) saving a workbook

And can it be referenced to a cell located in a different workbook? How is that code arranged?

Regards,
Stevie

 

[Andrew Poulsom]

If the cell is in a different workbook it can be referenced like this:

Workbooks("Book1.xls").Worksheets("Sheet1").Range("A1").Value

If you assign that to a variable, that variable can be used anywhere in your code. Example:

Dim strName as String
strName = Workbooks("Book1.xls").Worksheets("Sheet1").Range("A1").Value
ThisWorkbook.Worksheets("Sheet1").Name = strName
ThisWorkbook.SaveAs ThisWorkbook.Path & Application.PathSeparator & strName & ".xls"

 

[sabbathstevie]

Thanks again for all your help with this. I'm now trying to use a macro which searches for cells based on whats written in another cell - I've followed your advice with regards to referencing the workbook/sheet/cell but it doesn't seem to like this line of code - is it obvious where I'm going wrong?

Cells.Find(What:=Workbooks("EBT Commissions 03.04.10 - 27.06.10").Worksheets("Employee Maintenance").Range("D24").Value, After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

 

[Andrew Poulsom]

What error do you get on that line of code?