Copy & Paste
 

I have workbook "A" that I have open & would like a Macro to copy th
information from workbook "B" and then copy the information fro
workbook "C" below the information previously copied from workbook "B"
After that is completed I would like column A sorted in ascendin
order and an auto filter added in row 1. Does anyone have an
sugesstions?

Thank

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Copy & Paste
 

set wkbk = Workbooks.Open( "C:\B.xls")
wkbk.worksheets(1).Rows(1).copy _
Destination:=thisworkbook.Worksheets(1).Cells(rows .count,1).End(xlup)(2)
wkbk.Close
set wkbk = Workbooks.Open( "C:\C.xls")
wkbk.worksheets(1).Rows(1).copy _
Destination:=thisworkbook.Worksheets(1).Cells(rows .count,1).End(xlup)(2)
wkbk.Close

You can get the rest by turning on the macro recorder while you make the
changes manually.

--
Regards,
Tom Ogilvy

"STEVEB" wrote in message
...

I have workbook "A" that I have open & would like a Macro to copy the
information from workbook "B" and then copy the information from
workbook "C" below the information previously copied from workbook "B".
After that is completed I would like column A sorted in ascending
order and an auto filter added in row 1. Does anyone have any
sugesstions?

Thanks


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Copy & Paste
 

Tom,

This worked Great...Thanks for your help.


I plan to use this macro twice a month. Thus workbook B was actually
C:\B.October.xls & workbook C was actually C:\C.October.xls. Each
month I save the file with the current month as the last part of the
name & again mid month generally October 15.xls.

Is there a way for the Macro to find the most recent file by date &
open that workbook? What I am trying to avoid is having to change the
file path in the macro each month to open the most recent file. Thus,
at the end of November, I would like the macro to copy & past from the
Nov file not the Oct file. Any suggestions?


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Copy & Paste
 

Dim sMonth as String
Dim sc as string, sb as string
Dim wkbk as Workbook

sMonth = format(date,"mmmm")
if day(date) = 15 then _
sMonth = sMonth & " 15"
sc = "C." & sMonth & ".xls"
sb = "B." & sMonth & ".xls"
set wkbk = Workbooks.Open( "C:\" & sb)
wkbk.worksheets(1).Rows(1).copy _
Destination:=thisworkbook.Worksheets(1).Cells(rows .count,1).End(xlup)(2)
wkbk.Close
set wkbk = Workbooks.Open( "C:\" & sc)
wkbk.worksheets(1).Rows(1).copy _
Destination:=thisworkbook.Worksheets(1).Cells(rows .count,1).End(xlup)(2)
wkbk.Close

--
Regards,
Tom Ogilvy


"STEVEB" wrote in message
...

Tom,

This worked Great...Thanks for your help.


I plan to use this macro twice a month. Thus workbook B was actually
C:\B.October.xls & workbook C was actually C:\C.October.xls. Each
month I save the file with the current month as the last part of the
name & again mid month generally October 15.xls.

Is there a way for the Macro to find the most recent file by date &
open that workbook? What I am trying to avoid is having to change the
file path in the macro each month to open the most recent file. Thus,
at the end of November, I would like the macro to copy & past from the
Nov file not the Oct file. Any suggestions?


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Copy & Paste
 

Thanks again Tom! Everything worked smoothly.

I have a similar Macro that is on a one month lag....meaning I run th
October report & the October 15th report in November. Is there a wa
to change the code to always have the macro open the prior month fil
instead of the curren month?

Thanks again,

Stev

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Copy & Paste
 

Dim sMonth as String
Dim sc as string, sb as string
Dim wkbk as Workbook
Dim dt as Date

dt = DateSerial(year(date),Month(Date)-1,Day(date))
sMonth = format(dt,"mmmm")
if day(dt) = 15 then _
sMonth = sMonth & " 15"
sc = "C." & sMonth & ".xls"
sb = "B." & sMonth & ".xls"
set wkbk = Workbooks.Open( "C:\" & sb)
wkbk.worksheets(1).Rows(1).copy _
Destination:=thisworkbook.Worksheets(1).Cells(rows .count,1).End(xlup)(2)
wkbk.Close
set wkbk = Workbooks.Open( "C:\" & sc)
wkbk.worksheets(1).Rows(1).copy _
Destination:=thisworkbook.Worksheets(1).Cells(rows .count,1).End(xlup)(2)
wkbk.Close

But this assumes you are running a month or more after, so to do Oct 15, you
would have to do it in November on or after the 15th.

--
Regards,
Tom Ogilvy


STEVEB wrote in message
...

Thanks again Tom! Everything worked smoothly.

I have a similar Macro that is on a one month lag....meaning I run the
October report & the October 15th report in November. Is there a way
to change the code to always have the macro open the prior month file
instead of the curren month?

Thanks again,

Steve


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/