Free Microsoft Excel 2013 Quick Reference

Record Selection

Hi Everybody

I am stuck on what am sure is a simple problem but I just can’t see the answer
My user will select a range of cells, I want to record this selection and
re-activate it later. How can I do this?

Many Thanks


Hi All

I'm running a macro that creates an autofilter with the criteria blank. I'm having a probelm where the rows in the spreadsheet contain data but are blank in the criteria column.

Does anyone know what is the code to extract the number of records selected by the autofilter? It is showing in the bottom left corner of the Excel screen but I can't get it into a VB command.

Thanks

Paul

Hi,

I have a excel file which is used for the data entry purpose. I need a solution for the record selects to be highlighted which will be known that the entry is completed. When i click the down arrow key the particular cell is to be highlighted. Is it possible??

Thanks in advance

ravi

Hi all,

I wonder if anyone can help with this problem, basically I have an Access query which when executed gives me records in a table in the following format:

RecordName1 (6 records)
RecordName2 (5 records)
RecordName3 (2 records)

How can re-programme the query to select only the first 3 records of each "RecordName".

I hope this makes sense,

TonyJ

I am populating my ListBox using ADO from an access database. In my listbox I have 5 columns of data. When multiple records are selected the listbox populates fine with the data spread across the 5 columns. My problem is when I select a single record. All my data ends up complied into column one of my list box.

Here is an example of what is happening:

Column1 Column2 Column3 Column4 Column5 Column6
1
Honeywell
Intrusion
Contacts
Contact
10

I want it to look like:

Column1 Column2 Column3 Column4 Column5 Column6
1 Honeywell Intrusion Contacts Contact 10

Here is the code I am using:


	VB:
	
 ListBox2_Click() 
    Dim cnt As ADODB.Connection 
    Dim rst As ADODB.Recordset 
    Dim stDB As String, stConn As String, stSQL As String 
    Dim vaData As Variant 
    Dim k As Long 
     
     'Instantiate the Connection object.
    Set cnt = New ADODB.Connection 
     'Path to and the name of the database.
     'Assumes the DB and worksheet are in same directory.
     'If not, you need to specify DB path
    stDB = ThisWorkbook.Path & "" & "Items.mdb" 
     
     'Create the connection string.
    stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & stDB & ";" 
     
     'Create the SQL-statement.
    stSQL = "SELECT * FROM ItemsTable WHERE Manufacturer = '" & ItemSelection.ListBox1.Value & "'  AND GenericCategory =  '"
& Me.ListBox2.Value & "' ;" 
     
    With cnt 
        .CursorLocation = adUseClient 'Necesary for creating disconnected recordset.
        .Open stConn 'Open connection.
         'Instantiate the Recordsetobject and execute the SQL-statement.
        Set rst = .Execute(stSQL) 
    End With 
     
    With rst 
        Set .ActiveConnection = Nothing 'Disconnect the recordset.
        k = .Fields.Count 
         'Populate the array with the whole recordset.
        vaData = .GetRows 
    End With 
     
     'Close the connection.
    cnt.Close 
     
     'Manipulate the Combobox's properties and show the data.
    With Me 
        With .ListBox3 
            .Clear 
            .BoundColumn = k 
            .ColumnCount = 6 
            .List = Application.Transpose(vaData) 
            .ListIndex = -1 
        End With 
    End With 
     
     'Release objects from memory.
    Set rst = Nothing 
    Set cnt = Nothing 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks...

Hi All, would anyone have any suggestions to solve the following problem please.

I have a simple list of some 4000 cases, each of which need auditing on a regular basis. In order to be audited they need to be selected and allocated to an auditor.

The question is, rather than manually selecting cases for audit, is there some automated function/code that would select entirely at random say 100 cases, and maybe flag the selected records in adjacent cells to indicate that they had been selected and therefore exempted from selection on the next run?

Im not holding my breath, but if you guys cant do it I know it cant be done!

Cheers

Simon

