Import data into seperate sheets
 

Hi guys,

The piece of code that i am using here, imports data from 3 externa
files into one worksheet. Could someone tell me how the code would loo
if i were to put the data from each file into seperate worksheets?

Sub Open3_v2()
Dim i As Integer

'Clear out the existing data
Worksheets("Data").UsedRange.EntireRow.Delete

'Get the 3 data files
For i = 1 To 3
OpenFile
Next
End Sub

Sub OpenFile()
Dim strFile As String
Dim wbkData As Workbook

'Locate the file - quit if user presses cancel
strFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls"
, "Select file", "OK")
If Dir(strFile) = "" Then Exit Sub

'Open the file
Set wbkData = Workbooks.Open(strFile)

'Copy all the data from the one sheet
wbkData.Activate
ActiveSheet.UsedRange.Select
Selection.Copy

'Come back to this workbook and paste the data at the bottom
ThisWorkbook.Activate
Worksheets("Data").Activate
Cells(ActiveSheet.UsedRange.Rows.Count + 1, 1).Select
ActiveSheet.Paste

'Close the data file
wbkData.Close False

'Free up used space in memory
Set wbkData = Nothing
End Sub

Thanks in advance,
Cathal

--
Message posted from http://www.ExcelForum.com

Import data into seperate sheets
 

off the information you provided:

if you have 3 sheets Data1, Data2, Data3 then set the OpenFile Sub t
accept a parameter:

e.g. Sub OpenFile(iCount as integer)

then send the Sub the value of 'i' when it is called

e.g OpenFile i
or Call OpenFile (i)

then within the Sub use the value to access the specific sheet.

Worksheets("Data" & iCount).Activate

Hope this helps

Tanner Dhes

--
Message posted from http://www.ExcelForum.com

Import data into seperate sheets
 

Tried doing this but im still a begginer at this and it would not work
can u please show me where that code goes in??

Thanks.
Cathal

--
Message posted from http://www.ExcelForum.com

Import data into seperate sheets
 

Hi

The 3 worksheets that you are going to import to must be named Data1
Data2 and Data3 for this to work. I've altered the code you poste
below and marked the changed lines with - '###:
'-------------------------------------------------------------------
Sub Open3_v2()
Dim i As Integer

'Clear out the existing data
Worksheets("Data").UsedRange.EntireRow.Delete

'Get the 3 data files
For i = 1 To 3
'### the i is added on the end of this statement and sent to th
OpenFile sub
OpenFile i
Next
End Sub

'### the variable iCount was added here to take the value sent to thi
sub
Sub OpenFile(iCount as Integer)
Dim strFile As String
Dim wbkData As Workbook

'Locate the file - quit if user presses cancel
strFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls",
"Select file", "OK")
If Dir(strFile) = "" Then Exit Sub

'Open the file
Set wbkData = Workbooks.Open(strFile)

'Copy all the data from the one sheet
wbkData.Activate
ActiveSheet.UsedRange.Select
Selection.Copy

'Come back to this workbook and paste the data at the bottom
ThisWorkbook.Activate
'### the iCount value is appended onto the word 'Data' to create th
name of the worksheet and it will be incremented each time the OpenFil
sub is called
Worksheets("Data" & iCount).Activate
Cells(ActiveSheet.UsedRange.Rows.Count + 1, 1).Select
ActiveSheet.Paste

'Close the data file
wbkData.Close False

'Free up used space in memory
Set wbkData = Nothing
End Sub

'-------------------------------------------------------------------
Hope that's clearer

Regard

--
Message posted from http://www.ExcelForum.com

Import data into seperate sheets
 

Thanks a million for this, but it is givin me an error '9' saying th
script is out of range on the first line Sub open3_v2() line
"Worksheets("Data").UsedRange.EntireRow.Delete "
So i changed the name of sheet 1 from data 1 to just data and i
imported the data from the first file, but then it gave an error on th
line: "Worksheets("Data" & iCount).Activate" before it gave me th
option to open the sceond file, how do i go about solving this?

--
Message posted from http://www.ExcelForum.com

Import data into seperate sheets
 

I solved this by renaming sheet 1 as 'Data', sheet 2 as 'Data1', sheet
as 'Data2' and sheet 4 as 'Data3'. but this is too much trouble and i
puts nothing into the sheet called 'Data'. Is there any way of makin
this work without havin to change the names of the worksheets at all
i.e. leave them as sheet1, sheet2, sheet3?

--
Message posted from http://www.ExcelForum.com

Import data into seperate sheets
 

Sorry - my fault, i didn't read all the code properly. i've now move
the line that clears out the existing data into the loop as you shoul
have 3 worksheets that need clearing. you will need the worksheet
named Data1, Data2 & Data3 (with no space between the word an
number):
'-------------------------------------------------------------------
Sub Open3_v2()
Dim i As Integer

'######## this for loop has changed to include the clearing of data
'Get the 3 data files
For i = 1 To 3
'#####
'Clear out the existing data
Worksheets("Data" & i).UsedRange.EntireRow.Delete
'### the i is added on the end of this statement and sent to th
OpenFile sub
OpenFile i
Next
End Sub

'### the variable iCount was added here to take the value sent to thi
sub
Sub OpenFile(iCount as Integer)
Dim strFile As String
Dim wbkData As Workbook

'Locate the file - quit if user presses cancel
strFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls",
"Select file", "OK")
If Dir(strFile) = "" Then Exit Sub

'Open the file
Set wbkData = Workbooks.Open(strFile)

'Copy all the data from the one sheet
wbkData.Activate
ActiveSheet.UsedRange.Select
Selection.Copy

'Come back to this workbook and paste the data at the bottom
ThisWorkbook.Activate
'### the iCount value is appended onto the word 'Data' to create th
name of the worksheet
'### and it will be incremented each time the OpenFile sub is called
Worksheets("Data" & iCount).Activate
Cells(ActiveSheet.UsedRange.Rows.Count + 1, 1).Select
ActiveSheet.Paste

'Close the data file
wbkData.Close False

'Free up used space in memory
Set wbkData = Nothing
End Sub

'-------------------------------------------------------------------
Regard

--
Message posted from http://www.ExcelForum.com

Import data into seperate sheets
 

Sorry should know that myself (just change 'data' to 'sheet' in th
code!!) For some reason it is copying the code over and leaving row on
blank how can i change this from happening?

--
Message posted from http://www.ExcelForum.com

Import data into seperate sheets
 

Again i worked this out, (really should think before i ask!!!).

This has been great help, thanks ever so much,

Regards,
Cathal

--
Message posted from http://www.ExcelForum.com