Macros VBA to open and copies range from another workbook

[APML]

Hi All, Let me start by saying that I'm not very good at macros or VBA.
I have the following code which opens a CSV file named 1a.csv (which is in C:\CSVToday) and copies range A4 : G2000 to my current workbook
What I'm trying to achieve is:
Once the macro is triggered then it asks me where and what the file to copy the range from
Once it has copied the range (into my currently opened workbook), then it closes the file without saving any changes

In the future, the file being copied will be an excel file and not a CSV. (but won't contain any formulas or formatting), I just want to copy values.

Would really appreciate any help


Sub Open1A_CopyData()
'
' Open1A_CopyData Macro
Workbooks.Open "C:\CSVToday\1a.csv"
Workbooks("1a.csv").Sheets("1a").Range("A4:G2000").Copy
ThisWorkbook.Sheets("TodaysData").Range("C4").PasteSpecial Paste:=xlPasteValues
ActiveWindow.WindowState = xlMinimized
Range("A1").Select
MsgBox ("Data Has Been Updated")
End Sub

 

[Zot]

When you opened any workbook, it will become active workbook. So, I believe the statement ThisWorkbook refers to 1a.csv, not the workbook you are pasting to.

So, it is good practice to define workbooks so that it is easy to refers to later. The code can be consolidate to make it shorter but this step by step will make it easy to understand.

Here is my untested code which will open/filter to excel (old and new) and csv files

Sub CopyData()

Dim FName As Variant
Dim rngSource As Range
Dim wsSource As Worksheet, wsDest As Worksheet
Dim wbSource As Workbook, wbDest As Workbook

Application.ScreenUpdating = False

' Define this macro workbook as wbDest
Set wbDest = ActiveWorkbook
' Define destination worksheet. Rename if necessary
Set wsDest = wbDest.Sheets("Sheet1")

' Select Source workbook
FName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx, *.csv), *.xls; *.xlsx; *.csv", Title:="Select a File")
If FName = False Then Exit Sub                         'CANCEL is clicked
' Define workbook to be copied from as wbSource while opening it
Set wbSource = Workbooks.Open(Filename:=FName, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
' Define working sheet in wbSource. Change sheet name if required
Set wsSource = wbSource.Sheets("Sheet1")
' Define range of Source data to copy
Set rngSource = wsSource.Range("A4:G2000")

' Copy rngSource and paste to wsDest
rngSource.Copy wsDest.Range("C4").PasteSpecial(xlPasteValues)

Application.ScreenUpdating = True

End Sub

 

[APML]

Hi, firstly thanks for your help. I tried it but got this:

 

[Zot]

Hi, firstly thanks for your help. I tried it but got this:
View attachment 73227

It is the workbook you installed the macro in. The statement is to define worksheet in it which is Sheet1 in this case. If you don't have a sheet called by that name, then you get error. Change to your destination sheet name. Same it goes to the source sheet name.