Need help creating script for MS Excel 1

[silkpurse]

I have a spreadsheet of roughly 165 entries. Future versions could be smaller or larger. I have created a form on a second tab in which I have placed formulas in 9 fields that populate those fields with data from specific cells on the master spreadsheet. There are no calculations in any of the fields, simply instructions to pull the data found in the related fields (i.e. "=Master!Q3").

I need a script that will do the following:

1. Save the initial version of the form to a specified folder using the Street and Town fields as the filename.
2. Print the initial version of the form.
3. Update the form with data from the next row that is populated in the Address column.
4. Save the second version of the form as in Step 1, to the same folder.
5. Print the second version of the form.
6. Repeat steps 3-5 until all rows populated in the Address column have been saved and printed.

Here is a portion of the macro I wrote. It just repeats from here, increasing the find and replace values by one till it ends. I don't know how to write it in such a way as to tell it to "n + 1", so it was all done manually. My test macro ran 6 lines only.

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
Cells.Find(What:="=Master!E3", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Replace What:="=Master!E3", Replacement:="=Master!E4", LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Find(What:="=Master!F3", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Replace What:="=Master!F3", Replacement:="=Master!F4", LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Find(What:="=Master!J3", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Replace What:="=Master!J3", Replacement:="=Master!J4", LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Find(What:="=Master!K3", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Replace What:="=Master!K3", Replacement:="=Master!K4", LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Find(What:="=Master!S3", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Replace What:="=Master!S3", Replacement:="=Master!S4", LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Find(What:="=Master!R3", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Replace What:="=Master!R3", Replacement:="=Master!R4", LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Find(What:="=Master!L3", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Replace What:="=Master!L3", Replacement:="=Master!L4", LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Find(What:="=Master!Q3", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Replace What:="=Master!Q3", Replacement:="=Master!Q4", LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Find(What:="=Master!AI3", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Replace What:="=Master!AI3", Replacement:="=Master!AI4", LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False

I've attached a dummy version of the spreadsheet. It is limited to twenty lines of data, but should be enough to work with. And, of course, the names have been changed to protect the innocent.

That's pretty much it. I have no idea how to get the to save the form to a specified folder using a different filename for each form. And, of course, writing a macro that performs that task for 165 lines will be tedious, even using copy/paste, so I’m hoping a script can manage that much more elegantly.

Thanks,
Henry

 

[SkipVought]

Hi,

Thanks for reposting here and for providing the code you have generated.

There’s a much easier way using a totally different approch.

Add another sheet to use for reference information. I called my sheet Factors. On the Factors sheet, I entered this value in...
[tt]
A1: CurrentRow
[/tt]
1) SELECT A1:B1
2) Formulas > Defined Names > Crteate from Selection > Creat names from the values in the Left column

Now, if you select B1, you will notice that the value in the Name Box, that usually has A1 type references, has CurrentRow. And if yor are on some other sheet and enter CurrentRow in the Name Box, it will take you to Sheet Factors cell B1.

Start off by entering 3 in B1 on the Factors sheet.

You mission, if you choose to accept it, is to change each of the 9 formulas on your Data Form sheet to this format...
[tt]
=INDIRECT(“MASTER![highlight #FCAF3E]D[/highlight]”&CurrentRow)
[/tt]
...being sure to change the [highlight #FCAF3E]column letter[/highlight] appropriately.

Now, each time you change the value in CurrentRow, your formulas will reflect the data from that row in the Master table.

Now all your code has to do is loop through the DATA cells on your Master sheet.

However, you have thrown a monkey wrench into the works, because your data in not contiguous. You have a “pretty” table, but to database standards and the standards that Excel expects to process, its pretty useless. Well maybe there is some value, but the mostly empty rows could become a problem.

So here’s how to loop through all the rows in your table...

Dim lRow As Long

For lRow = 3 to Sheets(“Master”).UsedRange.Rows.Count
   [CurrentRow] = lRow
   If Trim(Sheets(“Master”).Cells(CurrentRow, “D”}.Value) <> “” Then 
      Sheets(“Data Form”).PrintOut
   End If
Next

And that’s it.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[silkpurse]

Hi Skip,
Thanks for the help. I like the approach, but I think I must have entered something incorrectly or there is a mistake somewhere. My assumption was that the above code was intended as new macro that would run through the rows in my spreadsheet and execute the print instructions (is that wrong?). When I first copy/pasted the code into the VBA editor, it returned an invalid character error indicating the "}" that follows the "D". I replaced that with a close parenthesis, and VBA seemed happy. When I tried to run the macro, I got run-time error 1004, and VBA highlighted this line:

If Trim(Sheets(“Master”).Cells(CurrentRow, “D”).Value) <> “” Then

The only change I made was to re-type the quotation marks. When I'd put the "Indirect" formula in my data form, I got an error message that went away when I replaced them in the formula. Excel is apparently picky about that. I saved the macro with the new quotation marks and it ran, then whipped through the spreadsheet in no time. I thought we were good to go, but what I found was that I'd printed 165 copies of the data in row 3. Ouch.

I deleted all but 10 lines from the spreadsheet, so I don't accidently use a ream of paper trying to test this thing, and eliminated the blank lines. I can live without them. Now when I try to run the macro, I'm back to the 1004 error.

Sorry if I'm a dunce around this. I'm essentially a technical writer, who knows XML pretty well, and enough about coding to be dangerous. I'm on deployment with FEMA at the moment, and trying to help them automate some time-consuming processes.

I've attached my dummy spreadsheet again, with the modifications you suggested.

Thanks so much for your help and patience.

Henry

 

[SkipVought]

Sorry, I typed a CLOSE BRACE } rather than a CLOSE PARENTHESIS ) [BLUSH]

I was on my iPad, not my laptop.

Where is your VBA code? I expected to see it in your workbook.

Sub PrintDataForm()
    Dim lRow As Long
    
    For lRow = 3 To Sheets("Master").UsedRange.Rows.Count
       [CurrentRow] = lRow
       If Trim(Sheets("Master").Cells(lRow, "D").Value) <> "" Then
          Sheets("Data Form").PrintOut
       End If
    Next
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[silkpurse]

Hi Skip,
Maybe it's not supposed to go there, but I initiated this by recording a macro with nothing in it, then edited the macro by pasting your code there: View tab > Macros > View Macros > Edit. I gave it a new name, but here's what I've got, and other than the name, I think it's exactly what you've got above. Were you able to get the dummy file to run the routine? I'm still getting the same error, and the debugger is highlighting the "If...Then" row.

Henry


Sub NextRow()
'
' NextRow Macro
'
' Keyboard Shortcut: Ctrl+r
'
Dim lRow As Long

For lRow = 3 To Sheets("Master").UsedRange.Rows.Count
[CurrentRow] = lRow
If Trim(Sheets("Master").Cells(CurrentRow, "D").Value) <> "" Then
Sheets("Data Form").PrintOut
End If
Next


End Sub

 

[SkipVought]

Boy am I batting zero!

I modified the code above, so copy it again, paste it into your module and run.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[silkpurse]

Skip,
BINGO! Thanks so much. You've been a great help...
Henry

 

[SkipVought]

I’m really sorry for all the errors, not getting you a bonafide solution right off.

Glad its working now.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]