Sometimes when I filter on my spreadsheets it gives me the records that I have selected e.g. 50 records (100) but frequently this isn't present. Is there anyway of 'switching' this function on so that I can see the number of records on a filtered spreadsheet?

Cheers

Hi

I have a problem with autofilter on a particular spreadsheet - usually when an autofilter is applied to the data and a criteria is selected the status bar shows a line stating the number of records selected from the range eg "6 of 8 records selected". On the problem spreadsheet all that is displayed is a message saying "Filter mode" how can I toggle the record display back on or is it something to do with my data ?

M

Hi there
My Search macro is doing evrything it is supposed to do except it keeps selecting
the sheet (Records) when the inputbox is called for. Sheet (Display) is the sheet I want to have always on display.
If I use Sheets("Display").Select instead of Sheets("Records").Select
I cannot trigger the second macro.
Please have a look at the attached book and advise me where I am wrong.
Thanks for your help in advance.

	VB:
	
 
Sub Search() 
    On Error Resume Next 
    Dim rgFound 'rgFound var to hold row number for the cell containing
     ' date searched for.
    Dim Qry As Date 'Qry var to hold date searched for
     
    Sheets("Records").Select 
    Columns("A2:A").Select 'defines range to be used for the search.
     
    Qry = Application.InputBox(Prompt:="Please Type in Date" & Chr(13) _ 
    & "Format: dd/mm/yyyy", Title:="Search") 
     
     'Search takes place here:
    Cells.Find(What:=Qry, After:=ActiveCell, _ 
    LookIn:=xlFormulas, _ 
    LookAt:=xlPart, _ 
    SearchOrder:=xlByRows, _ 
    SearchDirection:=xlNext, _ 
    MatchCase:=False, _ 
    SearchFormat:=False).Activate 
     'The cell of date searched for is
     'activated then is passed to var rgFound
     
     
     
    rgFound = Selection.Row 'triggers the CallRecords
    Range("DisplayRec") = rgFound 'Sheets("Display").Range("B8")
    Sheets("Display").Range("C15") = Selection 'date searched for goes here
     
     
     
    CallRecords 
     
    Sheets("Display").Select 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


HI,
I have excel spreadsheet with approx 30K records.
Using below criteria I have to generate unique records list
with single entries only. But first , how to sanitize data at
earlier stage and modify it before seeking required output.

Qstns:
1. How to remove any trailing white spaces in entire worksheet.

2. Given RAW INPUT, convert into MODIFIED INPUT
- RAW INPUT has duplicate entries in the sense
A --> Z and Z --> A information is same.
So modified input must have ONLY ONE record.

3. Required Output, must leave blank if no data is available.

Required OUTPUT:
----------------
VEG d.e.1 STL d.e.1 -
VEG a.b.0 LAX a.b.0 100
MSP f.g.0 VEG f.g.0 -
STL c.d.1 MSP c.d.1 250
VEG g.h.1 MSP g.h.1 -

MODIFIED INPUT:
---------------
VEG a.b.0 LAX a.b.0 100
STL c.d.1 MSP c.d.1 250

RAW INPUT:
----------
VEG a.b.0 LAX a.b.0 100
LAX a.b.0 VEG a.b.0 100
MSP c.d.1 STL c.d.0 250
STL c.d.1 MSP c.d.0 250

Appreciate help, Brgds.

I am trying to create a macro that will generate Regression results using the
Regression function (by applying it to pre-established input data). I am
using the Record MAcro utility and going through the steps to create a simple
regression analysis. The "Record Macro" utility is not capturing the
definitions for Y, X, and output area that are entered into the Regression
Dialog box. How do I get the Macro to capture the definitions for X, Y, and
Output area?

Hi there,

I am analysing some research data and I am trying to count the number of occurrences of a specific criterion after it fulfils another criterion over multiple cell ranges. So for instance, selecting records that meet criterion (a) and within those records, selecting records that meet criterion (i). I have been trying to use countif but it cannot handle the complexity. Is there a way to achieve the same result?

