Copy & Paste

[STEVEB]

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

 

[Tom Ogilvy]

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.

 

[STEVEB]

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?

 

[Tom Ogilvy]

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

 

[STEVEB]

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

 

[Tom Ogilvy]

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.