Copy Data from my current active workbook to a closed workbook

[Martin sherk]

Hello,

I need help copying data from my current active workbook(Code is in another workbook) to a closed file

My current active worksheet which I will copy data from has the name "Customers Data" sheet

My destination path for the closed workbook is C:\1.Martin\1.Work\Customers\October 2022
My destination workbook is: Customer Raw data.xlsx
My destination worksheet is: Customers Data

Can someone help construct a VBA to do the above, I will be thankful.

 

[dmt32]

Hi,
would be helpful if you could share copy of Customers Data input worksheet using MrExcel Addin: XL2BB - Excel Range to BBCode

Dave

 

[Martin sherk]

@dmt32 you are right, sorry

Here we go

Rec_Export_with_Attlasachment_Status9_39.csv
	A	B	C	D	E	F	G	H
1	Invoice no.	Company	Payee	Currency	Amount	Issue date	Due date	Avaliable
2	50411254	USA1	ENGD1	USD	1,002,558.00	25.10.2022	30-Jun-22	FALSE
3	50411254	USA2	ENGD2	USD	1,002,558.00	25.10.2023	31-Jul-22	FALSE
4	50411254	USA3	ENGD3	USD	1,002,558.00	25.10.2024	30-Nov-22	FALSE
5	50411254	USA4	ENGD4	USD	1,002,558.00	25.10.2025	25-Sep-22	FALSE
6	50411254	USA5	ENGD5	USD	1,002,558.00	25.10.2026	25-May-22	FALSE
7	50411254	USA6	ENGD6	USD	1,002,558.00	25.10.2027	25-May-22	FALSE
8	50411254	USA7	ENGD7	USD	1,002,558.00	25.10.2028	25-May-22	FALSE
9	50411254	USA8	ENGD8	USD	1,002,558.00	25.10.2029	25-May-22	FALSE
10	50411254	USA9	ENGD9	USD	1,002,558.00	25.10.2030	25-May-22	FALSE
11	50411254	USA10	ENGD10	USD	1,002,558.00	25.10.2031	25-May-22	FALSE
12	50411254	USA11	ENGD11	USD	1,002,558.00	25.10.2032	25-May-22	FALSE
13	50411254	USA12	ENGD12	USD	1,002,558.00	25.10.2033	25-May-22	FALSE
14	50411254	USA13	ENGD13	USD	1,002,558.00	25.10.2034	25-May-22	FALSE
15	50411254	USA14	ENGD14	USD	1,002,558.00	25.10.2035	25-May-22	FALSE
16	50411254	USA15	ENGD15	USD	1,002,558.00	25.10.2036	25-May-22	FALSE
Sheet1

 

[dmt32]

@dmt32 you are right, sorry

Is the destination sheet identical & you want to paste the whole range to next blank row?

Dave

 

[Martin sherk]

Is the destination sheet identical & you want to paste the whole range to next blank row?

Dave

Book2
	A	B	C	D	E	F	G	H
1	Invoice no.	Company	Payee	Currency	Amount	Issue date	Due date	Available
2
3
4
5
6
7
8
9
10
11
12
13
14
Customer Main data

Hello Dave,

the destination sheet has a table as in the above screenshot, so I need to copy data from A2 to H2 after the header
also, I need to delete any data in the destination sheet except for the header before copying the new data to the destination sheet

 

[Martin sherk]

Book2
	A	B	C	D	E	F	G	H
1	Invoice no.	Company	Payee	Currency	Amount	Issue date	Due date	Available
2
3
4
5
6
7
8
9
10
11
12
13
14
Customer Main data

Hello Dave,

the destination sheet has a table as in the above screenshot, so I need to copy data from my sheet to the destination sheet in A2 to H2 after the header
also, I need to delete any data in the destination sheet except for the header before copying the new data to the destination sheet

 

[dmt32]

Hi,
only lightly tested but see if this code will do what you want

Place in a STANDARD module

