Hello experts, I have an Excel 2003 workbook, containing several worksheets ('workbook A'). I want to produce a second and separate worbook ('workbook B') with a worksheet containing data derived from one of the sheets in A - though some of the data will need to be modified in the transfer
I was just wondering if someone could recommend an approach please. This is a task that will have to be done repeatedly by an admin user. Ideally I'd do it as an excel macro so that it can be put behind a 'Create Derived Sheet' button or whatever, and can easily be distributed with the original workbook. But I'm a newbie to macros so dont know if possible. Once I'm on the right lines I'll be fine, but just having a clear idea of a sensible and proven approach is eluding me currently. Need to get this sorted, hence points.
The easiest way to get started is to record a macro (Tools\Macro\Record Macro). After you've started recording, perform the steps you need and the macro will write itself. Stop recording when you're done and go to the Visual Basic Editor (Alt+F11) to see your code. If you have any questions on the code or it doesn't do exactly what you want it to do, keep posting, EE is the place for you.
thanks Thomas. Maybe I should add some more info. The source sheet has various columns one of which is a month. I need to be able to prompt the user to specify the month they are interested in, select all the records in the source sheet that are for that month, create a new sheet (preferably in a new workbook), and write (with some transformation) the appropriate records from the source sheet into the new worksheet. Depending on the content of a given record, I need to actually write 2 or more records into the new sheet which give extra info based on some hard-code business rules for a client. Does a macro still cound the way to go? Does it support user-supplied parameters? Thanks in advance
A macro is definitely the way to go, and it does support parameters. However, macro recording doesn't take well to that, meaning that you would first have to record the macro with a fixed input (March 08) for instance, and then convert that into a parameter.
Ideally, with your month being a parameter, I would add a sheet from which to launch the macro, with a drop down box with the choice of month. Do an autofilter with the month as the criteria, copy the filtered data in a new worksheet. For the business rules, where are they stored and how do you pick them?
the business rules are pretty simple and can be totally hard coded. The user doesnt need to know them, and the same rules apply to each record every time Essentially, for a given record if a given column contains one value, then I need to write row X into the new sheet, if it contains another value, then I write row X and row Y. I will look into applying your advice- thanks
Do the autofilter, copy the data, then do a loop on the data to add your business rules. Something like:
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
Dim lastRow As Long
With ActiveSheet
lastRow = .range("A1").CurrentRegion.Rows.Count
For i = 0 To lastRow - 1
If .Cells(lastRow - i, 1).Value = 2 Then
Rows(lastRow - i + 1).Insert
Cells(lastRow - i + 1, 1).Value = 3
End If
Next
End With
