Results 1 to 3 of 3

Thread: copy and paste
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default copy and paste

    Copy entire complete row between Signup Date word and === this sign and paste that data to abc.xlsx(note aabc.xlsx file is not opened we have to open the same by vba and also note that "Signup Date" word and "===" sign will be in column A and we have to copy entire row between this )
    and also make a note there can be a chance it has two signup date
    example
    signup Date
    XYZ A B C D
    123


    A/AB/C
    ===




    SIGNUP DATE
    854 E
    HDFD F
    DJF G






    =====
    So in this case copy both the data and paste it to aabc.xlsx
    vba will be placed in a seprate file both the files are located in same place
    open the file by vba do the process and save and close the file by vba
    so plz have alook sir and do needful

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,060
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: copy and paste

    A few questions about where you want to paste the data.

    Where in the "aabc.xls" file do you want this information copied to (what shet/range)?
    Is there already data/headings in this file?
    If so, do you want it posted directly below what is there, or should it skip a row?
    If there are multiple sections to copy, do you want one copied below the other, and do you want to skip any rows in between?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,060
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: copy and paste

    OK, I made some assumptions, and came up with some code for you.
    I have the Source file ("aabc.xls") hard-coded into the VBA code. You will want to edit the location and name of this file to match your requirements.
    It will prompt you to select the Source File
    I assume that both files only have one sheet.
    I assume that we are copying to the nextg available row in column A on the Destination sheet.

    Here is the code that I came up with:
    Code:
    Sub MyCopyMacro()
    
        Dim srcWB As Workbook
        Dim destWB As Workbook
        Dim srcFN As Variant
        Dim destFN As String
        Dim lr As Long, sr As Long, er As Long
        Dim r As Long
        
        Application.ScreenUpdating = False
    
    '   Open file browser to open source file and restrict to Excel files
        srcFN = Application.GetOpenFilename("Excel Files (*.xl*),*.xl*", , "Choose File", "Open", False)
        If srcFN = False Then
            MsgBox "You have not selected a source file", vbOKOnly, "ERROR!"
            Exit Sub
        Else
            Workbooks.Open (srcFN)
            Set srcWB = ActiveWorkbook
        End If
    
    '   Set destination workbook
        destFN = "C:\TEMP\aabc.xls"
    
    '   Open destination file and set it to workbook object reference
        Workbooks.Open Filename:=destFN
        Set destWB = ActiveWorkbook
        
    '   Loop through all rows in column A in Source file
        srcWB.Activate
        lr = Cells(Rows.Count, "A").End(xlUp).Row   'find last row in column A with data
        For r = 1 To lr
            If UCase(Trim(Cells(r, "A"))) = "SIGNUP DATE" Then
                sr = r
            Else
                If Trim(Cells(r, "A")) = "===" Then
                    er = r
    '               Copy rows to destination sheet
                    Rows(sr & ":" & er).Copy
                    destWB.Activate
                    Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
                    ActiveSheet.Paste
                    Application.CutCopyMode = False
                    srcWB.Activate
                End If
            End If
        Next r
                
    '   Close workbooks
        srcWB.Close
        destWB.Activate
        destWB.Save
        destWB.Close
        
        Application.ScreenUpdating = True
        
        MsgBox "Copy complete!", vbOKOnly
        
    End Sub
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •