Nothing Special   »   [go: up one dir, main page]
Scribd Logo
Upload

Welcome to Scribd!

  • 83 views

    Vba Code

    Uploaded by

    anarsaiah
    The code opens a file called "Q-SALES.xlsx" in read-only mode, copies data from its "Sheet1" to the active workbook's "Sheet1", closes the source file without saving, and includes error handling. It also contains additional unrelated code for opening workbooks from a file dialog or directly.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd

    Vba Code

    Uploaded by

    anarsaiah
    83 views6 pages
    The code opens a file called "Q-SALES.xlsx" in read-only mode, copies data from its "Sheet1" to the active workbook's "Sheet1", closes the source file without saving, and includes error handling. It also contains additional unrelated code for opening workbooks from a file dialog or directly.

    Original Description:

    Original Title

    vba code

    Copyright

    © © All Rights Reserved

    Available Formats

    TXT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The code opens a file called "Q-SALES.xlsx" in read-only mode, copies data from its "Sheet1" to the active workbook's "Sheet1", closes the source file without saving, and includes error handling. It also contains additional unrelated code for opening workbooks from a file dialog or directly.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    83 views6 pages

    Vba Code

    Uploaded by

    anarsaiah
    The code opens a file called "Q-SALES.xlsx" in read-only mode, copies data from its "Sheet1" to the active workbook's "Sheet1", closes the source file without saving, and includes error handling. It also contains additional unrelated code for opening workbooks from a file dialog or directly.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    of 6
    At a glance
    Powered by AI
    The code is copying data from one workbook to another by looping through rows and copying the formulas. It is opening a source file, getting the total rows, then copying each row of data to the destination file before closing the source file.

    The code is looping through each row, checking if column A is not empty and column O is empty, and if so it is copying that entire row from the active sheet to the past sheet. It finds the last used row to determine the paste location.

    New sheets can be added to a specific location using the .Add method along with the Before or After properties. Sheet names can be assigned as they are created. Functions can check if a sheet already exists.

    Option Explicit

    Private Sub Workbook_Open()


    Call ReadDataFromCloseFile
    End Sub

    Sub ReadDataFromCloseFile()
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    Dim src As Workbook

    ' OPEN THE SOURCE EXCEL WORKBOOK IN "READ ONLY MODE".


    Set src = Workbooks.Open("C:\Q-SALES.xlsx", True, True)
    https://t.me/s/bankniftysignals
    ' GET THE TOTAL ROWS FROM THE SOURCE WORKBOOK.
    Dim iTotalRows As Integer
    iTotalRows = src.Worksheets("sheet1").Range("B1:B" & Cells(Rows.Count,
    "B").End(xlUp).Row).Rows.Count

    ' COPY DATA FROM SOURCE (CLOSE WORKGROUP) TO THE DESTINATION WORKBOOK.
    Dim iCnt As Integer ' COUNTER.
    For iCnt = 1 To iTotalRows
    Worksheets("Sheet1").Range("B" & iCnt).Formula =
    src.Worksheets("Sheet1").Range("B" & iCnt).Formula
    Next iCnt

    ' CLOSE THE SOURCE FILE.


    src.Close False ' FALSE - DON'T SAVE THE SOURCE FILE.
    Set src = Nothing

    ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    End Sub

    Sub OpenWorkbook ()

    Dim strFile As String

    strFile = Application.GetOpenFilename()
    Workbooks.Open (strFile)

    End Sub

    Dim MyFile As String


    MyFile = Application.GetOpenFilename()
    Workbooks.Open (MyFile)

    1. The code line below closes close-open.xlsm.

    Workbooks("close-open.xlsm").Close
    2. The code line below closes the first opened/created workbook.

    Workbooks(1).Close

    3. The code line below closes the active workbook.


    ActiveWorkbook.Close
    4. The code line below closes all workbooks that are currently open.

    Workbooks.Close
    5. The code line below opens sales.xlsx.

    Workbooks.Open ("sales.xlsx")

    Sub OpenNewWorkbook()
    Dim wb As Workbook
    Set wb = Workbooks.Add
    End Sub

    Option Explicit

    Sub test()

    Dim varSheetA As Variant


    Dim varSheetB As Variant
    Dim strRangeToCheck As String
    Dim iRow As Long
    Dim iCol As Long

    strRangeToCheck = "A1:IV65536"
    ' If you know the data will only be in a smaller range, reduce the size of the
    ranges above.
    Debug.Print Now
    varSheetA = Worksheets("Sheet1").Range(strRangeToCheck)
    varSheetB = Worksheets("Sheet2").Range(strRangeToCheck) ' or whatever your
    other sheet is.
    Debug.Print Now

    For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)


    For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
    If varSheetA(iRow, iCol) = varSheetB(iRow, iCol) Then
    ' Cells are identical.
    ' Do nothing.
    Else
    ' Cells are different.
    ' Code goes here for whatever it is you want to do.
    End If
    Next iCol
    Next iRow

    Set wbkA = Workbooks.Open(filename:="C:\MyBook.xls")


    Set varSheetA = wbkA.Worksheets("Sheet1") .

    Sub Mover()

    Dim aSheet As Worksheet, pSheet As Worksheet


    Dim alr As Long, i As Long, plr As Long
    Set aSheet = ThisWorkbook.ActiveSheet
    Set pSheet = ThisWorkbook.Sheets("Sheet2") 'SET PASTE SHEET NAME HERE

    alr = aSheet.Cells(Rows.Count, 1).End(xlUp).Row 'FIND LAST ROW OF COLUMN A (1)

    For i = 2 To alr 'START FROM ROW 2 TO LAST ROW


    If aSheet.Cells(i, 1) <> "" And aSheet.Cells(i, 15) = "" Then 'IF A IS
    NOT EMPTY AND O IS EMPTY
    plr = pSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1 'FIND
    LAST ROW ON P SHEET
    aSheet.Rows(i).Copy Destination:=pSheet.Rows(plr) 'COPY
    FROM A SHEET TO P SHEET
    End If
    Next

    End Sub

    'Copy the data


    Sheets("Sheet1").Range("A1:B10").Copy
    'Activate the destination worksheet
    Sheets("Sheet2").Activate
    'Select the target range
    Range("E1").Select
    'Paste in the target destination
    ActiveSheet.Paste

    Application.CutCopyMode = False

    End Sub

    Sheets("sheet1").Range("C:E").Copy Sheets("sheet2").Range("G:I")

    ActiveSheet.Name = "NewSheet"
    Add Sheet with Name
    You can also define a Sheet name as you create the new Sheet:

    1
    Sheets.Add.Name = "NewSheet"
    Create New Sheet with Name from a Cell
    Or use a cell value to name a new Sheet:

    1
    Sheets.Add.Name = range("a3").value

    Add Sheet Before / After Another Sheet


    You might also want to choose the location of where the new Sheet will be inserted.
    You can use the After or Before properties to insert a sheet to a specific location
    in the workbook.

    Insert Sheet After Another Sheet


    This code will insert the new sheet AFTER another sheet:

    1
    Sheets.Add After:=Sheets("Input")
    This will insert a new Sheet AFTER another sheet and specify the Sheet name:

    1
    Sheets.Add(After:=Sheets("Input")).Name = "NewSheet"
    Notice the extra parenthesis required in the second example (the first example will
    generate an error if the second parenthesis are added).
    Add Sheet To End of Workbook
    To add a Sheet to the end of the workbook:

    1
    Sheets.Add After:=Sheets(Sheets.Count)

    Add Sheet To Beginning of Workbook:


    To add a Sheet to the beginning of the workbook:

    1
    Sheets.Add(Before:=Sheets(1)).Name = "FirstSheet"

    Add Sheet to Variable


    This code assigns the new Sheet to a variable as the sheet is created:

    1
    2
    Dim ws As Worksheet
    Set ws = Sheets.Add
    From here you can reference the new sheet with the variable �ws�:

    1
    ws.name = "VarSheet"

    More Add Sheet Examples


    Create Sheet if it Doesn�t Already Exist
    You might want to create a sheet only if it doesn�t already exist.

    Create Worksheets From List of Names


    The following routine will look at the contents of a single column set up Excel
    worksheets within the current workbook with these names. It makes a call to another
    function to see if a sheet with that name already exists, and if so the sheet isn�t
    created.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    Private Sub CommandButton1_Click()

    Call CreateWorksheets(Sheets("Sheet2").Range("A1:a10"))

    End Sub

    Sub CreateWorksheets(Names_Of_Sheets As Range)


    Dim No_Of_Sheets_to_be_Added As Integer
    Dim Sheet_Name As String
    Dim i As Integer

    No_Of_Sheets_to_be_Added = Names_Of_Sheets.Rows.Count

    For i = 1 To No_Of_Sheets_to_be_Added

    Sheet_Name = Names_Of_Sheets.Cells(i, 1).Value

    'Only add sheet if it doesn't exist already and the name is longer than zero
    characters

    If (Sheet_Exists(Sheet_Name) = False) And (Sheet_Name <> "") Then


    Worksheets.Add().Name = Sheet_Name
    End If

    Next i

    End Sub
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    Function Sheet_Exists(WorkSheet_Name As String) As Boolean
    Dim Work_sheet As Worksheet

    Sheet_Exists = False
    For Each Work_sheet In ThisWorkbook.Worksheets

    If Work_sheet.Name = WorkSheet_Name Then


    Sheet_Exists = True
    End If

    Next

    End Function

    Sub FileOpenDialogBox()

    'Display a Dialog Box that allows to select a single file.


    'The path for the file picked will be stored in fullpath variable
    With Application.FileDialog(msoFileDialogFilePicker)
    'Makes sure the user can select only one file
    .AllowMultiSelect = False
    'Filter to just the following types of files to narrow down selection
    options
    .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
    'Show the dialog box
    .Show

    'Store in fullpath variable


    fullpath = .SelectedItems.Item(1)
    End With

    'It's a good idea to still check if the file type selected is accurate.
    'Quit the procedure if the user didn't select the type of file we need.
    If InStr(fullpath, ".xls") = 0 Then
    Exit Sub
    End If

    'Open the file selected by the user


    https://www.tradingview.com/chart/3FV80PHt/
    Workbooks.Open fullpath

    End Sub

    You might also like