Import Data from 2 Workbooks in the same folder

[steve]

Hi All,

I would appreciate some assistance with the following:

1. I have three excel workbooks in a folder. The name of the first
workbook starts with the letter "P". The second workbook's name
starts with "S" and the third is called Daily Recon.

2. Workbooks P and S represent data dumps from two different systems.
The Daily Recon consolidates the data dumpts from the P and S
workbooks.

3. Each day these three files are saved in a new folder named after
the day the data dumps correspond to. For example Mar 1, Mar 2 etc.

4. I would like to add a button in the Daily Recon workbook called
"Import" which will import the data on the first worksheet of the
workbook named "P" starting at row 10 to column J and down for
as many rows as there is data into a worksheet in the Daily Recon
workbook called "P". At the same time the code should also import
the data from the "S" workbook from the first worksheet but starting
at row 1 to column m and down for as many rows as there is data into
a worksheet named "S" in the Daily Recon workbook.

5. The Daily Recon workbook will already have two worksheets called
"P" and "S" so there is no need to insert new sheets during the import
processes.

6. I am using Excel 2002 on windows XP.

Any ideas on how to accomplish the above would be greatly appreciated.

Thanks,

Steve

 

[Joel]

I don't know your expertise in VBA or Excel. This is a starting point and I
will assit as needed by replying to my message.

The code below is a good starting point for doing your copying. It give an
example of finding the last row with data and how to copy from one file to
another. Get this code working then add a button.

The button gets added by going to the View Menu - Toolbar - Control toolbox.
Add a Command button. The Command button can be selected to run any macro.

Sub getworkbook()

Set OldWorkbook = ThisWorkbook
OldLastRow = OldWorkbook.Worksheets("sheet1"). _
Cells(1, 1).End(xlDown).Row

MyFileName = "c:\temp\abc.xls"

Workbooks.Open Filename:=MyFileName, ReadOnly:=True
' Removed pathname from file name so it can be referenced in this program.
'Basic doesn't like the full pathname???? stupid microsoft
Do While (1)
CharPosition = InStr(MyFileName, "\")
If CharPosition > 0 Then
MyFileName = Mid(MyFileName, CharPosition + 1)
Else
Exit Do
End If
Loop

Lastrow = Workbooks(MyFileName). _
Worksheets("sheet1").Cells(10, 1).End(xlDown).Row
Set MyRange = Workbooks(MyFileName).Worksheets("sheet1"). _
Range(Cells(10, 1), Cells(Lastrow, 1)).EntireRow

MyRange.Copy



MyRange.Copy Destination:=OldWorkbook.Worksheets("Sheet1"). _
Cells(OldLastRow + 1, 1)



Workbooks(MyFileName).Close SaveChanges:=False

End Sub