Thanks

Hello everyone, I am new to this forum, I need a macro where I can update particular record in Access table from Excel worksheet. I have a macro where I can append/Add new records in Access table from Excel work sheet :

Private Sub CommandButton2_Click()

'Author : Ken Puls (www.excelguru.ca)
'Macro purpose: To add record to Access database using ADO and SQL
'NOTE: Reference to Microsoft ActiveX Data Objects Libary required

Dim cnt As New ADODB.Connection, _
rst As New ADODB.Recordset, _
dbPath As String, _
tblName As String, _
rngColHeads As Range, _
rngTblRcds As Range, _
colHead As String, _
rcdDetail As String, _
ch As Integer, _
cl As Integer, _
notNull As Boolean

'Set the string to the path of your database as defined on the worksheet
dbPath = ActiveSheet.Range("B1").Value
tblName = ActiveSheet.Range("B2").Value
Set rngColHeads = ActiveSheet.Range("tblHeadings")
Set rngTblRcds = ActiveSheet.Range("tblRecords")

'Concatenate a string with the names of the column headings
colHead = " ("
For ch = 1 To rngColHeads.Count
colHead = colHead & rngColHeads.Columns(ch).Value
Select Case ch
Case Is = rngColHeads.Count
colHead = colHead & ")"
Case Else
colHead = colHead & ","
End Select
Next ch

'Open connection to the database
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & dbPath & ";"

'Begin transaction processing
On Error GoTo EndUpdate
cnt.BeginTrans

'Insert records into database from worksheet table
For cl = 1 To rngTblRcds.Rows.Count

'Assume record is completely Null, and open record string for concatenation
notNull = False
rcdDetail = "('"

'Evaluate field in the record
For ch = 1 To rngColHeads.Count
Select Case rngTblRcds.Rows(cl).Columns(ch).Value
'if empty, append value of null to string
Case Is = Empty
Select Case ch
Case Is = rngColHeads.Count
rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) & "NULL)"
Case Else
rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) & "NULL,'"
End Select

'if not empty, set notNull to true, and append value to string
Case Else
notNull = True
Select Case ch
Case Is = rngColHeads.Count
rcdDetail = rcdDetail & rngTblRcds.Rows(cl).Columns(ch).Value & "')"
Case Else
rcdDetail = rcdDetail & rngTblRcds.Rows(cl).Columns(ch).Value & "','"
End Select
End Select
Next ch

'If record consists of only Null values, do not insert it to table, otherwise
'insert the record
Select Case notNull
Case Is = True
rst.Open "INSERT INTO " & tblName & colHead & " VALUES " & rcdDetail, cnt
Case Is = False
'do not insert record
End Select
Next cl

EndUpdate:
'Check if error was encounted
If Err.Number <> 0 Then
'Error encountered. Rollback transaction and inform user
On Error Resume Next
cnt.RollbackTrans
MsgBox "There was an error. Update was not succesful!", vbCritical, "Error!"
Else
On Error Resume Next
cnt.CommitTrans
End If

'Close the ADO objects
cnt.Close
Set rst = Nothing
Set cnt = Nothing
On Error GoTo 0
End Sub

Please help me in building a macro where I can update field "StatusRemarks" where "VendorCode" and "RecdDate" matches the value.

I have attached my excel file with same fields as in my Access table.

Your help will be highly appreciated.

Thanks in advance

I started the macro recorder, selected Sheet1, Protect, Password then Sheet 2 and repeated. Read the script and no mention of the code for password on and password text!

Hello everyone. What does the
mean, and how do I get a cell to do that when I am recording a macro? For example, I want the cell A1 to say 
	

	
, how do I do that?

I noticed that the macro recorder selects the sheets in a worbook by name. This is fine as long as the sheet names remain constant. Is there any way to select a given sheet in a workbook in a macro without naming it? Like perhaps "prior sheet" or something like that?

