form fill-in and text manipulation

I

ironguy

Here's my problem:

I have a sheet that pulls in text data and formats it into specifi
columns. There are two sets of data with multiple columns.

I have another sheet that contains a template of a form. I need
separate form for each row of text data from the other sheet.
currently use COUNTIF in a VBA macro to count the number of entries i
each of the two text data sections then copy the form as a new shee
that many times.

What I can't figure out, is how to take individual parts of data fro
the rows of text and import them recursively into the different forms.

E.g. 'Datasheet' (imported text) has 6 rows of data, each with
columns. My macro creates 6 worksheets called Form (1), Form (2), et
to Form (6). I need to populate data, and manipulate it, from row
(multiple columns) of 'Datasheet' into Form (1). Likewise, row 2 int
Form (2).

Some of the data I need to do text manipulation on, like LEFT, MID
etc.

The oter part is that the 'Datasheet' text is updated daily.

Any thoughts on how to do a recursive data import and manipulation
 
D

Dave Peterson

Maybe you can do the creation in code:

Option Explicit
Sub testme()

Dim textWks As Worksheet
Dim FormsWks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Set textWks = ActiveSheet

With textWks
FirstRow = 1 'no header row?
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
Set FormsWks = Worksheets.Add(after:=Worksheets(Worksheets.Count))
FormsWks.Range("a1").Value = .Cells(iRow, "A").Value
FormsWks.Range("c9").Value = Left(.Cells(iRow, "B").Value, 12)
FormsWks.Range("e13").Value = Mid(.Cells(iRow, "C").Value, 8, 18)

On Error Resume Next
FormsWks.Name = "Forms (" & iRow & ")"
If Err.Number <> 0 Then
Err.Clear
MsgBox "Please rename: " & FormsWks.Name
End If
On Error GoTo 0
Next iRow
End With

End Sub


I didn't know how to map the columns, but it might give you an idea wiht
left/right/mid stuff.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
I

ironguy

Thanks for the macro. I will give it a shot. I've actually got i
working by using an Access form and the excel sheet as the database
The problem I have now is a matter of sorting and counting and tex
manipulation before the data is exported to the Access form.

As I stated, I have two types of data that are imported to th
worksheet. I'm trying to figure a way to sort and copy the tw
different data types into separate worksheets so I can manipulate th
text then export from there to ACCESS. I can sort the data, but a
unsure how to copy the data to another sheet. The problem is that th
numbers of rows of data will vary. Maybe I can use COUNTIF to get th
number of rows then soehow use that number to copy that many rows int
another sheet. Hmmm. Can I use the countif value as a reference
 
Top