Skip to main content

Add Record & Save Record into another Microsoft Excel Sheet

I want macro only select that records from Sno 1 to 10 which have code and ONLY Transfer values to anther sheet in excel ?





Below is a VBA macro designed to copy only the values (not formulas) from rows with a valid Code (non-empty in the "Code" column) for Sno 1 to 10 from the source sheet and paste them into the target sheet.


Sub CopyValidRecords()

    Dim SourceSheet As Worksheet, TargetSheet As Worksheet

    Dim LastRow As Long, TargetRow As Long

    Dim i As Long


    ' Set worksheets

    Set SourceSheet = ThisWorkbook.Sheets("Source") ' Replace "Source" with your source sheet name

    Set TargetSheet = ThisWorkbook.Sheets("Target") ' Replace "Target" with your target sheet name


    ' Find the next empty row in the target sheet

    TargetRow = TargetSheet.Cells(TargetSheet.Rows.Count, "A").End(xlUp).Row + 1


    ' Loop through Sno 1 to 10 in the source sheet

    For i = 1 To 10

        ' Check if Code column is not empty (Column "C")

        If SourceSheet.Cells(i, "C").Value <> "" Then

            ' Copy values of the entire row to the target sheet

            TargetSheet.Cells(TargetRow, "A").Resize(1, 7).Value = SourceSheet.Cells(i, "A").Resize(1, 7).Value

            ' Increment the target row

            TargetRow = TargetRow + 1

        End If

    Next i


    MsgBox "Records copied successfully!", vbInformation

End Sub


How It Works

  1. Source Sheet:

    • The macro loops through rows 1 to 10.
    • It checks if the "Code" column (column "C") is not empty.

  2. Target Sheet:

    • Copies only values of the rows that have a non-empty "Code" to the next available row in the target sheet.

  3. Dynamic Column Handling:

    • The macro assumes data spans from columns A to G (Invoice, Sno, Code, Description, Qty, Unit Price, Amount).
    • Adjust .Resize(1, 7) if more or fewer columns are needed.

  4. No Formulas:

    • Only the final values (not formulas) are pasted into the target sheet.

Steps to Implement

  1. Open your Excel workbook.
  2. Press Alt + F11 to open the VBA editor.
  3. Go to Insert > Module and paste the macro code.
  4. Replace "Source" and "Target" with your actual sheet names.
  5. Close the editor and press Alt + F8.
  6. Run the macro CopyValidRecords.

Example Input

Source Sheet (Name: Source):

css
| Invoice | Sno | Code | Description           | Qty | Unit Price | Amount |
|---------|-----|------|-----------------------|-----|------------|--------|
| 4       | 1   | G005 | Hoodie               | 55  | 10.00      | 550.00 |
| 4       | 2   | G006 | Crewneck Sweatshirt  | 80  | 24.00      | 1920.00|
| 4       | 3   | G009 | Bomber Jacket        | 40  | 24.00      | 960.00 |
| 4       | 4   | G005 | Hoodie               | 10  | 10.00      | 100.00 |
| 4       | 5   | G007 | Cardigan             | 6   | 10.00      | 60.00  |
| 4       | 6   |      |                      |     |            |        |

Example Output

Target Sheet (Name: Target):

css
| Invoice | Sno | Code | Description           | Qty | Unit Price | Amount |
|---------|-----|------|-----------------------|-----|------------|--------|
| 4       | 1   | G005 | Hoodie               | 55  | 10.00      | 550.00 |
| 4       | 2   | G006 | Crewneck Sweatshirt  | 80  | 24.00      | 1920.00|
| 4       | 3   | G009 | Bomber Jacket        | 40  | 24.00      | 960.00 |
| 4       | 4   | G005 | Hoodie               | 10  | 10.00      | 100.00 |
| 4       | 5   | G007 | Cardigan             | 6   | 10.00      | 60.00  |

Let me know if you have further questions! 😊



Labels:
Location: India

Comments

Post a Comment

Popular posts from this blog

Spell Number Function

  How to Convert Number into Words in MS Excel | Spell Number in Excel | MS Excel Tips & Tricks @1 In Microsoft Excel, you can't directly write a function like a traditional programming language. However, you can use  VBA (Visual Basic for Applications)  to create custom functions, like a "spell" function, which converts numbers into words. Here's an example of how to write a  VBA spell function  that converts numbers to words in Excel: Step-by-step guide to create the function: Open Excel. Press  Alt + F11  to open the  VBA editor . In the VBA editor, click  Insert  >  Module . Paste the following code into the module:   Function SpellNumber(ByVal MyNumber)     Dim Units As String     Dim Cents As String     Dim DecimalPlace As Integer     Dim Count As Integer     Dim Place(9) As String     Dim Hundreds As String          Pl...
1 comment
Read more

Number convert to words

Number convert to words  Virtual Learning Earning Channel Steps to Add the Macro Open Excel and press ALT + F11 to open the VBA Editor . Click Insert > Module . Copy and paste the code below into the module. Close the VBA Editor and return to Excel. Use the function in a cell like =NumberToWords(A1) . VBA Code for Digit-by-Digit Conversion Function NumberToWords(ByVal MyNumber As String) As String     Dim i As Integer     Dim Result As String     Dim Digit As String          ' Loop through each digit in the number     For i = 1 To Len(MyNumber)         Digit = Mid(MyNumber, i, 1)         Select Case Digit             Case "0": Result = Result & " Zero"             Case "1": Result = Result & " One"             Case "2": Result = Result & " Two"     ...
Post a Comment
Read more

Automatically update work sheets based on the Account field

        To automatically update sheets based on the Account field, you can use VBA in Excel. This script will check the Account field in your main sheet and transfer rows to the corresponding sheet for each account. Here's how to set it up: . Using VBA (Fully Automated Solution) If you want the data to be copied automatically whenever you enter it in the main sheet, you can use a VBA script. Steps to Implement VBA: Press Alt + F11 to open the VBA editor. Go to Insert > Module and paste the following code: VBA Script for Automatic Sheet Updates Sub UpdateAccountSheets()     Dim wsMain As Worksheet     Dim wsAccount As Worksheet     Dim lastRow As Long, i As Long     Dim accountName As String     Dim accountSheet As Worksheet     Dim accountLastRow As Long     Dim lastProcessedRow As Long          ' Set the main worksheet     Set wsMain = ThisWorkbook.Sheets("M...
Post a Comment
Read more