VBA: Select Used Range

[SteveOranjin]

Hello,

I am attempting to write a macro that selects a used range, and pastes it into a new document. What I want the macro to do is move all the data that is in one sheet, into another. I don't want to copy the entire sheet though, as that is putting unneccessary stress on the computer.

Option Explicit
Sub Import() 'Excel VBA to Open a signle file and import data


'We are defining our variables.
Dim owb As Workbook
Dim Sh As Worksheet


Set Sh = Sheet1
'open a file called (england)
Set owb = Workbooks.Open("C:\Users\Data Coordinator I\Desktop\England\England")






owb.Sheets("Sheet1").Range("A1:F100").Copy
Sh.Range("A1").PasteSpecial xlPasteValues
owb.Close False




End Sub

 

[JoeMo]

1. Add a file extension to your owb file path.
2. owb.Sheets("Sheet1").UsedRange.Copy

 

[SteveOranjin]

Got it! Nvm

 

[SteveOranjin]

Do you know how to set it up so I can import the file in regardless of what the name of the file is?