Open a Workbook and Transfer Data to a New Sheet in Excel VBA - CODE INCLUDED

Code and Downloadable File
chrisjterrell.com/blog/226224...
Free Quick Reference Guide
www.chrisjterrell.com/excel-v...
This video will be looking at opening a file within Excel VBA using the file selector. Now it's super easy to do this, especially if you have the file path. It's easy to do it if you're using the file dialog. But what fun is that? So I'm going to show you how to open a file, go and grab some data from that file, add a new sheet and then take that data from the file that you just opened and put that in the original file. It is more fun, and you'll learn a little bit more in the process.
So let's dig in and get started. We have two Macros. The first shows you how to use the filedialog. We use Application.getOpenFile to get the string of the file you are looking to open. In this code, we are only showing the xlsx files because of this sting "Excel File Only, *.xlsx" and the title of the window is "Select a File to Open." The first thing we do is show you how "cancel" is handled. Clicking "Cancel returns "False." We use an if statement to exit the sub if the string is equal to "false." Next, we pick a file, open the file and then close it.
Now the magic happens. We open a workbook and get data from the first worksheet. Then we add a worksheet to the original workbook and copy the data. First, we declare objects which are going to be all my base objects. I dim my objects so they can be assigned. To do this, we need three objects that for "Thisworkbook" and we need two for the workbook we open.
To get the dialog of the file picker, we use Application.GetOpenFileName so we can select the file. We need to use an if statement that will exit the macro if the user clicks "Cancel" on the file dialog.
We do two things when we open the file, we open the file and assign it to our workbook object. Once the workbook is open, we set our range equal to the data in the first sheet of the workbook using the "CurrentRegion."
The next step is to add a new sheet for the data and rename it using a timestamp. We make the ranges the same size by using the resize function, and then we set the range on the opened workbook equal to the range on the new sheet we just added.
And we are done!!
'======CODE===========================
Sub OpenFile()
Filename = Application.GetOpenFilename("Excel File Only, *.xlsx", 1, "Select A file to Open")
Debug.Print Filename
If Filename = False Then
'RUclips doesn't like less than or greater than
Exit Sub
Else
'No Parenthesis because we are not assigning this to an obect
Workbooks.Open Filename
End If
MsgBox "This workbook has been open " & ActiveWorkbook.Name
ActiveWorkbook.Close
End Sub
Sub OpenFileMoveData()
Dim wb As Workbook 'For the Original Workbook
Dim ws As Worksheet 'For the newly Added workbook
Dim rng As Range 'For the range on the new workbook
Dim nwb As Workbook
Dim nrng As Range
Set wb = ThisWorkbook
Filename = Application.GetOpenFilename("Excel File Only, *.xlsx", 1, "Select A file to Open")
Debug.Print Filename
If Filename = False Then
Exit Sub
End If
'This uses parethesis because you are assigning the object
Set nwb = Workbooks.Open(Filename)
nrng = nwb.Sheets(1).CurrentRegion
Set ws = wb.Sheets.Add
ws.Name = Format(Date, "MM-DD-YY HH MM SS") 'Rename the Sheet to be a timestamp
Set rng = ws.Cells(1, 1).Resize(nrng.Rows.Count, nrng.Columns.Count) 'Set the range size equal to the size of the openned workbook
'show your work :)
wb.Activate
ws.Activate
rng.Select
rng = nrng 'THE MAGIC - this is equivalent to copying
End Sub

[@kebincui]

Excellent video👍

[@Ivon_vlog]

a life saver....

[@muhammadsuleman6179]

Hi i hope you are fine and good.
i have question?
kindly reply
I have VBA Macro excel file. it have a button in it. when i press the button it automatically get data from website and put it excel sheet with date.
but unfortunately the file is not working now. when i run the file in other laptop it works fine.
can you solve the problem to run the file in first computer?

[@EverydayVBAExcelTraining]

Those are tough ones. Does it give you an error or does it just not work.
Please provide any lines of code that are causing the error

[@user-sw9vm9je1b]

How to select another sheet instead of first sheet

[@EverydayVBAExcelTraining]

You can name the sheet like
Sheets("sheet name")
Or by index
Sheets(2). 'the second sheet in the workbook

[@darray7472]

Where are you?
Why did you stop posting?

[@EverydayVBAExcelTraining]

My life got busy. I actually don't like doing the thumbnails etc.
Does it matter

[@darray7472]

I have learned a lot from your videos. I hope you will post again some day. Thanks for your reply.