Macros VBA to open and copies range from another workbook

APML

Board Regular
Joined
Sep 10, 2021
Messages
216
Office Version
  1. 365
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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
VBA Code:
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
 
Upvote 0
Hi, firstly thanks for your help. I tried it but got this:
1662358324790.png
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

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