Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Copy, paste without file name referenced after paste | Excel Discussion (Misc queries) | |||
Copy; Paste; Paste Special are disabled | Excel Discussion (Misc queries) | |||
Excel cut/Paste Problem: Year changes after data is copy and paste | Excel Discussion (Misc queries) | |||
I cannot paste from one workbook to another. Copy works, paste do. | Excel Discussion (Misc queries) |