Thanks,
LouP

PS. in case that was cofusing, I am always starting on the first worksheet and would like to copy the formats on the worksheet preceeding it but the name of that workshhet will always be changing as the name of the sheet is the date it was added to the file.

Hi, I am hoping someone can help me get the right code to update a record in Access 2010. I have this code which works great for creating a new Record. However I would also like to Edit and Delete records but my efforts have so far failed and my brain is becoming gloopy. My Access Table has a Column for "Recordset_ID" so this would be the unique identifier for the record.

I've tried incorporate a statement like UPDATE Analyst_Data SET Analyst_Name='Tom Lancaster' WHERE Recordset_ID=1 but this returned an error about a missing parameter value.

The code I am using to add a record is below - any help on adjusting this to update and delete a record will be very much appreciated.

Sub
DB_Insert_via_ADOSQL()
    Dim cnt As New ADODB.Connection, _
            rst As New ADODB.Recordset, _
            dbPath As String, _
            tblName As String, _
            rngColHeads As Range, _
            rngTblRcds As Range, _
            colHead As String, _
            rcdDetail As String, _
            ch As Integer, _
            cl As Integer, _
            notNull As Boolean, _
            sConnect As String

    'Set the string to the path of your database as defined on the worksheet
    dbPath = "E:AdminAutomationStacksheetAnalyst DataAnalyst Data.accdb"
    tblName = "Analyst_Data"
    Set rngColHeads = ActiveSheet.Range("tbl_Headings")
    Set rngTblRcds = ActiveSheet.Range("tbl_Records")

    'Set the database connection string here
    sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & dbPath & "';"     'For use
with *.accdb files

    'Concatenate a string with the names of the column headings
    colHead = " ("
    For ch = 1 To rngColHeads.Count
        colHead = colHead & rngColHeads.Columns(ch).Value
        Select Case ch
            Case Is = rngColHeads.Count
                colHead = colHead & ")"
            Case Else
                colHead = colHead & ","
        End Select
    Next ch

    'Open connection to the database
    cnt.Open sConnect
    'Begin transaction processing
