Macro to open workbook and paste data

[howard]

I have written code to open a file and copy the data from sheet1 and paste in on sheet "Imported Data"

I need the code amended so that I do not need to delete sheets2


Your assistance is most appreciated

 Sub Open_Workbook()
ChDir ("C:\downloads")
Sheets(2).Delete

Dim nb As Workbook, tw As Workbook, ts As Worksheet, TheFile As String, ThePath As String
ThePath = "C:\extract\"
TheFile = Dir(ThePath & "PTAW*Sales" & "*.csv")



'If TheFile = False Or IsEmpty(TheFile) Then Exit Sub
With Application
    .ScreenUpdating = False
    End With
Set tw = ThisWorkbook
Set ts = tw.ActiveSheet
Set nb = Workbooks.Open(TheFile)
nb.Sheets(1).Copy After:=Workbooks("PTAW BR1 Sales.xlsm").Sheets(1)
 Sheets(2).Name = "Imported Data"
 ChDir ("C:\my Documents")
 
End Sub

 

[gitmeto]

Did you try just removing the line "Sheets(2).Delete" ?

 

[howard]

I did try that earlier. However, when the data is copied to the destination workbook, it inserts a new sheets

I want to copy the data from sheet1 on the source darta workbook and paste this on sheet "Imported Data"

Kindly amend my code accordingly

 

[gitmeto]

Have you looked at creating a connection between the two workbooks using Power Query?

 

[howard]

Have not looked at using PQ

 

[gitmeto]

I started working with it a bit ago and it saves you from writing a lot of VBA. The name makes it sound daunting but it is actually fairly easy to use. You just go into the destination workbook, click on the data tab and go through the wizard to extract the data from the workbook that has the source data. Once thing is that the source data (I believe) must be in a table format.

 

[howard]

it is very easy but how does it update when source data changes ?

 

[MARK858]

Hi howard, you click the Refresh All button on the data tab

 

[howard]

Thanks. I imported the Data using Power Query (on Data Tab , I selected Get Data from File> From Text/CSV) . However If I change the data in the source file and select Refresh All the data does not change in the destination workbook

 

[gitmeto]

Can you click on the "Queries and Connections" button and then hover over the query to see if there are any error messages?