Copy & Paste

  • Thread starter Thread starter STEVEB
  • Start date Start date
S

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
 
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.
 
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?
 
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
 
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
 
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.
 
Back
Top