''''    On Error GoTo EndUpdate
    cnt.BeginTrans

    'Insert records into database from worksheet table
    For cl = 1 To rngTblRcds.Rows.Count
        'Assume record is completely Null, and open record string for concatenation
        notNull = False
        rcdDetail = "('"

        'Evaluate field in the record
        For ch = 1 To rngColHeads.Count
            Select Case rngTblRcds.Rows(cl).Columns(ch).Value

                    'if empty, append value of null to string
                Case Is = Empty
                    Select Case ch
                        Case Is = rngColHeads.Count
                            rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) & "NULL)"
                        Case Else
                            rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) & "NULL,'"
                    End Select

                    'if not empty, set notNull to true, and append value to string
                Case Else
                    notNull = True
                    Select Case ch
                        Case Is = rngColHeads.Count
                            rcdDetail = rcdDetail & rngTblRcds.Rows(cl).Columns(ch).Value & "')"
                        Case Else
                            rcdDetail = rcdDetail & rngTblRcds.Rows(cl).Columns(ch).Value & "','"
                    End Select
            End Select
        Next ch

        'If record consists of only Null values, do not insert it to table, otherwise
        'insert the record
        Select Case notNull
            Case Is = True
                rst.Open "INSERT INTO " & tblName & colHead & " VALUES " & rcdDetail,
cnt
            Case Is = False
                'do not insert record
        End Select
    Next cl

EndUpdate:
    'Check if error was encounted
    If Err.Number <> 0 Then
        'Error encountered.  Rollback transaction and inform user
        On Error Resume Next
        cnt.RollbackTrans
        MsgBox "There was an error.  Update was not succesful!", vbCritical, "Error!"
    Else
        On Error Resume Next
        cnt.CommitTrans
    End If

    'Close the ADO objects
    cnt.Close
    Set rst = Nothing
    Set cnt = Nothing
    On Error GoTo 0
End Sub


I think that I only need one row of code.

First, I have 10 columns where the user can select 1 of - depending on the users need. The actual columns are column 3 thru column 12.

Let's say that the user selects column 6 (which would be cell G11).

The starting code of the macro already has the records selected that will be sorted.

The next step of the macro is to simply sort the selection by column 6.

I don't have any idea on how to sort by column 6, when the next selection might be column 3 or whatever.

All help will be appreciated.

http://www.excelforum.com/excel-prog...o-another.html

Hello All,

I'm still looking for help with the thread above. I need "Validation Worksheet" to always include the results of the autofilter of "checklog" and then make unique, random choices from "checklog" until "D3" is reached. See Sub RANDOM() .

With the attached, there needs to be 6 records selected (5% of 114), with one mandatory item to review (in green on "checklog").

Thanks for these ideas, guys.

Bob

I have a listing of schools, some records selected with a "Y". When all of the desired schools have been selected, I want to run a macro that will scan through those cells and, for each one that is selected, put the school name into a string. I want that resultant string to be written to a cell.

Here's an example of the data:

    DHS
Y   East
     McCowan
     West
     Amber Terrace
     Cockrell Hill
     Moates
Y   Northside
     Ruby Young
Y   The Meadows
     Woodridge
The resultant string should then read "East, Northside, The Meadows"

TIA

Bob

Firstly apologies if this is elsewhere on the site, i did look, i am new to excel and dont really know where to start at all.

What i am looking to do is search using a user input and select all the rows that contain the entered search text.

I have 4 sheet workbook, columns are the same accross them all, and what i require is to be able to take a text input from a user, maybe what column to search, and in what sheet. Then search the relevent sheet for matches. Once found the record that contains the match is to be copied to another sheet where filters can e applied. There will be multiple records selected for each search.

I hope this makes sense.

Any advice or pointers to decent tutorials or templates would be much appreciated.

I am trying to create a macro that will generate Regression results using the
Regression function (by applying it to pre-established input data). I am
using the Record MAcro utility and going through the steps to create a simple
regression analysis. The "Record Macro" utility is not capturing the
definitions for Y, X, and output area that are entered into the Regression
Dialog box. How do I get the Macro to capture the definitions for X, Y, and
Output area?

Dear All, I would be grateful for some help on the following.

I have a large table which will be autofiltered in different ways by several users. The results of each filter presented to the user as a form or report. It is important that all users should know which records in their filter have been already filtered by a colleague.
I had thought to do this by a bit of code such as :-

x = range("C3").CurrentRegion.Rows.Count
 For count = 0 to x
       If range("C3").Offset(count,0).Hidden <> True Then
               Range("c3").Offset(count,0).value = "Listed"
        End If
 Next count
This does not work, can anyone point me in the right direction
John

Hi,
I have a bit of code linked with a button in a Excel form that copy and insert some results into a spreadsheet named Records.
The code itself worked fine and I have managed to save about 700 rows until this error came out: " Excel cannot complete this task with available resources. Choose less data or close application". I have closed all the applications running, appart Excel and still does not work. I have looked into the spreadsheet where it saves the data and found about 12000 rows empty and about 16.000 columns and apart those 16 columns that I use all where empty.
Any suggestions..?? The coloured bit of code is what the debugger highlighted...

Code is as follows:

	VB:
	
 CommandButton6_Click() 
     
    If Not ListBox1 = "Planned" And Not ListBox1 = "Actual" Then 
        MsgBox "Please select 'Actual' or 'Planned'", vbInformation & vbOKOnly, "Invalid Selection" 
        Goto endsub1 
    End If 
    If Not Cells(24, 9) = Empty Then 
        notes1 = Cells(24, 9) 
    End If 
    If ListBox1 = "Planned" Then 
        depot = Cells(1, 20).Value 
        Application.DisplayAlerts = False 
        On Error Resume Next 
        Workbooks.Open "d:local cloudsharedtransporttransport documentspoints systemrecords.xlsm" 
        Workbooks.Open "z:transport documentspoints systemrecords.xlsm" 
        On Error Goto 0 
        Application.DisplayAlerts = True 
        Workbooks("records.xlsm").Activate 
        Worksheets("records").Activate 
        Sheets("Records").Select 
        Sheets("Records").Rows("2:2").Select 
         
        [COLOR=#ff0000]Selection.Insert Shift:=xlDown[/COLOR] 
         
        Workbooks("Copy of Points System.xlsm").Activate 
        Sheets("Dustcart Summary").Select 
        Range("A25:F25").Select 
        Selection.Copy 
        Workbooks("records.xlsm").Activate 
        Sheets("Records").Select 
        Sheets("Records").Range("A2:F2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
        :=False, Transpose:=False 
        Sheets("Records").Range("A2:F2").Font.Bold = False 
        Sheets("Records").Rows("2:2").EntireRow.AutoFit 
        Sheets("Records").Range("A2:F2").HorizontalAlignment = xlCenter 
        Sheets("Records").Range("A2").Select 
        Selection.NumberFormat = "m/d/yyyy" 
        Sheets("Records").Range("A1").Select 
        Workbooks("Copy of Points System.xlsm").Activate 
        Sheets("Dustcart Summary").Select 
        Application.CutCopyMode = False 
        Range("A1").Select 
        Workbooks("records.xlsm").Activate 
        Worksheets("Records").Range("D2:E2").NumberFormat = "0" 
        Worksheets("Records").Cells(2, 10) = "=IF(LEN(DAY(A2))=1,0&DAY(A2),DAY(A2))" 
        Worksheets("Records").Cells(2, 11) = "=IF(MONTH(A2)>9,MONTH(A2),0&MONTH(A2))" 
        Worksheets("Records").Cells(2, 12) = "=YEAR(A2)" 
        Worksheets("Records").Cells(2, 13) = "=VLOOKUP(K2,Months3,2,FALSE)" 
        Worksheets("Records").Cells(2, 7) = depot 
        Worksheets("Records").Cells(2, 16) = "Dustcart" 
        Worksheets("Records").Cells(2, 14) = "=ABS(D2-H2)" 
        Worksheets("Records").Cells(2, 15) = "=H2-D2" 
        Worksheets("Records").Cells(2, 9) = notes1 
        Sheets("Records").Range("A2:J2").Font.Bold = False 
        ActiveWorkbook.Save 
        ActiveWorkbook.Close 
        Goto endsub1 
    End If 
    If ListBox1 = "Actual" Then 
        Application.DisplayAlerts = False 
        On Error Resume Next 
        Workbooks.Open "d:local cloudsharedtransporttransport documentspoints systemrecords.xlsm" 
        Workbooks.Open "z:transport documentspoints systemrecords.xlsm" 
        On Error Goto 0 
        Application.DisplayAlerts = True 
        Workbooks("records.xlsm").Activate 
        Worksheets("Records").Protect DrawingObjects:=False, Contents:=False, Scenarios:=False 
        Worksheets("Records").Visible = xlSheetVisible 
        Worksheets("Records").Columns("A:P").AutoFilter 
        Worksheets("Records").Columns("A:P").AutoFilter Field:=1, Criteria1:=Workbooks("Copy of Points
System.xlsm").Worksheets("Dustcart Summary").Cells(12, 1).Value 
        Worksheets("Records").Columns("A:P").AutoFilter Field:=2, Criteria1:=Workbooks("Copy of Points
System.xlsm").Worksheets("Dustcart Summary").Cells(6, 1).Value 
        Workbooks("Copy of Points System.xlsm").Activate 
        Sheets("Dustcart Summary").Select 
        Worksheets("Dustcart Summary").Range("D25").Select 
        Selection.Copy 
        Workbooks("records.xlsm").Activate 
        Sheets("Records").Select 
        Sheets("Records").Range("H1").Select 
        Selection.End(xlDown).Select 
        If Selection = "Actual Points" Then 
            Goto errorhandle 
        End If 
        Selection.PasteSpecial xlPasteValues 
        Selection.NumberFormat = "0" 
        Sheets("Records").Range("I1").Select 
        Selection.End(xlDown).Select 
        If Not ActiveCell = Empty Then 
            notes2 = ActiveCell 
            ActiveCell = notes2 & ", " & notes1 
        Else 
            ActiveCell = notes1 
        End If 
        Worksheets("Records").AutoFilterMode = False 
        Sheets("Records").Range("A2:F2").HorizontalAlignment = xlCenter 
        Sheets("Records").Range("A2:F2").Font.Bold = False 
        ActiveWorkbook.Save 
        ActiveWorkbook.Close 
        Goto endsub1 
    End If 
errorhandle: 
    MsgBox "Data could not be found", vbInformation & vbOKOnly, "Error" 
    Workbooks("records.xlsm").Saved = True 
    Workbooks("records.xlsm").Activate 
    ActiveWorkbook.Close 
    Goto endsub1 
endsub1: 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Many thanks

Hi, I am very new to VB and Macro writing. I have created a locked form in one worksheet with various cells to be filled with info. the macro copies each cell then pastes in another worksheet along a row, then clears the form ready for the next entry. I need this marco to be run multiple times with each paste going into the next row down on the destination sheet. I have tried a few ways but not getting it. Below is the macro. Could anyone please help me complete this? Happy to give further information if requried.

Sub Test()
'
' Test Macro
'
' Keyboard Shortcut: Ctrl+Shift+T
'

Range("C6").Select
Selection.Copy
Sheets("Records").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Form").Select
Range("C8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Records").Select
Range("D2").Select
ActiveSheet.Paste
Sheets("Form").Select
Range("C10").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Records").Select
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Form").Select
Range("C12").Select
Application.CutCopyMode = False
Selection.Copy
Range("C12").Select
Sheets("Records").Select
Range("F2").Select
ActiveSheet.Paste
Sheets("Form").Select
Range("C14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Records").Select
Range("G2").Select
ActiveSheet.Paste
Sheets("Form").Select
Range("G10").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Records").Select
Range("E2").Select
ActiveSheet.Paste
Sheets("Form").Select
Range("G12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Records").Select
Range("B2").Select
ActiveSheet.Paste
Sheets("Form").Select
Range("G14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Records").Select
Range("H2").Select
ActiveSheet.Paste
Sheets("Form").Select
Range("C6:C14").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("G10:G14").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("C18").Select
Selection.Copy
Sheets("Records").Select
Range("I2").Select
ActiveSheet.Paste
Sheets("Form").Select
Range("G18").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Records").Select
Range("J2").Select
ActiveSheet.Paste
Sheets("Form").Select
Range("C18").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Records").Select
Range("I2").Select
ActiveSheet.Paste
Sheets("Form").Select
Range("C20").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Records").Select
Range("K2").Select
ActiveSheet.Paste
Sheets("Form").Select
Range("G20").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Records").Select
Range("L2").Select
ActiveSheet.Paste
Sheets("Form").Select
Range("C22").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Records").Select
Range("M2").Select
ActiveSheet.Paste
Sheets("Form").Select
Range("G22").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Records").Select
Range("N2").Select
ActiveSheet.Paste
Sheets("Form").Select
Range("C24").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Records").Select
Range("O2").Select
ActiveSheet.Paste
Sheets("Form").Select
Range("G24").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Records").Select
Range("P2").Select
ActiveSheet.Paste
Sheets("Form").Select
Range("C18:C24").Select
Application.CutCopyMode = False
Selection.ClearContents
Sheets("Form").Select
Range("G18:G24 ").Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveWindow.SmallScroll Down:=18
Range("F44").Select
ActiveWindow.SmallScroll Down:=-30

End Sub