Inserting Filtered RC cell information into other worksheets

Unsing 2003

Created a macro to add then copy/past cell info from one worksheet to a 
series of other new worksheets.  Works fine.

The reason for the macro was to automate the process of adding a worksheet 
(which is limited to the 255 character limit) then copy/paste cells so as to 
overcome the 255/per cell limitation.

Now I have a new series of worksheets "A 1 thru 10".

I would like to populate the cells of the new worksheets with certain cells 
existing on another worksheet, Named "B", which has filtered data.

Thus worksheet A1 has 10 cells (in a different layout on W/S "B").  I want 
to populate A1 then, A2, A3 .... etc., with cells from W/S "B" where the 
"Visible Rows" may be 
Row 3 next 7 next 20, next 57.

So A1 is populated with W/S "B" Row 3 information
   A2 is populated with W/S "B" Row 7 info
   A3 is populated with W/S "B" Row 20 info (and so on)

I am not sure how to cause a VBA loop to skip through W/S "B" visible rows, 
populate the various "A" series W/S and then stop when the last visible row 
on "B" is encountered.

So I need a counter?  that increments non-sequentially?
knows how many "A" W/S to populate and stops when all visible row 
information is completed.

Not sure whether to use .Offset() or what ever.

Any help would be appreciated.

Dennis

BTW the macro so far is:

Sub WorkSheetCopy()
'
'
' Assumes that the ActiveSheet is the Copy-from Worksheet
'
'
'
' Keyboard Shortcut: Ctrl+Shift+W
'
'
    Dim WorkSheetNumber As Long
    Dim OrigWorkSheetName As String
    ActiveSheet.Select
    OrigWorkSheetName = ActiveSheet.Name
    ActiveSheet.Copy After:=ActiveWorkbook.Worksheets _
         (ActiveWorkbook.Sheets.count)
    WorkSheetNumber = ActiveWorkbook.Sheets.count
    Sheets(OrigWorkSheetName).Select
    Cells.Select
    Selection.Copy
    Sheets(WorkSheetNumber).Select
    Cells.Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A1").Select
    Sheets(OrigWorkSheetName).Select
    Range("A1").Select
End Sub

0
Dennis (299)
7/18/2005 2:15:02 PM
excel.misc 78881 articles. 5 followers. Follow

10 Replies
355 Views

Similar Articles

[PageSpeed] 49

This is an example of one way to do the kind of copy/paste you have in mind:

