Consolidating worksheets
 

Each month I will have between 28-31 workbooks located in
directory C:\Renewals Reports and will need to
combine/compile all the data in columns A:Z in all these
files into one workbook called Renewals.xls. Now instead
of manually opening each workbook and copying and pasting
all the data into my Renewals.xls spreadhseet, I would
rather have a code to do it all automatically.

Currently I have the following code(se below) but its not
quite what I need. This code looks in C:\Renewals Reports
and opens up each spreadhseet file and puts the value of
cell A2 into my Renewals.xls file starting in cell A2.
Whatever value is in cell A2 of the next workbook is put
in the next cell down in Renewals.xls.

Instead of getting the data in cell A2 of all the
worksheets, how do I get the data in all the cells in
column A:Z(not including blank cells)?


Dim FName As String
Dim WB As Workbook
Dim Dest As Range
Const FOLDERNAME = "C:\Renewals Reports" 'ChDrive
FOLDERNAME
ChDir FOLDERNAME
Set Dest = Range("A2")
FName = Dir("*.xls")
Do Until FName = ""
Set WB = Workbooks.Open(FName)
WB.Worksheets(1).Rows(2).Copy Destination:=Dest
WB.Close savechanges:=False
Set Dest = Dest(2, 1)
FName = Dir()
Loop

Thanks
Todd Huttenstine

Consolidating worksheets
 

Hi

Check out this page
http://www.rondebruin.nl/copy3.htm

And a new one that use ADO
http://www.rondebruin.nl/ado.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Todd Huttenstine" wrote in message ...
Each month I will have between 28-31 workbooks located in
directory C:\Renewals Reports and will need to
combine/compile all the data in columns A:Z in all these
files into one workbook called Renewals.xls. Now instead
of manually opening each workbook and copying and pasting
all the data into my Renewals.xls spreadhseet, I would
rather have a code to do it all automatically.

Currently I have the following code(se below) but its not
quite what I need. This code looks in C:\Renewals Reports
and opens up each spreadhseet file and puts the value of
cell A2 into my Renewals.xls file starting in cell A2.
Whatever value is in cell A2 of the next workbook is put
in the next cell down in Renewals.xls.

Instead of getting the data in cell A2 of all the
worksheets, how do I get the data in all the cells in
column A:Z(not including blank cells)?


Dim FName As String
Dim WB As Workbook
Dim Dest As Range
Const FOLDERNAME = "C:\Renewals Reports" 'ChDrive
FOLDERNAME
ChDir FOLDERNAME
Set Dest = Range("A2")
FName = Dir("*.xls")
Do Until FName = ""
Set WB = Workbooks.Open(FName)
WB.Worksheets(1).Rows(2).Copy Destination:=Dest
WB.Close savechanges:=False
Set Dest = Dest(2, 1)
FName = Dir()
Loop

Thanks
Todd Huttenstine

Consolidating worksheets
 

Thanks Ron. I like your site.


"Ron de Bruin" wrote in message
...
Hi

Check out this page
http://www.rondebruin.nl/copy3.htm

And a new one that use ADO
http://www.rondebruin.nl/ado.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Todd Huttenstine" wrote in message
...
Each month I will have between 28-31 workbooks located in
directory C:\Renewals Reports and will need to
combine/compile all the data in columns A:Z in all these
files into one workbook called Renewals.xls. Now instead
of manually opening each workbook and copying and pasting
all the data into my Renewals.xls spreadhseet, I would
rather have a code to do it all automatically.

Currently I have the following code(se below) but its not
quite what I need. This code looks in C:\Renewals Reports
and opens up each spreadhseet file and puts the value of
cell A2 into my Renewals.xls file starting in cell A2.
Whatever value is in cell A2 of the next workbook is put
in the next cell down in Renewals.xls.

Instead of getting the data in cell A2 of all the
worksheets, how do I get the data in all the cells in
column A:Z(not including blank cells)?


Dim FName As String
Dim WB As Workbook
Dim Dest As Range
Const FOLDERNAME = "C:\Renewals Reports" 'ChDrive
FOLDERNAME
ChDir FOLDERNAME
Set Dest = Range("A2")
FName = Dir("*.xls")
Do Until FName = ""
Set WB = Workbooks.Open(FName)
WB.Worksheets(1).Rows(2).Copy Destination:=Dest
WB.Close savechanges:=False
Set Dest = Dest(2, 1)
FName = Dir()
Loop

Thanks
Todd Huttenstine