Sub SubmitData()
    Dim FullName            As String
    Dim wsCustomersData     As Worksheet
    Dim wbCustomerRawData   As Workbook
    Dim CopyFromRange       As Range, CopyToRange As Range
    
    Const FilePath As String = "C:\1.Martin\1.Work\Customers\October 2022\"
    Const FileName  As String = "Customer Raw data.xlsx"
    
    FullName = FilePath & FileName
    
    Set wsCustomersData = ThisWorkbook.Worksheets("Customers Data")
    'size the copy range (sheet must not be protected)
    Set CopyFromRange = wsCustomersData.Range("A1").CurrentRegion
    
    On Error GoTo myerror
    If Not Dir(FullName, vbDirectory) = vbNullString Then
        Application.ScreenUpdating = False
        Set wbCustomerRawData = Workbooks.Open(FullName, 0, False)
        'size to destination range
        Set CopyToRange = _
        wbCustomerRawData.Sheets(1).Cells(1, 1).Resize(, CopyFromRange.Columns.Count)
        
        'ensure header values match
        CopyToRange.Value = CopyFromRange.Rows(1).Value
        
        'copy data to sheet
        CopyFromRange.AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=CopyToRange
        
    Else
        'file not found
        Err.Raise 53
    End If
    
myerror:
    If Not wbCustomerRawData Is Nothing Then wbCustomerRawData.Close CBool(Err = 0)
    Application.ScreenUpdating = True
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
    
End Sub

I have assumed that the destination worksheet in the closed file is the first (or only) sheet in the workbook.

Dave

 

[Martin sherk]

Hi,
only lightly tested but see if this code will do what you want

Place in a STANDARD module

Sub SubmitData()
    Dim FullName            As String
    Dim wsCustomersData     As Worksheet
    Dim wbCustomerRawData   As Workbook
    Dim CopyFromRange       As Range, CopyToRange As Range
   
    Const FilePath As String = "C:\1.Martin\1.Work\Customers\October 2022\"
    Const FileName  As String = "Customer Raw data.xlsx"
   
    FullName = FilePath & FileName
   
    Set wsCustomersData = ThisWorkbook.Worksheets("Customers Data")
    'size the copy range (sheet must not be protected)
    Set CopyFromRange = wsCustomersData.Range("A1").CurrentRegion
   
    On Error GoTo myerror
    If Not Dir(FullName, vbDirectory) = vbNullString Then
        Application.ScreenUpdating = False
        Set wbCustomerRawData = Workbooks.Open(FullName, 0, False)
        'size to destination range
        Set CopyToRange = _
        wbCustomerRawData.Sheets(1).Cells(1, 1).Resize(, CopyFromRange.Columns.Count)
       
        'ensure header values match
        CopyToRange.Value = CopyFromRange.Rows(1).Value
       
        'copy data to sheet
        CopyFromRange.AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=CopyToRange
       
    Else
        'file not found
        Err.Raise 53
    End If
   
myerror:
    If Not wbCustomerRawData Is Nothing Then wbCustomerRawData.Close CBool(Err = 0)
    Application.ScreenUpdating = True
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
   
End Sub

I have assumed that the destination worksheet in the closed file is the first (or only) sheet in the workbook.

Dave

Appreciate the time taken preparing this, Thanks, Dave.
please note that I have 3 sheets in the destination sheet, so i need a way where i can specify which sheet to copy data to.

 

[dmt32]

Appreciate the time taken preparing this, Thanks, Dave.
please note that I have 3 sheets in the destination sheet, so i need a way where i can specify which sheet to copy data to.

either make the destination sheet the first sheet in the workbook or to specify a name, change this line

Set CopyToRange = _
        wbCustomerRawData.Sheets(1).Cells(1, 1).Resize(, CopyFromRange.Columns.Count)

to this

Set CopyToRange = _
        wbCustomerRawData.WorkSheets("Customer Main Data").Cells(1, 1).Resize(, CopyFromRange.Columns.Count)

Change the sheet name in RED as required

Dave

 

[Martin sherk]

either make the destination sheet the first sheet in the workbook or to specify a name, change this line

Set CopyToRange = _
        wbCustomerRawData.Sheets(1).Cells(1, 1).Resize(, CopyFromRange.Columns.Count)

to this

Set CopyToRange = _
        wbCustomerRawData.WorkSheets("Customer Main Data").Cells(1, 1).Resize(, CopyFromRange.Columns.Count)

Change the sheet name in RED as required

Dave

It worked Flawlessly !! time saved thanks to you.

one last request please, it copies data to the destination file but after row 1000, the data copied is without table format, can i fix that?