Two Workbooks, Same Names and Different extensions; Copy and Paste if Sheets are MATCHED

rodrigo_m_almeida

New Member
Joined
Jan 13, 2022
Messages
42
Office Version
  1. 2021
Platform
  1. Windows
Does anyone have any ideas ?
How to do this

I'm trying with
VBA Code:
Public Sub ABC123()
    ' Optimize
        Application.ScreenUpdating = False: Application.DisplayAlerts = False
    ' Declare Variables
        Dim xPathName As Variant, xFileName01, xFileName02 As String, xOldWB, xNewWB As Workbook
    ' Browse File
        Set FileDialog = Application.FileDialog(msoFileDialogFolderPicker)
        FileDialog.Title = "Select Folder :"
        If FileDialog.Show = -1 Then
                xPathName = FileDialog.SelectedItems(1)
            Else
                Exit Sub
        End If
        If Right(xPathName, 1) <> "\" Then xPathName = xPathName + "\"
    ' Search Extension
        xFileName01 = Dir(xPathName & "*.xls")
        xFileName02 = Dir(xPathName & "*.csv")
        Do While xFileName01 <> ""
            ' Open File 01 (XLS)
                Workbooks.Open Filename:=xPathName & xFileName01
                Set xOldWB = ActiveWorkbook
            ' Open File 02 (CSV)
                Workbooks.Open Filename:=xPathName & xFileName02, Delimiter:=";", Local:=True
                Set xNewWB = ActiveWorkbook
            ' Copy And Paste
                xOldWB.Range("A" & Rows.Count).End(xlUp).Row.Copy
                xNewWB.Range("A" & Rows.Count).End(xlUp)(2).Row.PasteSpecial (xlPasteValues)
            ' Save File
                xNewWB.SaveAs Filename:=xPathName & xFileName01, FileFormat:=xlCSV, Local:=True
            ' Close File
                xOldWB.Close
                xNewWB.Close
            ' New Search
                xFileName01 = Dir
        Loop
    ' Optimize
        Application.ScreenUpdating = True: Application.DisplayAlerts = True
End Sub

If Worksheets have the same name and different extensions,
open files copy all XLS content to paste and save in CSV in the last blank line...

My Problem...

I'm not able to copy and paste and not have idea how to work with the same names workbooks
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Forum statistics

Threads
1,214,588
Messages
6,120,409
Members
448,959
Latest member
camelliaCase

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