copy and paste

hdgfss

New Member
Joined
Aug 29, 2019
Messages
41
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top