excel vba - copy formula and generate new sheets

Question

if i have a sheet called 'values' with just 1 column with cells such as:

ColumnA --------- emp_id emp_name dept_id

and then a 2nd sheet called 'sql' with 2 columns with cells such as:

ColumnA -------- ="select count(*) from tablex where "&a1&" is null;" ="select count(*) from tablex where length("&a1&") > 10;" ColumnB -------- Sheet for null Sheet for length

What VBA macro code (note i need it in vba as i just want to click one button to generate the sheets) would i need to generate the following two output sheets (each sheet with just one column):

 Sheet for null ------------------- select count(*) from tablex where emp_id is null; select count(*) from tablex where emp_name is null; select count(*) from tablex where dept_id is null; Sheet for length ------------------- select count(*) from tablex where length(emp_id) > 10; select count(*) from tablex where length(emp_name) > 10; select count(*) from tablex where length(dept_id) > 10;

which database do you connect to? (where is tablex). What have you already tried? — JMax
– JMax, Commented Sep 2, 2011 at 15:09
Oracle. Just to get this simple example working tablex is hardcoded but in future i would put the tablename in a cell next to each fieldname. i've tried it just manually copying down cells but i would really like to save time with vba. — toop
– toop, Commented Sep 2, 2011 at 15:13

PaulStock · Accepted Answer · 2011-09-02 16:06:42Z

I think this will do what you are asking.

One change I made is that on the "sql" sheet, I changed the cell contents to look like this:

="select count(*) from tablex where variable is null;"

so I used "variable" instead of "&a1&" like you originally had, and then in the code it replaces this word with the correct value of emp_id, emp_name, etc.

 Sub GenerateSheets() Dim formulaRange As Range Dim formula As String Dim r As Range Dim destloc As Range Dim VariableRange As Range Set formulaRange = Worksheets("sql").Cells(1, 1) ' get each formula Do Until formulaRange.Value = "" For Each r In formulaRange.Rows ' for each formula found, create a new sheet and move it to end Worksheets.Add ActiveSheet.Name = r.Offset(0, 1).Value ActiveSheet.Move after:=Sheets(ActiveWorkbook.Sheets.Count) Set destloc = ActiveSheet.Cells(1, 1) Set VariableRange = Worksheets("values").Cells(1.1) ' Loop through all the variables Do Until VariableRange.Value = "" destloc.Value = Replace(formulaRange.Value, "variable", VariableRange.Value) Set VariableRange = VariableRange.Offset(1, 0) Set destloc = destloc.Offset(1, 0) Loop Next Set formulaRange = formulaRange.Offset(1, 0) Loop End Sub

Collectives™ on Stack Overflow

excel vba - copy formula and generate new sheets

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related