Sub a()
    With Sheet2
        .Range("A1", 
..Range("A1").Offset(1000).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy
        Sheet1.Range("A1").PasteSpecial xlPasteAll
    End With
    Application.CutCopyMode = False
End Sub


-- 
Jim
"Dennis" <Dennis@discussions.microsoft.com> wrote in message 
news:37C59E5F-6E4F-4892-B633-253340F334E5@microsoft.com...
| Unsing 2003
|
| Created a macro to add then copy/past cell info from one worksheet to a
| series of other new worksheets.  Works fine.
|
| The reason for the macro was to automate the process of adding a worksheet
| (which is limited to the 255 character limit) then copy/paste cells so as 
to
| overcome the 255/per cell limitation.
|
| Now I have a new series of worksheets "A 1 thru 10".
|
| I would like to populate the cells of the new worksheets with certain 
cells
| existing on another worksheet, Named "B", which has filtered data.
|
| Thus worksheet A1 has 10 cells (in a different layout on W/S "B").  I want
| to populate A1 then, A2, A3 .... etc., with cells from W/S "B" where the
| "Visible Rows" may be
| Row 3 next 7 next 20, next 57.
|
| So A1 is populated with W/S "B" Row 3 information
|   A2 is populated with W/S "B" Row 7 info
|   A3 is populated with W/S "B" Row 20 info (and so on)
|
| I am not sure how to cause a VBA loop to skip through W/S "B" visible 
rows,
| populate the various "A" series W/S and then stop when the last visible 
row
| on "B" is encountered.
|
| So I need a counter?  that increments non-sequentially?
| knows how many "A" W/S to populate and stops when all visible row
| information is completed.
|
| Not sure whether to use .Offset() or what ever.
|
| Any help would be appreciated.
|
| Dennis
|
| BTW the macro so far is:
|
| Sub WorkSheetCopy()
| '
| '
| ' Assumes that the ActiveSheet is the Copy-from Worksheet
| '
| '
| '
| ' Keyboard Shortcut: Ctrl+Shift+W
| '
| '
|    Dim WorkSheetNumber As Long
|    Dim OrigWorkSheetName As String
|    ActiveSheet.Select
|    OrigWorkSheetName = ActiveSheet.Name
|    ActiveSheet.Copy After:=ActiveWorkbook.Worksheets _
|         (ActiveWorkbook.Sheets.count)
|    WorkSheetNumber = ActiveWorkbook.Sheets.count
|    Sheets(OrigWorkSheetName).Select
|    Cells.Select
|    Selection.Copy
|    Sheets(WorkSheetNumber).Select
|    Cells.Select
|    ActiveSheet.Paste
|    Application.CutCopyMode = False
|    Range("A1").Select
|    Sheets(OrigWorkSheetName).Select
|    Range("A1").Select
| End Sub
| 


0
jrrech (1932)
7/18/2005 6:01:04 PM
Thanks Jim!

"Jim Rech" wrote:

> This is an example of one way to do the kind of copy/paste you have in mind:
> 
> Sub a()
>     With Sheet2
>         .Range("A1", 
> ..Range("A1").Offset(1000).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy
>         Sheet1.Range("A1").PasteSpecial xlPasteAll
>     End With
>     Application.CutCopyMode = False
> End Sub
> 
> 
> -- 
> Jim
> "Dennis" <Dennis@discussions.microsoft.com> wrote in message 
> news:37C59E5F-6E4F-4892-B633-253340F334E5@microsoft.com...
> | Unsing 2003
> |
> | Created a macro to add then copy/past cell info from one worksheet to a
> | series of other new worksheets.  Works fine.
> |
> | The reason for the macro was to automate the process of adding a worksheet
> | (which is limited to the 255 character limit) then copy/paste cells so as 
> to
> | overcome the 255/per cell limitation.
> |
> | Now I have a new series of worksheets "A 1 thru 10".
> |
> | I would like to populate the cells of the new worksheets with certain 
> cells
> | existing on another worksheet, Named "B", which has filtered data.
> |
> | Thus worksheet A1 has 10 cells (in a different layout on W/S "B").  I want
> | to populate A1 then, A2, A3 .... etc., with cells from W/S "B" where the
> | "Visible Rows" may be
> | Row 3 next 7 next 20, next 57.
> |
> | So A1 is populated with W/S "B" Row 3 information
> |   A2 is populated with W/S "B" Row 7 info
> |   A3 is populated with W/S "B" Row 20 info (and so on)
> |
> | I am not sure how to cause a VBA loop to skip through W/S "B" visible 
> rows,
> | populate the various "A" series W/S and then stop when the last visible 
> row
> | on "B" is encountered.
> |
> | So I need a counter?  that increments non-sequentially?
> | knows how many "A" W/S to populate and stops when all visible row
> | information is completed.
> |
> | Not sure whether to use .Offset() or what ever.
> |
> | Any help would be appreciated.
> |
> | Dennis
> |
> | BTW the macro so far is:
> |
> | Sub WorkSheetCopy()
> | '
> | '
> | ' Assumes that the ActiveSheet is the Copy-from Worksheet
> | '
> | '
> | '
> | ' Keyboard Shortcut: Ctrl+Shift+W
> | '
> | '
> |    Dim WorkSheetNumber As Long
> |    Dim OrigWorkSheetName As String
> |    ActiveSheet.Select
> |    OrigWorkSheetName = ActiveSheet.Name
> |    ActiveSheet.Copy After:=ActiveWorkbook.Worksheets _
> |         (ActiveWorkbook.Sheets.count)
> |    WorkSheetNumber = ActiveWorkbook.Sheets.count
> |    Sheets(OrigWorkSheetName).Select
> |    Cells.Select
> |    Selection.Copy
> |    Sheets(WorkSheetNumber).Select
> |    Cells.Select
> |    ActiveSheet.Paste
> |    Application.CutCopyMode = False
> |    Range("A1").Select
> |    Sheets(OrigWorkSheetName).Select
> |    Range("A1").Select
> | End Sub
> | 
> 
> 
> 
0
Dennis (299)
7/18/2005 6:16:03 PM
Jim,

What is the path that I should look into to insert information into W/S's A 
1 thru 10
from Filtered Rows in Worksheet B?  (see my previous)

I like your method of copy/paste!

Dennis

"Jim Rech" wrote:

> This is an example of one way to do the kind of copy/paste you have in mind:
> 
> Sub a()
>     With Sheet2
>         .Range("A1", 
> ..Range("A1").Offset(1000).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy
>         Sheet1.Range("A1").PasteSpecial xlPasteAll
>     End With
>     Application.CutCopyMode = False
> End Sub
> 
> 
> -- 
> Jim
> "Dennis" <Dennis@discussions.microsoft.com> wrote in message 
> news:37C59E5F-6E4F-4892-B633-253340F334E5@microsoft.com...
> | Unsing 2003
> |
> | Created a macro to add then copy/past cell info from one worksheet to a
> | series of other new worksheets.  Works fine.
> |
> | The reason for the macro was to automate the process of adding a worksheet
> | (which is limited to the 255 character limit) then copy/paste cells so as 
> to
> | overcome the 255/per cell limitation.
> |
> | Now I have a new series of worksheets "A 1 thru 10".
> |
> | I would like to populate the cells of the new worksheets with certain 
> cells
> | existing on another worksheet, Named "B", which has filtered data.
> |
> | Thus worksheet A1 has 10 cells (in a different layout on W/S "B").  I want
> | to populate A1 then, A2, A3 .... etc., with cells from W/S "B" where the
> | "Visible Rows" may be
> | Row 3 next 7 next 20, next 57.
> |
> | So A1 is populated with W/S "B" Row 3 information
> |   A2 is populated with W/S "B" Row 7 info
> |   A3 is populated with W/S "B" Row 20 info (and so on)
> |
> | I am not sure how to cause a VBA loop to skip through W/S "B" visible 
> rows,
> | populate the various "A" series W/S and then stop when the last visible 
> row
> | on "B" is encountered.
> |
> | So I need a counter?  that increments non-sequentially?
> | knows how many "A" W/S to populate and stops when all visible row
> | information is completed.
> |
> | Not sure whether to use .Offset() or what ever.
> |
> | Any help would be appreciated.
> |
> | Dennis
> |
> | BTW the macro so far is:
> |
> | Sub WorkSheetCopy()
> | '
> | '
> | ' Assumes that the ActiveSheet is the Copy-from Worksheet
> | '
> | '
> | '
> | ' Keyboard Shortcut: Ctrl+Shift+W
> | '
> | '
> |    Dim WorkSheetNumber As Long
> |    Dim OrigWorkSheetName As String
> |    ActiveSheet.Select
> |    OrigWorkSheetName = ActiveSheet.Name
> |    ActiveSheet.Copy After:=ActiveWorkbook.Worksheets _
> |         (ActiveWorkbook.Sheets.count)
> |    WorkSheetNumber = ActiveWorkbook.Sheets.count
> |    Sheets(OrigWorkSheetName).Select
> |    Cells.Select
> |    Selection.Copy
> |    Sheets(WorkSheetNumber).Select
> |    Cells.Select
> |    ActiveSheet.Paste
> |    Application.CutCopyMode = False
> |    Range("A1").Select
> |    Sheets(OrigWorkSheetName).Select
> |    Range("A1").Select
> | End Sub
> | 
> 
> 
> 
0
Dennis (299)
7/18/2005 6:21:01 PM
An unfortunate name of worksheets (A1 thru A10).  It makes it look like cell
addresses.

But maybe something like this will show you one way to go through the visible
cells:

Option Explicit
Sub testme()

    Dim fWks As Worksheet  'from worksheet
    Dim iCtr As Long
    Dim rngF As Range
    Dim myCell As Range
    
    With ActiveSheet.AutoFilter.Range
        Set rngF = Nothing
        On Error Resume Next
        Set rngF = .Columns(1).Cells.Resize(.Rows.Count - 1, 1).Offset(1, 0) _
                            .SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If rngF Is Nothing Then
            'only the header is visible
            MsgBox "no details shown"
        Else
            iCtr = 0
            For Each myCell In rngF.Cells
                iCtr = iCtr + 1
                If WorksheetExists("a" & iCtr, ActiveWorkbook) Then
                    'it's there
                Else
                    'add it
                    Worksheets.Add
                    ActiveSheet.Name = "A" & iCtr
                End If
                
                myCell.EntireRow.Copy _
                    Destination:=Worksheets("a" & iCtr).Range("a1")
            Next myCell

        End If
    End With
End Sub
Function WorksheetExists(SheetName As Variant, _
    Optional WhichBook As Workbook) As Boolean
    'from Chip Pearson
    Dim WB As Workbook
    Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
    On Error Resume Next
    WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)
End Function

The WorksheetExists function was taken from a Chip Pearson post.  (I like it!)

Dennis wrote:
> 
> Unsing 2003
> 
> Created a macro to add then copy/past cell info from one worksheet to a
> series of other new worksheets.  Works fine.
> 
> The reason for the macro was to automate the process of adding a worksheet
> (which is limited to the 255 character limit) then copy/paste cells so as to
> overcome the 255/per cell limitation.
> 
> Now I have a new series of worksheets "A 1 thru 10".
> 
> I would like to populate the cells of the new worksheets with certain cells
> existing on another worksheet, Named "B", which has filtered data.
> 
> Thus worksheet A1 has 10 cells (in a different layout on W/S "B").  I want
> to populate A1 then, A2, A3 .... etc., with cells from W/S "B" where the
> "Visible Rows" may be
> Row 3 next 7 next 20, next 57.
> 
> So A1 is populated with W/S "B" Row 3 information
>    A2 is populated with W/S "B" Row 7 info
>    A3 is populated with W/S "B" Row 20 info (and so on)
> 
> I am not sure how to cause a VBA loop to skip through W/S "B" visible rows,
> populate the various "A" series W/S and then stop when the last visible row
> on "B" is encountered.
> 
> So I need a counter?  that increments non-sequentially?
> knows how many "A" W/S to populate and stops when all visible row
> information is completed.
> 
> Not sure whether to use .Offset() or what ever.
> 
> Any help would be appreciated.
> 
> Dennis
> 
> BTW the macro so far is:
> 
> Sub WorkSheetCopy()
> '
> '
> ' Assumes that the ActiveSheet is the Copy-from Worksheet
> '
> '
> '
> ' Keyboard Shortcut: Ctrl+Shift+W
> '
> '
>     Dim WorkSheetNumber As Long
>     Dim OrigWorkSheetName As String
>     ActiveSheet.Select
>     OrigWorkSheetName = ActiveSheet.Name
>     ActiveSheet.Copy After:=ActiveWorkbook.Worksheets _
>          (ActiveWorkbook.Sheets.count)
>     WorkSheetNumber = ActiveWorkbook.Sheets.count
>     Sheets(OrigWorkSheetName).Select
>     Cells.Select
>     Selection.Copy
>     Sheets(WorkSheetNumber).Select
>     Cells.Select
>     ActiveSheet.Paste
>     Application.CutCopyMode = False
>     Range("A1").Select
>     Sheets(OrigWorkSheetName).Select
>     Range("A1").Select
> End Sub

-- 

Dave Peterson
0
petersod (12005)
7/18/2005 6:22:11 PM
Dave,

I will enjoy tailoring this as I follow almost all of it.  If I need any 
additional, I add here - I'll do my best to take it the rest of the way.  
Thanks for the heavy lifting!

Dennis

"Dave Peterson" wrote:

> An unfortunate name of worksheets (A1 thru A10).  It makes it look like cell
> addresses.
> 
> But maybe something like this will show you one way to go through the visible
> cells:
> 
> Option Explicit
> Sub testme()
> 
>     Dim fWks As Worksheet  'from worksheet
>     Dim iCtr As Long
>     Dim rngF As Range
>     Dim myCell As Range
>     
>     With ActiveSheet.AutoFilter.Range
>         Set rngF = Nothing
>         On Error Resume Next
>         Set rngF = .Columns(1).Cells.Resize(.Rows.Count - 1, 1).Offset(1, 0) _
>                             .SpecialCells(xlCellTypeVisible)
>         On Error GoTo 0
>         If rngF Is Nothing Then
>             'only the header is visible
>             MsgBox "no details shown"
>         Else
>             iCtr = 0
>             For Each myCell In rngF.Cells
>                 iCtr = iCtr + 1
>                 If WorksheetExists("a" & iCtr, ActiveWorkbook) Then
>                     'it's there
>                 Else
>                     'add it
>                     Worksheets.Add
>                     ActiveSheet.Name = "A" & iCtr
>                 End If
>                 
>                 myCell.EntireRow.Copy _
>                     Destination:=Worksheets("a" & iCtr).Range("a1")
>             Next myCell
> 
>         End If
>     End With
> End Sub
> Function WorksheetExists(SheetName As Variant, _
>     Optional WhichBook As Workbook) As Boolean
>     'from Chip Pearson
>     Dim WB As Workbook
>     Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
>     On Error Resume Next
>     WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)
> End Function
> 
> The WorksheetExists function was taken from a Chip Pearson post.  (I like it!)
> 
> Dennis wrote:
> > 
> > Unsing 2003
> > 
> > Created a macro to add then copy/past cell info from one worksheet to a
> > series of other new worksheets.  Works fine.
> > 
> > The reason for the macro was to automate the process of adding a worksheet
> > (which is limited to the 255 character limit) then copy/paste cells so as to
> > overcome the 255/per cell limitation.
> > 
> > Now I have a new series of worksheets "A 1 thru 10".
> > 
> > I would like to populate the cells of the new worksheets with certain cells
> > existing on another worksheet, Named "B", which has filtered data.
> > 
> > Thus worksheet A1 has 10 cells (in a different layout on W/S "B").  I want
> > to populate A1 then, A2, A3 .... etc., with cells from W/S "B" where the
> > "Visible Rows" may be
> > Row 3 next 7 next 20, next 57.
> > 
> > So A1 is populated with W/S "B" Row 3 information
> >    A2 is populated with W/S "B" Row 7 info
> >    A3 is populated with W/S "B" Row 20 info (and so on)
> > 
> > I am not sure how to cause a VBA loop to skip through W/S "B" visible rows,
> > populate the various "A" series W/S and then stop when the last visible row
> > on "B" is encountered.
> > 
> > So I need a counter?  that increments non-sequentially?
> > knows how many "A" W/S to populate and stops when all visible row
> > information is completed.
> > 
> > Not sure whether to use .Offset() or what ever.
> > 
> > Any help would be appreciated.
> > 
> > Dennis
> > 
> > BTW the macro so far is:
> > 
> > Sub WorkSheetCopy()
> > '
> > '
> > ' Assumes that the ActiveSheet is the Copy-from Worksheet
> > '
> > '
> > '
> > ' Keyboard Shortcut: Ctrl+Shift+W
> > '
> > '
> >     Dim WorkSheetNumber As Long
> >     Dim OrigWorkSheetName As String
> >     ActiveSheet.Select
> >     OrigWorkSheetName = ActiveSheet.Name
> >     ActiveSheet.Copy After:=ActiveWorkbook.Worksheets _
> >          (ActiveWorkbook.Sheets.count)
> >     WorkSheetNumber = ActiveWorkbook.Sheets.count
> >     Sheets(OrigWorkSheetName).Select
> >     Cells.Select
> >     Selection.Copy
> >     Sheets(WorkSheetNumber).Select
> >     Cells.Select
> >     ActiveSheet.Paste
> >     Application.CutCopyMode = False
> >     Range("A1").Select
> >     Sheets(OrigWorkSheetName).Select
> >     Range("A1").Select
> > End Sub
> 
> -- 
> 
> Dave Peterson
> 
0
Dennis (299)
7/18/2005 9:13:43 PM
Dave, (I hope that you read this!)

It took me a while to "perfect" this procedure.  Obviously I am not that 
good with VBA.  Saying that, this procedure and call to another works very 
well as intended.

As an accountant, my real work is accounting, audit, and Sarbanes-Oxley 
(SOX) tasks.  VBA is my hobby.

We SOX people have a number of "Remediation" w/s to be generated from a 
result a "Master" control w/s.

It is not pretty, but it does the job and you were the one who gave me 
significant clue's.

I would like to share it with others as many of you do for we humbler XL 
users.


Thank you again, Dennis

************************************************************
Sub ABBPopulateWorksheet()
    '
    'Source Dave Peterson 7/18/2005 heavily modified 7/29/2005 9:30 AM to 
DMB special use
    '
    '
    'This Procedure is Calls Sub ABBOneCellText() and its result is the 
variable TextVar
    '  In order to maintain the Variable TextVar, see two lines below.
    '
    'NOTE: "Dim TextVar As String"  must be placed outside of both related 
Sub Routines.
    '      Place it at the top of this module file
    '
    If MsgBox("To succesfully run this Macro, these items must be known or 
established beforehand: " + Chr(10) _
              & "          The worksheet with the data to pass into another 
worksheet must be the file from which you start" + Chr(10) _
              & "          the macro.  i.e. (must be the active sheet).  
This sheet is the DataSourceSheet." + Chr(10) + Chr(10) _
              & "Obtain the full file-name AND Sheetname of the Excel 
Workbook/Sheet to be the Template format" + Chr(10) _
              & "Obtain the full file-name AND Sheetname of the Excel 
Workbook/Sheet into which the data will be placed" + Chr(10) + Chr(10) _
              & "Also obtain the Column letters of the final Control 
definition, Control Owner, Control Number and Risk Number" + Chr(10) + 
Chr(10) _
              & "ARE YOU READY TO CONTINUE?", vbYesNo, "NOTICE") = vbNo Then 
Exit Sub
              
    Dim myOrigSheetProtectStatus As Boolean
    Dim DataSourceBook As Workbook     'Data "from" workbook
    Dim DataSourceSheet As Worksheet   'Data "from" worksheet
    Dim CopyFromBook As Workbook       'Workbook to use as template
    Dim CopyFromSheet As Worksheet     'Worksheet to use as template
    Dim ReceiveBook As Workbook        'Workbook to be populated
    Dim ReceiveSheetName As String     'Worksheet to be populated
    Dim DataSourcePath As String       'Path for all files
    Dim CopyFromBookName As String     'Workbook to use as template
    Dim CopyFromSheetName As String    'Workbook to use as template
    Dim CopyFromSheetNameOrig As String 'Workbook to use as template
    Dim ReceiveBookName As String      'Workbook to be populated
    Dim DataSourceBookName As String   'Data "from" workbook name
    Dim DataSourceSheetName As String  'Data "from" worksheet name
    Dim VisibleRowsCounter As Long     'Counts visable rows after filtering
    Dim VisibleRowsRange As Range      'Range of all Visable Rows
    Dim MyCell As Range                'used to ID and select Rows with data
    Dim SheetExists As Boolean         'Logical status (existance) of a sheet
    Dim ColLtrControlNumber As String  'Column Letter(s) of Control Numbers
    Dim ColLtrControlDescrip As String 'Column Letter(s) of Control 
Description
    Dim ColLtrControlBy As String      'Column Letter(s) of Control Owner
    Dim ColLtrRiskNumber As String     'Column Letter(s) of Risk Number
    Dim Continue As Boolean
    Dim Counter As Long
    ' Must have the Data source workbook open and the filter ranges set
    '
    Continue = True
    Do While Continue = True
        On Error Resume Next
        myOrigSheetProtectStatus = ActiveSheet.ProtectContents
        If myOrigSheetProtectStatus = True Then
            ActiveSheet.Protect UserInterfaceOnly:=True
        End If
        If Error Then
           If MsgBox("There is no Active Worksheet ......... Exiting Routine 
...", vbOKOnly, _
           "NOTICE") = vbOK Then Exit Sub
        End If
        Set DataSourceBook = ActiveWorkbook
        Set DataSourceSheet = ActiveSheet
        '
        'Note:  Worksheet Tab names are limited to 31 characters. The 
process below, adds 7
        ' characters to the source tab name, (i.e." GAP nn") therefore the 
original tab name can not
        ' exceed 24 characters
        '
        If Len(DataSourceSheet.Name) > 24 Then
            MsgBox ("NOTE: The Data-Source Tab Label [" & 
DataSourceSheet.Name & _
            "] will be truncated to 24 Characters!!")
        End If
        DataSourceSheetName = Trim(Mid(DataSourceSheet.Name, 1, 24))
        DataSourceSheet.Name = DataSourceSheetName
        CopyFromSheetName = "GAP Template"
        CopyFromSheetNameOrig = "GAP Template"
        ' Determines the path of the Data source workbook and saves it as a 
variable
        '   to use below
        DataSourcePath = DataSourceBook.Path
        DataSourceBookName = DataSourceBook.Name
        ' Opens up the workbook from which the one and only W/S will become 
a "template" for use below
        DataSourceBookName = InputBox("Enter the complete File Name 
including the Extension" & Chr(10) _
            & "of the File from which the data will come", , 
DataSourceBookName)
        If DataSourceBookName = "" Then
            MsgBox "Valid Data not Entered - CANCELLED!"
            Exit Sub
        End If
        ReceiveBookName = InputBox("Enter the complete File Name including 
the Extension" & Chr(10) & _
            "of the File into which the data will go" & Chr(10) & Chr(10) & 
"NOTE: The first Sheet in " & _
            "the ReceiveBook will be the Template", , ReceiveBookName)
        If ReceiveBookName = "" Then
            MsgBox "Valid Data not Entered - CANCELLED!"
            Exit Sub
        End If
        CopyFromBookName = InputBox("Enter the complete File Name including 
the Extension" & Chr(10) & _
             "of the File ""Template"" to Copy From", , CopyFromBookName)
        CopyFromSheetName = InputBox("Enter the SHEET Name to be copied in 
the File 'Template' to" & _
             "Copy From", , CopyFromSheetName)
        Set CopyFromBook = Nothing
        On Error Resume Next
        Set CopyFromBook = Workbooks(CopyFromBookName)
        On Error GoTo 0
        If CopyFromBook Is Nothing Then
           Set CopyFromBook = Workbooks.Open(fileName:=DataSourcePath & "\" 
& CopyFromBookName)
        End If
        Set ReceiveBook = Nothing
        On Error Resume Next
        Set ReceiveBook = Workbooks(ReceiveBookName)
        On Error GoTo 0
        If ReceiveBook Is Nothing Then
               Set ReceiveBook = Workbooks.Open(fileName:=DataSourcePath & 
"\" & ReceiveBookName)
               If Error Then
                  Workbooks.Add
                  Workbooks("Book1").SaveAs (DataSourcePath & "\" & 
ReceiveBookName)
               End If
               On Error GoTo 0
        End If
    '   ReceiveBook should have THE FIRST sheet cloned as a template.
        DataSourceBook.Activate
        DataSourceSheet.Activate
        On Error Resume Next
        With DataSourceSheet.AutoFilter.Range
            Set VisibleRowsRange = Nothing
            On Error Resume Next
            Set VisibleRowsRange = .Columns(1).Cells.Resize(.Rows.count - 1, 
1).Offset(1, 0).SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
            If VisibleRowsRange Is Nothing Then
                'only the header is visible
                MsgBox "                            No filtered details 
shown !!!" + Chr(10) + Chr(10) + _
                "The Template W/S must be Active when you start this Macro" 
+ Chr(10) + _
                "Also make sure that AutoFilter is actively filtering one 
Column"
            Else
                VisibleRowsCounter = 0
                ' Note: the range is one-cell wide.  Thus VisibleRowsCounter 
will increment until = number of rows
                For Each MyCell In VisibleRowsRange.Cells
                    VisibleRowsCounter = VisibleRowsCounter + 1
                    Dim WSName As String
                    WSName = DataSourceSheetName & " GAP " & 
VisibleRowsCounter
                    If WorksheetExists(WSName, ReceiveBook) Then
                        If MsgBox("Be aware that you may be duplicating 
worksheets!" + Chr(10) & Chr(10) & _
                           "Do you wish to Continue?", vbYesNo) = vbNo Then 
Exit Sub
                        CopyFromSheetName = WSName
                    Else
                        If WorksheetExists("GAP Template", ReceiveBook) Then
                            CopyFromSheetName = "GAP Template"
                            ' Do not wish to count the Template w/s
                            VisibleRowsCounter = VisibleRowsCounter - 1
                        Else
                            MsgBox ("Add a Worksheet to Copy From and Name 
it 'GAP Template'")
                            Exit Sub
                        End If
                    End If
                    ReceiveBook.Worksheets.Add 
After:=ReceiveBook.Worksheets(ReceiveBook.Sheets.count)
                    VisibleRowsCounter = VisibleRowsCounter + 1
                    NewSheetName = ReceiveBook.ActiveSheet.Name
                    ReceiveBook.Sheets(NewSheetName).Activate
                    Counter = VisibleRowsCounter
                    'This loop checks if there is a worksheet with the same 
name
                    Do While WorksheetExists(DataSourceSheetName & " GAP " & 
Counter, ReceiveBook)
                        Counter = Counter + 1
                    Loop
                    ReceiveBook.Sheets(NewSheetName).Name = 
DataSourceSheetName & " GAP " & Counter
                    ReceiveSheetName = DataSourceSheetName & " GAP " & Counter
'                    On Error Resume Next
                    ReceiveBook.ActiveSheet.Name = ReceiveSheetName
                    CopyFromBook.Worksheets(CopyFromSheetName).Activate
                    Cells.Copy
                    ReceiveBook.Worksheets(ReceiveSheetName).Activate
                    ReceiveBook.Worksheets(ReceiveSheetName).Select
                    Cells.Select
                    Selection.PasteSpecial Paste:=xlPasteFormats, 
Operation:=xlNone, _
                       SkipBlanks:=False, Transpose:=False
                    ReceiveBook.Worksheets(ReceiveSheetName).Paste
                    CopyFromBook.Worksheets(CopyFromSheetName).Activate
                    Cells.Copy
                    ReceiveBook.Worksheets(ReceiveSheetName).Activate
                    ReceiveBook.Worksheets(ReceiveSheetName).Select
                    Cells.Select
                    ReceiveBook.Worksheets(ReceiveSheetName).Paste
                    Application.CutCopyMode = False
                    DataSourceBook.Activate
                    If VisibleRowsCounter < 3 Then
                        ColLtrControlNumber = Trim(InputBox("Control 
Number", "Enter Column Letter(s) of:", "K"))
                        ColLtrControlDescrip = Trim(InputBox("Actual 
Control", "Enter Column Letter(s) of:", "L"))
                        ColLtrControlBy = Trim(InputBox("Control Performed 
By", "Enter Column Letter(s) of:", "O"))
                        ColLtrRiskNumber = Trim(InputBox("Risk Number", 
"Enter Column Letter(s) of:", "G"))
                    End If
                    Sheets(1).Activate
                    Call ABBOneCellText
                    ReceiveBook.Worksheets(ReceiveSheetName).Activate
                    ActiveSheet.Range("A4").Value = Mid(DataSourceBookName, 
1, 12)
                    ActiveSheet.Range("A4").Font.ColorIndex = 5
                    ActiveSheet.Range("A6").Value = TextVar
                    ActiveSheet.Range("A6").Font.ColorIndex = 5
                    ActiveSheet.Range("A8").Formula = "=MID('[" & 
DataSourceBookName & "]" & _
                        DataSourceSheetName & "'!" & ColLtrControlNumber & 
MyCell.Cells.Row & ",1,1)" '
                    ActiveSheet.Range("A8").Font.ColorIndex = 5
                    ActiveSheet.Range("A10").Formula = "='[" & 
DataSourceBookName & "]" & _
                        DataSourceSheetName & "'!" & ColLtrControlDescrip & 
MyCell.Cells.Row
                    ActiveSheet.Range("A10").Font.ColorIndex = 5
                    ActiveSheet.Range("D4").Value = Mid(Now(), 1, 10)
                    ActiveSheet.Range("D4").Font.ColorIndex = 5
                    ActiveSheet.Range("F4").Formula = "='[" & 
DataSourceBookName & "]" & _
                        DataSourceSheetName & "'!" & ColLtrControlBy & 
MyCell.Cells.Row
                    ActiveSheet.Range("F4").Font.ColorIndex = 5
                    ActiveSheet.Range("F8").Formula = "='[" & 
DataSourceBookName & "]" & _
                        DataSourceSheetName & "'!" & ColLtrRiskNumber & 
MyCell.Cells.Row
                    ActiveSheet.Range("F8").Font.ColorIndex = 5
                    ActiveSheet.Range("I4").Formula = "='[" & 
DataSourceBookName & "]" & _
                        DataSourceSheetName & "'!" & ColLtrControlNumber & 
MyCell.Cells.Row
                    ActiveSheet.Range("I4").Font.ColorIndex = 5
                    ReceiveBook.Worksheets(ReceiveSheetName).Select
                    Cells.Select
                    With Selection
                       .Copy
                       .PasteSpecial Paste:=xlPasteValues, 
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                    End With
                    Application.CutCopyMode = False
                    Range("A1").Select
                Next MyCell
            End If
        End With
        If myOrigSheetProtectStatus = True Then
            DataSourceSheet.Protect UserInterfaceOnly:=False
        End If
        If MsgBox("Press YES to Continue Processing Sheets", vbYesNo) = vbNo 
Then Continue = False
        'Activate book and sheets which were active in the beginning of the 
process
        DataSourceBook.Activate
        DataSourceSheet.Activate
    Loop
'    If WorksheetExists("GAP Template", ReceiveBook) And 
ReceiveBook.Sheets.count > 1 Then
'        ReceiveBook.Activate
'        Application.DisplayAlerts = False
'        Sheets("GAP Template").Delete
'        Application.DisplayAlerts = True
'    End If
   
   MsgBox "Process Completed!   Press OK to Continue"

End Sub


'The following VBA code finds a location in your worksheet [Sheets(1) in 
this case],
'you then manually create an Offset setting from the text-find
'Cells.Find(What:="Your Choice of Text") to the actual data that you wish to 
utilize.

'Once the range "MyRange" is computed, another loop computes a Variable 
"TextVar"
'which represents the information in the "MyRange" cells in a Text variable 
that can
'be saved in another cell/Worksheet.

'This can be a great help to those doing SOX work where a great deal of data
' rollups occur.
'
'Assistance from Jim Rech 7/26/2005 Excel.General
'

Sub ABBOneCellText()
    '
    'Assistance from Jim Rech 7/26/2005 Excel.General
    '
    'This Procedure is called from ABBPopulateWorksheet()
    '
    'NOTE: "Dim TextVar As String"  must be placed outside of both related 
Sub Routines.
    '      Place it at the top of this module file
    '
    Dim MyRange As Range
    Dim MyCell As Range
    Dim LastDataColumn As Integer
    Dim LastDataRow As Integer
    Dim FirstDataColumn As Integer
    Dim FirstDataRow As Integer
    '
    'Note: Do NOT "Dim TextVar As String" in this module
    '
    ' "Finds the 1st instance of the use of "IMPACTED ABACUS" in the W/S and 
Offsets
    '  to the first cell with meaningful data
    Set MyRange = Sheets(1).Cells.Find(What:="IMPACTED ABACUS").Offset(2, 3)
    'Establishes the upperleft row number
    FirstDataRow = MyRange.Row
    LastDataRow = FirstDataRow
    'Establishes the upperleft Column number
    FirstDataColumn = MyRange.Column
    LastDataColumn = FirstDataColumn
    ' Loop computes last column with data
    Do While Not IsEmpty(Rows(FirstDataRow).Cells(LastDataColumn))
      LastDataColumn = LastDataColumn + 1
    Loop
    'Represents the last column with meaningful data in the 1st meaningful 
row of data
    LastDataColumn = LastDataColumn - 1
    Do While Not IsEmpty(Columns(FirstDataColumn).Cells(LastDataRow))
      LastDataRow = LastDataRow + 1
    Loop
    'Represents the last row with meaningful data in the "MyRange" row of data
    LastDataRow = LastDataRow - 1
    'Establishes  or "Sets" the Meaningful Data range
    Set MyRange = Range(Cells(FirstDataRow, FirstDataColumn), 
Cells(LastDataRow, LastDataColumn))
    TextVar = Empty
    For Each MyCell In MyRange
        If MyCell.Value <> "" Then TextVar = TextVar + MyCell.Value + Chr(10)
    Next MyCell
    'Clears any previous selections to A1
    Range("A1").Select
End Sub

Sub GetRealLastCell()
  ' Tom Olgavy  Excel.General 7/6/2005
  Dim RealLastRow As Long
  Dim RealLastColumn As Long
  Range("A1").Select
  On Error Resume Next
    RealLastRow = _
    Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
     RealLastColumn = _
    Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
   Cells(RealLastRow, RealLastColumn).Select
End Sub
************************************************************





"Dave Peterson" wrote:

> An unfortunate name of worksheets (A1 thru A10).  It makes it look like cell
> addresses.
> 
> But maybe something like this will show you one way to go through the visible
> cells:
> 
> Option Explicit
> Sub testme()
> 
>     Dim fWks As Worksheet  'from worksheet
>     Dim iCtr As Long
>     Dim rngF As Range
>     Dim myCell As Range
>     
>     With ActiveSheet.AutoFilter.Range
>         Set rngF = Nothing
>         On Error Resume Next
>         Set rngF = .Columns(1).Cells.Resize(.Rows.Count - 1, 1).Offset(1, 0) _
>                             .SpecialCells(xlCellTypeVisible)
>         On Error GoTo 0
>         If rngF Is Nothing Then
>             'only the header is visible
>             MsgBox "no details shown"
>         Else
>             iCtr = 0
>             For Each myCell In rngF.Cells
>                 iCtr = iCtr + 1
>                 If WorksheetExists("a" & iCtr, ActiveWorkbook) Then
>                     'it's there
>                 Else
>                     'add it
>                     Worksheets.Add
>                     ActiveSheet.Name = "A" & iCtr
>                 End If
>                 
>                 myCell.EntireRow.Copy _
>                     Destination:=Worksheets("a" & iCtr).Range("a1")
>             Next myCell
> 
>         End If
>     End With
> End Sub
> Function WorksheetExists(SheetName As Variant, _
>     Optional WhichBook As Workbook) As Boolean
>     'from Chip Pearson
>     Dim WB As Workbook
>     Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
>     On Error Resume Next
>     WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)
> End Function
> 
> The WorksheetExists function was taken from a Chip Pearson post.  (I like it!)
> 
> Dennis wrote:
> > 
> > Unsing 2003
> > 
> > Created a macro to add then copy/past cell info from one worksheet to a
> > series of other new worksheets.  Works fine.
> > 
> > The reason for the macro was to automate the process of adding a worksheet
> > (which is limited to the 255 character limit) then copy/paste cells so as to
> > overcome the 255/per cell limitation.
> > 
> > Now I have a new series of worksheets "A 1 thru 10".
> > 
> > I would like to populate the cells of the new worksheets with certain cells
> > existing on another worksheet, Named "B", which has filtered data.
> > 
> > Thus worksheet A1 has 10 cells (in a different layout on W/S "B").  I want
> > to populate A1 then, A2, A3 .... etc., with cells from W/S "B" where the
> > "Visible Rows" may be
> > Row 3 next 7 next 20, next 57.
> > 
> > So A1 is populated with W/S "B" Row 3 information
> >    A2 is populated with W/S "B" Row 7 info
> >    A3 is populated with W/S "B" Row 20 info (and so on)
> > 
> > I am not sure how to cause a VBA loop to skip through W/S "B" visible rows,
> > populate the various "A" series W/S and then stop when the last visible row
> > on "B" is encountered.
> > 
> > So I need a counter?  that increments non-sequentially?
> > knows how many "A" W/S to populate and stops when all visible row
> > information is completed.
> > 
> > Not sure whether to use .Offset() or what ever.
> > 
> > Any help would be appreciated.
> > 
> > Dennis
> > 
> > BTW the macro so far is:
> > 
> > Sub WorkSheetCopy()
> > '
> > '
> > ' Assumes that the ActiveSheet is the Copy-from Worksheet
> > '
> > '
> > '
> > ' Keyboard Shortcut: Ctrl+Shift+W
> > '
> > '
> >     Dim WorkSheetNumber As Long
> >     Dim OrigWorkSheetName As String
> >     ActiveSheet.Select
> >     OrigWorkSheetName = ActiveSheet.Name
> >     ActiveSheet.Copy After:=ActiveWorkbook.Worksheets _
> >          (ActiveWorkbook.Sheets.count)
> >     WorkSheetNumber = ActiveWorkbook.Sheets.count
> >     Sheets(OrigWorkSheetName).Select
> >     Cells.Select
> >     Selection.Copy
> >     Sheets(WorkSheetNumber).Select
> >     Cells.Select
> >     ActiveSheet.Paste
> >     Application.CutCopyMode = False
> >     Range("A1").Select
> >     Sheets(OrigWorkSheetName).Select
> >     Range("A1").Select
> > End Sub
> 
> -- 
> 
> Dave Peterson
> 
0
Dennis (299)
7/29/2005 2:22:03 PM
Thanks for your knowledge and time.  Folks like yourself really do help out 
when we get into, probably, our own traps.

See the "fulfillment" at the end of this thread.

Dennis


"Jim Rech" wrote:

> This is an example of one way to do the kind of copy/paste you have in mind:
> 
> Sub a()
>     With Sheet2
>         .Range("A1", 
> ..Range("A1").Offset(1000).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy
>         Sheet1.Range("A1").PasteSpecial xlPasteAll
>     End With
>     Application.CutCopyMode = False
> End Sub
> 
> 
> -- 
> Jim
> "Dennis" <Dennis@discussions.microsoft.com> wrote in message 
> news:37C59E5F-6E4F-4892-B633-253340F334E5@microsoft.com...
> | Unsing 2003
> |
> | Created a macro to add then copy/past cell info from one worksheet to a
> | series of other new worksheets.  Works fine.
> |
> | The reason for the macro was to automate the process of adding a worksheet
> | (which is limited to the 255 character limit) then copy/paste cells so as 
> to
> | overcome the 255/per cell limitation.
> |
> | Now I have a new series of worksheets "A 1 thru 10".
> |
> | I would like to populate the cells of the new worksheets with certain 
> cells
> | existing on another worksheet, Named "B", which has filtered data.
> |
> | Thus worksheet A1 has 10 cells (in a different layout on W/S "B").  I want
> | to populate A1 then, A2, A3 .... etc., with cells from W/S "B" where the
> | "Visible Rows" may be
> | Row 3 next 7 next 20, next 57.
> |
> | So A1 is populated with W/S "B" Row 3 information
> |   A2 is populated with W/S "B" Row 7 info
> |   A3 is populated with W/S "B" Row 20 info (and so on)
> |
> | I am not sure how to cause a VBA loop to skip through W/S "B" visible 
> rows,
> | populate the various "A" series W/S and then stop when the last visible 
> row
> | on "B" is encountered.
> |
> | So I need a counter?  that increments non-sequentially?
> | knows how many "A" W/S to populate and stops when all visible row
> | information is completed.
> |
> | Not sure whether to use .Offset() or what ever.
> |
> | Any help would be appreciated.
> |
> | Dennis
> |
> | BTW the macro so far is:
> |
> | Sub WorkSheetCopy()
> | '
> | '
> | ' Assumes that the ActiveSheet is the Copy-from Worksheet
> | '
> | '
> | '
> | ' Keyboard Shortcut: Ctrl+Shift+W
> | '
> | '
> |    Dim WorkSheetNumber As Long
> |    Dim OrigWorkSheetName As String
> |    ActiveSheet.Select
> |    OrigWorkSheetName = ActiveSheet.Name
> |    ActiveSheet.Copy After:=ActiveWorkbook.Worksheets _
> |         (ActiveWorkbook.Sheets.count)
> |    WorkSheetNumber = ActiveWorkbook.Sheets.count
> |    Sheets(OrigWorkSheetName).Select
> |    Cells.Select
> |    Selection.Copy
> |    Sheets(WorkSheetNumber).Select
> |    Cells.Select
> |    ActiveSheet.Paste
> |    Application.CutCopyMode = False
> |    Range("A1").Select
> |    Sheets(OrigWorkSheetName).Select
> |    Range("A1").Select
> | End Sub
> | 
> 
> 
> 
0
Dennis (299)
7/29/2005 2:51:11 PM
To all of those who may be interested,  there is also a Function() utilized 
in the macro that you may not have.
Place this Function() in the same VBA Module as the above macro.

Dennis

*****************************************************


Function WorksheetExists(SheetName As Variant, _
    Optional WhichBook As Workbook) As Boolean
    'from Chip Pearson via Dave Peterson 7/19/2005
    ' for Add Worksheet() and PopulateWorksheet()
    '
    Dim WB As Workbook
    Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
    On Error Resume Next
    WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)
End Function



"Dennis" wrote:

> Dave, (I hope that you read this!)
> 
> It took me a while to "perfect" this procedure.  Obviously I am not that 
> good with VBA.  Saying that, this procedure and call to another works very 
> well as intended.
> 
> As an accountant, my real work is accounting, audit, and Sarbanes-Oxley 
> (SOX) tasks.  VBA is my hobby.
> 
> We SOX people have a number of "Remediation" w/s to be generated from a 
> result a "Master" control w/s.
> 
> It is not pretty, but it does the job and you were the one who gave me 
> significant clue's.
> 
> I would like to share it with others as many of you do for we humbler XL 
> users.
> 
> 
> Thank you again, Dennis
> 
> ************************************************************
> Sub ABBPopulateWorksheet()
>     '
>     'Source Dave Peterson 7/18/2005 heavily modified 7/29/2005 9:30 AM to 
> DMB special use
>     '
>     '
>     'This Procedure is Calls Sub ABBOneCellText() and its result is the 
> variable TextVar
>     '  In order to maintain the Variable TextVar, see two lines below.
>     '
>     'NOTE: "Dim TextVar As String"  must be placed outside of both related 
> Sub Routines.
>     '      Place it at the top of this module file
>     '
>     If MsgBox("To succesfully run this Macro, these items must be known or 
> established beforehand: " + Chr(10) _
>               & "          The worksheet with the data to pass into another 
> worksheet must be the file from which you start" + Chr(10) _
>               & "          the macro.  i.e. (must be the active sheet).  
> This sheet is the DataSourceSheet." + Chr(10) + Chr(10) _
>               & "Obtain the full file-name AND Sheetname of the Excel 
> Workbook/Sheet to be the Template format" + Chr(10) _
>               & "Obtain the full file-name AND Sheetname of the Excel 
> Workbook/Sheet into which the data will be placed" + Chr(10) + Chr(10) _
>               & "Also obtain the Column letters of the final Control 
> definition, Control Owner, Control Number and Risk Number" + Chr(10) + 
> Chr(10) _
>               & "ARE YOU READY TO CONTINUE?", vbYesNo, "NOTICE") = vbNo Then 
> Exit Sub
>               
>     Dim myOrigSheetProtectStatus As Boolean
>     Dim DataSourceBook As Workbook     'Data "from" workbook
>     Dim DataSourceSheet As Worksheet   'Data "from" worksheet
>     Dim CopyFromBook As Workbook       'Workbook to use as template
>     Dim CopyFromSheet As Worksheet     'Worksheet to use as template
>     Dim ReceiveBook As Workbook        'Workbook to be populated
>     Dim ReceiveSheetName As String     'Worksheet to be populated
>     Dim DataSourcePath As String       'Path for all files
>     Dim CopyFromBookName As String     'Workbook to use as template
>     Dim CopyFromSheetName As String    'Workbook to use as template
>     Dim CopyFromSheetNameOrig As String 'Workbook to use as template
>     Dim ReceiveBookName As String      'Workbook to be populated
>     Dim DataSourceBookName As String   'Data "from" workbook name
>     Dim DataSourceSheetName As String  'Data "from" worksheet name
>     Dim VisibleRowsCounter As Long     'Counts visable rows after filtering
>     Dim VisibleRowsRange As Range      'Range of all Visable Rows
>     Dim MyCell As Range                'used to ID and select Rows with data
>     Dim SheetExists As Boolean         'Logical status (existance) of a sheet
>     Dim ColLtrControlNumber As String  'Column Letter(s) of Control Numbers
>     Dim ColLtrControlDescrip As String 'Column Letter(s) of Control 
> Description
>     Dim ColLtrControlBy As String      'Column Letter(s) of Control Owner
>     Dim ColLtrRiskNumber As String     'Column Letter(s) of Risk Number
>     Dim Continue As Boolean
>     Dim Counter As Long
>     ' Must have the Data source workbook open and the filter ranges set
>     '
>     Continue = True
>     Do While Continue = True
>         On Error Resume Next
>         myOrigSheetProtectStatus = ActiveSheet.ProtectContents
>         If myOrigSheetProtectStatus = True Then
>             ActiveSheet.Protect UserInterfaceOnly:=True
>         End If
>         If Error Then
>            If MsgBox("There is no Active Worksheet ......... Exiting Routine 
> ..", vbOKOnly, _
>            "NOTICE") = vbOK Then Exit Sub
>         End If
>         Set DataSourceBook = ActiveWorkbook
>         Set DataSourceSheet = ActiveSheet
>         '
>         'Note:  Worksheet Tab names are limited to 31 characters. The 
> process below, adds 7
>         ' characters to the source tab name, (i.e." GAP nn") therefore the 
> original tab name can not
>         ' exceed 24 characters
>         '
>         If Len(DataSourceSheet.Name) > 24 Then
>             MsgBox ("NOTE: The Data-Source Tab Label [" & 
> DataSourceSheet.Name & _
>             "] will be truncated to 24 Characters!!")
>         End If
>         DataSourceSheetName = Trim(Mid(DataSourceSheet.Name, 1, 24))
>         DataSourceSheet.Name = DataSourceSheetName
>         CopyFromSheetName = "GAP Template"
>         CopyFromSheetNameOrig = "GAP Template"
>         ' Determines the path of the Data source workbook and saves it as a 
> variable
>         '   to use below
>         DataSourcePath = DataSourceBook.Path
>         DataSourceBookName = DataSourceBook.Name
>         ' Opens up the workbook from which the one and only W/S will become 
> a "template" for use below
>         DataSourceBookName = InputBox("Enter the complete File Name 
> including the Extension" & Chr(10) _
>             & "of the File from which the data will come", , 
> DataSourceBookName)
>         If DataSourceBookName = "" Then
>             MsgBox "Valid Data not Entered - CANCELLED!"
>             Exit Sub
>         End If
>         ReceiveBookName = InputBox("Enter the complete File Name including 
> the Extension" & Chr(10) & _
>             "of the File into which the data will go" & Chr(10) & Chr(10) & 
> "NOTE: The first Sheet in " & _
>             "the ReceiveBook will be the Template", , ReceiveBookName)
>         If ReceiveBookName = "" Then
>             MsgBox "Valid Data not Entered - CANCELLED!"
>             Exit Sub
>         End If
>         CopyFromBookName = InputBox("Enter the complete File Name including 
> the Extension" & Chr(10) & _
>              "of the File ""Template"" to Copy From", , CopyFromBookName)
>         CopyFromSheetName = InputBox("Enter the SHEET Name to be copied in 
> the File 'Template' to" & _
>              "Copy From", , CopyFromSheetName)
>         Set CopyFromBook = Nothing
>         On Error Resume Next
>         Set CopyFromBook = Workbooks(CopyFromBookName)
>         On Error GoTo 0
>         If CopyFromBook Is Nothing Then
>            Set CopyFromBook = Workbooks.Open(fileName:=DataSourcePath & "\" 
> & CopyFromBookName)
>         End If
>         Set ReceiveBook = Nothing
>         On Error Resume Next
>         Set ReceiveBook = Workbooks(ReceiveBookName)
>         On Error GoTo 0
>         If ReceiveBook Is Nothing Then
>                Set ReceiveBook = Workbooks.Open(fileName:=DataSourcePath & 
> "\" & ReceiveBookName)
>                If Error Then
>                   Workbooks.Add
>                   Workbooks("Book1").SaveAs (DataSourcePath & "\" & 
> ReceiveBookName)
>                End If
>                On Error GoTo 0
>         End If
>     '   ReceiveBook should have THE FIRST sheet cloned as a template.
>         DataSourceBook.Activate
>         DataSourceSheet.Activate
>         On Error Resume Next
>         With DataSourceSheet.AutoFilter.Range
>             Set VisibleRowsRange = Nothing
>             On Error Resume Next
>             Set VisibleRowsRange = .Columns(1).Cells.Resize(.Rows.count - 1, 
> 1).Offset(1, 0).SpecialCells(xlCellTypeVisible)
>             On Error GoTo 0
>             If VisibleRowsRange Is Nothing Then
>                 'only the header is visible
>                 MsgBox "                            No filtered details 
> shown !!!" + Chr(10) + Chr(10) + _
>                 "The Template W/S must be Active when you start this Macro" 
> + Chr(10) + _
>                 "Also make sure that AutoFilter is actively filtering one 
> Column"
>             Else
>                 VisibleRowsCounter = 0
>                 ' Note: the range is one-cell wide.  Thus VisibleRowsCounter 
> will increment until = number of rows
>                 For Each MyCell In VisibleRowsRange.Cells
>                     VisibleRowsCounter = VisibleRowsCounter + 1
>                     Dim WSName As String
>                     WSName = DataSourceSheetName & " GAP " & 
> VisibleRowsCounter
>                     If WorksheetExists(WSName, ReceiveBook) Then
>                         If MsgBox("Be aware that you may be duplicating 
> worksheets!" + Chr(10) & Chr(10) & _
>                            "Do you wish to Continue?", vbYesNo) = vbNo Then 
> Exit Sub
>                         CopyFromSheetName = WSName
>                     Else
>                         If WorksheetExists("GAP Template", ReceiveBook) Then
>                             CopyFromSheetName = "GAP Template"
>                             ' Do not wish to count the Template w/s
>                             VisibleRowsCounter = VisibleRowsCounter - 1
>                         Else
>                             MsgBox ("Add a Worksheet to Copy From and Name 
> it 'GAP Template'")
>                             Exit Sub
>                         End If
>                     End If
>                     ReceiveBook.Worksheets.Add 
> After:=ReceiveBook.Worksheets(ReceiveBook.Sheets.count)
>                     VisibleRowsCounter = VisibleRowsCounter + 1
>                     NewSheetName = ReceiveBook.ActiveSheet.Name
>                     ReceiveBook.Sheets(NewSheetName).Activate
>                     Counter = VisibleRowsCounter
>                     'This loop checks if there is a worksheet with the same 
> name
>                     Do While WorksheetExists(DataSourceSheetName & " GAP " & 
> Counter, ReceiveBook)
>                         Counter = Counter + 1
>                     Loop
>                     ReceiveBook.Sheets(NewSheetName).Name = 
> DataSourceSheetName & " GAP " & Counter
>                     ReceiveSheetName = DataSourceSheetName & " GAP " & Counter
> '                    On Error Resume Next
>                     ReceiveBook.ActiveSheet.Name = ReceiveSheetName
>                     CopyFromBook.Worksheets(CopyFromSheetName).Activate
>                     Cells.Copy
>                     ReceiveBook.Worksheets(ReceiveSheetName).Activate
>                     ReceiveBook.Worksheets(ReceiveSheetName).Select
>                     Cells.Select
>                     Selection.PasteSpecial Paste:=xlPasteFormats, 
> Operation:=xlNone, _
>                        SkipBlanks:=False, Transpose:=False
>                     ReceiveBook.Worksheets(ReceiveSheetName).Paste
>                     CopyFromBook.Worksheets(CopyFromSheetName).Activate
>                     Cells.Copy
>                     ReceiveBook.Worksheets(ReceiveSheetName).Activate
>                     ReceiveBook.Worksheets(ReceiveSheetName).Select
>                     Cells.Select
>                     ReceiveBook.Worksheets(ReceiveSheetName).Paste
>                     Application.CutCopyMode = False
>                     DataSourceBook.Activate
>                     If VisibleRowsCounter < 3 Then
>                         ColLtrControlNumber = Trim(InputBox("Control 
> Number", "Enter Column Letter(s) of:", "K"))
>                         ColLtrControlDescrip = Trim(InputBox("Actual 
> Control", "Enter Column Letter(s) of:", "L"))
>                         ColLtrControlBy = Trim(InputBox("Control Performed 
> By", "Enter Column Letter(s) of:", "O"))
>                         ColLtrRiskNumber = Trim(InputBox("Risk Number", 
> "Enter Column Letter(s) of:", "G"))
>                     End If
>                     Sheets(1).Activate
>                     Call ABBOneCellText
>                     ReceiveBook.Worksheets(ReceiveSheetName).Activate
>                     ActiveSheet.Range("A4").Value = Mid(DataSourceBookName, 
> 1, 12)
>                     ActiveSheet.Range("A4").Font.ColorIndex = 5
>                     ActiveSheet.Range("A6").Value = TextVar
>                     ActiveSheet.Range("A6").Font.ColorIndex = 5
>                     ActiveSheet.Range("A8").Formula = "=MID('[" & 
> DataSourceBookName & "]" & _
>                         DataSourceSheetName & "'!" & ColLtrControlNumber & 
> MyCell.Cells.Row & ",1,1)" '
>                     ActiveSheet.Range("A8").Font.ColorIndex = 5
>                     ActiveSheet.Range("A10").Formula = "='[" & 
> DataSourceBookName & "]" & _
>                         DataSourceSheetName & "'!" & ColLtrControlDescrip & 
> MyCell.Cells.Row
>                     ActiveSheet.Range("A10").Font.ColorIndex = 5
>                     ActiveSheet.Range("D4").Value = Mid(Now(), 1, 10)
>                     ActiveSheet.Range("D4").Font.ColorIndex = 5
>                     ActiveSheet.Range("F4").Formula = "='[" & 
> DataSourceBookName & "]" & _
>                         DataSourceSheetName & "'!" & ColLtrControlBy & 
> MyCell.Cells.Row
>                     ActiveSheet.Range("F4").Font.ColorIndex = 5
>                     ActiveSheet.Range("F8").Formula = "='[" & 
> DataSourceBookName & "]" & _
>                         DataSourceSheetName & "'!" & ColLtrRiskNumber & 
> MyCell.Cells.Row
>                     ActiveSheet.Range("F8").Font.ColorIndex = 5
>                     ActiveSheet.Range("I4").Formula = "='[" & 
> DataSourceBookName & "]" & _
>                         DataSourceSheetName & "'!" & ColLtrControlNumber & 
> MyCell.Cells.Row
>                     ActiveSheet.Range("I4").Font.ColorIndex = 5
>                     ReceiveBook.Worksheets(ReceiveSheetName).Select
>                     Cells.Select
>                     With Selection
>                        .Copy
>                        .PasteSpecial Paste:=xlPasteValues, 
> Operation:=xlNone, SkipBlanks:=False, Transpose:=False
>                     End With
>                     Application.CutCopyMode = False
>                     Range("A1").Select
>                 Next MyCell
>             End If
>         End With
>         If myOrigSheetProtectStatus = True Then
>             DataSourceSheet.Protect UserInterfaceOnly:=False
>         End If
>         If MsgBox("Press YES to Continue Processing Sheets", vbYesNo) = vbNo 
0
Dennis (299)
7/29/2005 2:59:12 PM
Glad you got it working the way you wanted.

(I don't envy your job with that SOX stuff.)

Dennis wrote:
> 
> Dave, (I hope that you read this!)
> 
> It took me a while to "perfect" this procedure.  Obviously I am not that
> good with VBA.  Saying that, this procedure and call to another works very
> well as intended.
> 
> As an accountant, my real work is accounting, audit, and Sarbanes-Oxley
> (SOX) tasks.  VBA is my hobby.
<<snipped>>
0
petersod (12005)
7/29/2005 11:12:07 PM
In two years of work with SOX, I found it very difficult to find people with 
a smile.

Thanks again!

Dennis

"Dave Peterson" wrote:

> Glad you got it working the way you wanted.
> 
> (I don't envy your job with that SOX stuff.)
> 
> Dennis wrote:
> > 
> > Dave, (I hope that you read this!)
> > 
> > It took me a while to "perfect" this procedure.  Obviously I am not that
> > good with VBA.  Saying that, this procedure and call to another works very
> > well as intended.
> > 
> > As an accountant, my real work is accounting, audit, and Sarbanes-Oxley
> > (SOX) tasks.  VBA is my hobby.
> <<snipped>>
> 
0
Dennis (299)
7/30/2005 12:54:03 AM
Reply:

Similar Artilces:

Problem inserting a column!!!
Hello all. I just accessed a worksheet which Im trying to add a column to... I highlight the column which i want the new column inserted ...and when i try to insert a column it doesnt work! A warning message comes up stating that a column cannot be inserted until blank cells are removed from the right side of the sheet or something to that effect!? Whats going on??? Thank you. -- londonchick ------------------------------------------------------------------------ londonchick's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24529 View this thread: http://www.e...

how do I insert a picture as a background
I am trying to insert a picture in my excel document but I cannot make it go behind the cells where I have already typed some figures and words Nadia Format>Sheet>Background... (It won't print though) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Nadia" <Nadia@discussions.microsoft.com> wrote in message news:AEC4DAE2-08D2-4067-BC00-A8FAB0F727DF@microsoft.com... >I am trying to insert a picture in my excel document but I cannot make it >go > behind the cells where I have already typed some figure...

Select rows meeting criteria into another worksheet?
Is this possible? Users will enter records of work into a GeneralEntry spreadsheet. Each record includes a Client field, which they fill using a dropdown. Records are entered as work is completed, so client rows are not grouped together. A set of additional worksheets, one for each client, automatically display only the rows from GeneralEntry which contain the client's name in the Client field. Each of the client sheets should perform summary calculations on just the rows appearing in the client sheet, and display the results above the detail rows. I have tried using filtered l...

insert worksheet
How do you insert a worksheet after the last worksheet? I just insert the sheet normally, by right-click > Insert > Sheet and this puts it one sheet to the left of my last sheet so then I just left-click on it and drag it over into the proper place at the right end..........if you are copying an entire sheet, then there is an option in the process allowing you to select where to put it...... Vaya con Dios, Chuck, CABGx3 "Katie" <Katie@discussions.microsoft.com> wrote in message news:28AD09FA-FF11-4E85-928B-EB973AFFFBD9@microsoft.com... > How do you insert a workshe...

Multiple choices for a cell
I am creating a worksheet which deals with employee or job candidates qualifications. Each cell will hold the various details pertaining to a person's qualifications. Different people have different degrees. This will be all stored in a single cell and separated by a comma. Is it possible to create some sort of custom or drop down box which contains controls or check boxes so as to create a standardised list of qualifications the data entry clerk can choose from? For example, i would like a cell to have a drop down box, which is a field that displays the qualifications a particular gra...

help with spliting data cells
I am looking for assistance with splitting data between cells. I have a column of data (first (space) last name). I need to split that column into two seperate columns so each name value is in it;s own cell and i end with 2 seperate columns. (john smith) to (john) (smith) Please advise if this is easy and can be done. Thanks, Phil pcavalcanto@Aol.com -- pcavalcanto ------------------------------------------------------------------------ pcavalcanto's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32212 View this thread: http://www.excelforum.com/showthread...

Select range for function in a cell
Hi, is it possible to select create a formula in a cell. such as =SUM( and have the cursor be between the () so the user can select th appopriate range to enter? Or would this just be done separately. (Since one would need to know the range and WHERE to put the formul in..ie. what cell) similar to if you hit the autosum button but ther are no numbers above or the left, it simply waits for the user t select a range and then hits enter. THanks! -- Message posted from http://www.ExcelForum.com Hi if I understood you correctly: No -- Regards Frank Kabel Frankfurt, Germany > Hi, is it p...

Filtering messages according to hidden html content
Hi all, I'm using Outlook 2002. I've been getting spam that my email provider fails to identify, and I couldn't find a rule myself using the built in rules wizard. I think I have a simple way of identifing those mails, but I need some help in applying it: they all contain links to the site harperety.com, however those words only appear in the mail's html source, and never in the presented text itself. What I would like to do, then, is have the wizard filter according to the source of the messages, rather than how they are presented. I would rather not use a third party tool ...

Copy and Paste in Excel, copies cell and formula, but shows same v
Excel 2003 with 2007 converters installed. Not constant problem, but have seen it once before, can't remember how to stop it happening. I have a bank forecast which I have a running total column, against a individual line item list. Noddy stuff. Problem. If I make changes to the forecast I need to recopy down the running total formula to refresh the running total when it doesn't pick up the additions, usual when a cut has been pasted in. Whilst it allows me to dragdown or copy and paste the formula and the new cells take on the correct line/column numbers to do thei...

Custom View: Keeping worksheet hidden?
(Excel 2003) I've got a workbook with several worksheets (some hidden). I'd like to use "custom views" to basically set the print/page setup for each worksheet---the setup varies per sheet. Custom view allows me to do this, but it also unhides the worksheets. Question: Is there anyway to keep a Custom View from hidding/unhiding a worksheet? I tried saving the view with the sheet hidden and unhidden, but when I select the view, it shows everything as the view was saved. Problem is, the worksheets that are hidden are unhidden by other events....the custom view is interfering ...

displaying a comment only when the cell is selected #2
I'd like to insert a comment in a certain cell, so that when that cell is selected by a user, the comment is then displayed. I know this must be possible, but I haven't been able to figure it out.... I can show or hide comments in various combinations, but can't find a way to hide the comment UNTIL the cell is selected, and then display it. How do I do this please?! Thanks Hi Jeff, Try: Tools Options | View tab | select 'Comment indicator only" | OK --- Regards, Norman "Jeff" <no_em@ilplea.se> wrote in message news:JdidnecOi7ehGVbfRVnyhg@eclip...

Last Cell #2
I know this has been asked before but I cant find what I want on Google. I need to get the address or row number of the first empty cell in column A, either will do, I need to use VB to copy and paste data from ten sheets into one. A formula or a VB way would be great, anyway of identifying the first empty cell. This cell may be empty or contain a null string. Any help would be reaaly good. TIA Alan. to find the last row x=cells(rows.count,"a").end(xlup).row+1 to find the next empty x=cells(1,1).end(xldown).row ' or address -- Don Guillett SalesAid Software donaldb@281.com &...

Use of normalised information
I have a table with company information, where the uniqe key is organisation number, which i have normalised on company code. In that new table on company code, i have givin each code a ranking(number) after how important that code is for me (e.g. stockcompany(SC) got 1, where i singleowner company(SOC) got a higher number) I now want a tabel where it is the phonenumber that is the uniqe key, and since the same phonenumber can have "many" differents organisation numbers, then i want the right companycode attached to the phonenumber (e.g. SC if one of the companies is a stock...

duplicate cells linearly?
Hello all - Is there a way to duplicate a formula in cells were the row reference grows linearly, instead of simply the number of cells? For instance, if you have in Sheet2 the formula Sheet1!$c1 and you duplicate it into rows below, you get Sheet1!$c2 Sheet1!$c3 Sheet1!$c4 Sheet1!$c5 However, we would like to do : Sheet1!$c4 Sheet1!$c8 Sheet1!$c12 We have a spreadsheet composed of two worksheets. In the first worksheet there are cells which are summed every 72 rows. We want those summed values to appear in worksheet 2, in one row after the other. Since we have a lot of summed cells, i...

insert data from one excel file into another
Is there any way to insert data from one excel file into another without doing copy and pasting? I need to automate the process of inserting data but I cannot use copy-paste, because it puts data on a clipboard, and my Excel VBA program runs in a multi-user environment. So if one instance of a program is copying data into clipboard, and another instance is pasting it at the same time, it will paste incorrect data. Thank you Leonard. You'll need both spreadsheets open, but try this; Option Explicit Sub MyMacro() Dim MyVariable As String Windows("File2.xls").Ac...

Cell Not working
I have office 2002, my windows is home xp. Last week we had to reformat my xp, got a hold of a nasty worm! I just use a chart to display numbers in series. Now when I try it from an old sheet it inserts the number as a date? I keep trying to change the cell to GENERAL, but it dosen't work. Can't understand it. When you say you keep trying to re-format the cell as General, what result do you get? If I do it on a date cell (re-format) I get the date code (i.e. the value of the date). Ex. cells has "03-01-11", select cell and re-format to General = 37632. -- Regards; Ro...

Transfer information into Excel spreadsheet
In Internet Explorer on a particular web page, I used to be able to click on file > Edit with Excel and then select the parts of the page I wanted to export. (By clicking on the small yellow box with a + in it. Now, my "edit" options are with Front Page or Word. What happened? I realize I can right click on the body of the page and select export to Excel, but I want to continue doing it the way I have for the past 6 months. ...

delete row if given cell = 'X'
Is there an easy way to do the above. Thanks much Carl "MSNews" <nf@no.com> wrote in message news:%23Fj6fBvPKHA.764@TK2MSFTNGP02.phx.gbl... > Is there an easy way to do the above. > > Thanks much > > Carl > I'm not an expert but use data filter to select the rows, select and delete them. Try it with a trial sheet first though! Peter Several ways 1. data>filter>autofilter>filter on the x>delete 2. a looping macro from the bottom up sub delifx() for i=cells(rows.count,"a").end(xlup).row to 2 step -1 if lcase(cells(i,&qu...

Insert Picture fails repeatedly in Word 2008 when working with labels
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel When trying to insert a picture (.tif, .jpg, .gif) into a text box on a label document, Word 2008 repeatedly fails, giving the message: "This application cannot open this file. This file is an unsupported graphic format or may be damaged. Try opening this graphic in another application". If I switch back to the regular (non-label) document, I can successfully insert the picture, then I was able to copy and paste it. But after a couple of times doing this, I received the message no matter which document I tried...

using two cells 1 with total time n 1 with date how can i put the.
I'm trying to put the amount of time spent in to a seperate sheet (Cell # 2 column E ) from a sheet that has the dates in one cell (column A) and the time spent in another (column K) and the reason for the time spent in column B. On the seperate sheet I have to put the amount of time spent in column E and match with the date I have in column A on the seperate sheet to populate the chart that I'm making. The only thing that I have been able to do is create a formula to show the amount of time spent and for what reason but i can't match it to the date because the dates ...

CRM Parameter Filter
Hi, Is it possible to bypass the Parameter Filter, so i can pass Non-CRM related data via the url to a form? So i can react to it in the OnLoad Event? It worked well on 3.0, but 4.0 runs a parameter filter and throws an error on unknown parameters. On Nov 14, 11:29 am, ckokely <christoph.kok...@ptm-edv.at> wrote: > Hi, > > Is it possible to bypass the Parameter Filter, so i can pass Non-CRM related > data via the url to a form? So i can react to it in the OnLoad Event? It > worked well on 3.0, but 4.0 runs a parameter filter and throws an error on > unknown param...

Automatically inserting symbols
Dear all I have created an Excel worksheet (97) and need to enter a "tick" in several cells. In Word, you can apply a short-cut to a symbol so you can enter it quickly but I can't find a similar function in Excel. Is it possible to use shortcuts and, if so, how do you create them? Thanks. Louise Here's a macro to do the job:- '--------------------------------------- Sub TICK() ActiveCell.Value = Chr(252) With ActiveCell.Font .Name = "Wingdings" .Size = 10 .Bold = True End With End Sub '---------------------------------------- --------------------...

details about quick+insertion provided in c library(1997)
hi i came to know that microsoft improved the efficiency of quick sort by using a cutoff of 8 elements and continuing with insertion sort then, do anybody have the details about it please contant me. i will try to find out <mahale.amit@gmail.com> wrote in message news:1144988612.281114.269970@z34g2000cwc.googlegroups.com... > hi > i came to know that microsoft improved the efficiency of quick sort > by using a cutoff > of 8 elements and continuing with insertion sort then, do anybody have > the details about it > please contant me. > You have the sou...

print title page setup/sheet can not enter cell in row/column lin
On page setup in the sheet page we can not enter cells into the print titles row or columns. Also the worksheet cell query on these lines will not open. Hi Are you trying do this from Print Preview page, or File|Page Setup? I think it's only possible from the latter. -- Andy. "don r" <don r@discussions.microsoft.com> wrote in message news:26C400D7-14A0-41F8-BBE6-8DF2F5579A30@microsoft.com... > On page setup in the sheet page we can not enter cells into the print > titles > row or > columns. Also the worksheet cell query on these lines will not open. &g...

insert memo feild into a memo field truncates at 255 characters -
Greetings I have a table that has a memo field to retain specific information - text - which is the base of the combo box. Via a combobox on a form this memo field is added to a forms field which is also sized as a memo called "Project_Notes" - this part works. At a certain time the user uploads this form and its feilds to a parent table which also has a memo field to recieve the data from the other memo field. 'we have created the new entry now we should update the notes strOtherFields = ",Action_By,To_Do_date" _ ...