Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data from one workbook to another of same name
Greetings All,
Here is my problem: I need to copy the first 50 rows of a workbook and paste it into another of a similar name. More specifically, I have a folder of files that each need to have their first 50 rows copied and pasted into files that reside in another folder. Somehow I need to ensure that File A in Folder One, pastes those 50 rows into File A of Folder Two..... and so on through File Z. Am I drunk, and/or can this be done?!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data from one workbook to another of same name
This can definitely be done in a macro if you are willing to start the
macro and then find the destination file in an open prompt...the macro would then open that file...put in the 50 rows (how many columns?)...and then close and save the file. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data from one workbook to another of same name
I'm a bit desperate, so yes.... At around 100 files, and
the close and save being automated (?), shouldn't take too long, yes? Anyway, I'm all ears if you can point me in the direction to start.... TIA! -----Original Message----- This can definitely be done in a macro if you are willing to start the macro and then find the destination file in an open prompt...the macro would then open that file...put in the 50 rows (how many columns?)...and then close and save the file. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data from one workbook to another of same name
What if I know precisely the name of the destination file,
as well as its location (Lookup?)? -----Original Message----- This can definitely be done in a macro if you are willing to start the macro and then find the destination file in an open prompt...the macro would then open that file...put in the 50 rows (how many columns?)...and then close and save the file. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data from one workbook to another of same name
Sub TEMP()
'OPEN WORKBOOK Application.ScreenUpdating = False Application.DisplayAlerts = False current = Application.ActiveWorkbook.Name Dim FName As Variant FName = Application.GetOpenFilename _ (filefilter:="Excel Files(*.xls),*.xls,All Files (*.*),*.*") If FName = False Then Exit Sub Else Workbooks.OpenText Filename:=FName End If Dim TEMP As Variant openedwkbk = Application.ActiveWorkbook.Name 'COPY Workbooks(current).Activate Rows("1:50").Copy Workbooks(openedwkbk).Activate Range("A1").Select ActiveSheet.Paste 'SAVE AND CLOSE ActiveWorkbook.SaveAs Filename:=FName, FileFormat:=xlNormal ActiveWorkbook.Close Application.DisplayAlerts = True End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data from one workbook to another of same name
There is a start...FNAME is the full path name of the files...but it
may be more work to list those out then just to click on all the files. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data from one workbook to another of same name
My bad, get rid of the line that reads "Dim TEMP as Variant"
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data from one workbook to another of same name
While I very much appreciate the response, I fear I may be
in over my head here... Do I list all the files I wish to open after FName = Application.GetOpenFilename _? -----Original Message----- Sub TEMP() 'OPEN WORKBOOK Application.ScreenUpdating = False Application.DisplayAlerts = False current = Application.ActiveWorkbook.Name Dim FName As Variant FName = Application.GetOpenFilename _ (filefilter:="Excel Files(*.xls),*.xls,All Files (*.*),*.*") If FName = False Then Exit Sub Else Workbooks.OpenText Filename:=FName End If Dim TEMP As Variant openedwkbk = Application.ActiveWorkbook.Name 'COPY Workbooks(current).Activate Rows("1:50").Copy Workbooks(openedwkbk).Activate Range("A1").Select ActiveSheet.Paste 'SAVE AND CLOSE ActiveWorkbook.SaveAs Filename:=FName, FileFormat:=xlNormal ActiveWorkbook.Close Application.DisplayAlerts = True End Sub . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data from one workbook to another of same name
The code I gave you will do what I said earlier:
"This can definitely be done in a macro if you are willing to start the macro and then find the destination file in an open prompt...the macro would then open that file...put in the 50 rows (how many columns?)...and then close and save the file." IF you need to have the macro open the files (and not you) that gets much more complicated. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data from one workbook to another of same name
Expand the lists to include all of your source and corresponding destination
files. Sub CopyFiles() Dim PathSrc as String, PathDest as String Dim srcList as Variant, destList as Variant Dim i as Long Dim bkSrc as Workbook, bkDest as Workbook PathSrc = "C:\MyFiles\" PathDest = "C:\YourFiles\" srcList = Array("AE1234.xls", _ "AB3567.xls", _ "RN2134.xls", _ "ZZ9999.xls") destList = Array("bb1111.xls", _ "cc2222.xls", _ "dd3333.xls", _ "zz1111.xls") for i = lbound(srcList) to ubound(srcList) set bksrc = workbooks.Open(PathSrc & srcList(i)) set bkdest = workbooks.Open(PathDest & destlist(i)) bksrc.worksheets(1).Rows(1).Resize(50).copy _ Destination:=bkDest bksrc.close SaveChanges:=False bkDest.close SaveChanges:=True Next End Sub If you had come sequence to our names and some corresondence between source and destination filenames, this could be simpler. -- Regards, Tom Ogilvy "KENNY" wrote in message ... What if I know precisely the name of the destination file, as well as its location (Lookup?)? -----Original Message----- This can definitely be done in a macro if you are willing to start the macro and then find the destination file in an open prompt...the macro would then open that file...put in the 50 rows (how many columns?)...and then close and save the file. . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data from one workbook to another of same name
Thanks for the reply : The source and destination files
are exactly the same with one exception: the destination files have an "M" at the end, e.g. Source = TR123 Destination = TR123M Does that help? Thanks a ton! -----Original Message----- Expand the lists to include all of your source and corresponding destination files. Sub CopyFiles() Dim PathSrc as String, PathDest as String Dim srcList as Variant, destList as Variant Dim i as Long Dim bkSrc as Workbook, bkDest as Workbook PathSrc = "C:\MyFiles\" PathDest = "C:\YourFiles\" srcList = Array("AE1234.xls", _ "AB3567.xls", _ "RN2134.xls", _ "ZZ9999.xls") destList = Array("bb1111.xls", _ "cc2222.xls", _ "dd3333.xls", _ "zz1111.xls") for i = lbound(srcList) to ubound(srcList) set bksrc = workbooks.Open(PathSrc & srcList(i)) set bkdest = workbooks.Open(PathDest & destlist(i)) bksrc.worksheets(1).Rows(1).Resize(50).copy _ Destination:=bkDest bksrc.close SaveChanges:=False bkDest.close SaveChanges:=True Next End Sub If you had come sequence to our names and some corresondence between source and destination filenames, this could be simpler. -- Regards, Tom Ogilvy "KENNY" wrote in message ... What if I know precisely the name of the destination file, as well as its location (Lookup?)? -----Original Message----- This can definitely be done in a macro if you are willing to start the macro and then find the destination file in an open prompt...the macro would then open that file...put in the 50 rows (how many columns?)...and then close and save the file. . . |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data from one workbook to another of same name
One last problem: The below creats a "Run-time error 1004
Application-defined or object-defined error" Any help is greatly appreciated Sub RAW() Dim PathSrc As String, PathDest As String Dim srcList As Variant, destList As Variant Dim i As Long Dim bkSrc As Workbook, bkDest As Workbook PathSrc = "Y:\Sales\Target Customer\2005 Raw\" PathDest = "Y:\Sales\Target Customer\2005 Raw - Main\" srcList = Array("Raw 1.xls", _ "Raw 2.xls") destList = Array("Raw 1M.xls", _ "Raw 2M.xls") For i = LBound(srcList) To UBound(srcList) Set bkSrc = Workbooks.Open(PathSrc & srcList(i)) Set bkDest = Workbooks.Open(PathDest & destList(i)) bkSrc.Worksheets(1).Rows(1).Resize(50).Copy _ Destination:=bkDest bkSrc.Close SaveChanges:=False bkDest.Close SaveChanges:=True Next End Sub -----Original Message----- Expand the lists to include all of your source and corresponding destination files. Sub CopyFiles() Dim PathSrc as String, PathDest as String Dim srcList as Variant, destList as Variant Dim i as Long Dim bkSrc as Workbook, bkDest as Workbook PathSrc = "C:\MyFiles\" PathDest = "C:\YourFiles\" srcList = Array("AE1234.xls", _ "AB3567.xls", _ "RN2134.xls", _ "ZZ9999.xls") destList = Array("bb1111.xls", _ "cc2222.xls", _ "dd3333.xls", _ "zz1111.xls") for i = lbound(srcList) to ubound(srcList) set bksrc = workbooks.Open(PathSrc & srcList(i)) set bkdest = workbooks.Open(PathDest & destlist(i)) bksrc.worksheets(1).Rows(1).Resize(50).copy _ Destination:=bkDest bksrc.close SaveChanges:=False bkDest.close SaveChanges:=True Next End Sub If you had come sequence to our names and some corresondence between source and destination filenames, this could be simpler. -- Regards, Tom Ogilvy "KENNY" wrote in message ... What if I know precisely the name of the destination file, as well as its location (Lookup?)? -----Original Message----- This can definitely be done in a macro if you are willing to start the macro and then find the destination file in an open prompt...the macro would then open that file...put in the 50 rows (how many columns?)...and then close and save the file. . . |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data from one workbook to another of same name
There was a problem on one line. I fixed that.
This uses the fact about just adding the M, so it only needs the srclist. I tested it and it worked for me: Sub RAW_AA() Dim PathSrc As String, PathDest As String Dim srcList As Variant Dim i As Long, sDest As String Dim bkSrc As Workbook, bkDest As Workbook PathSrc = "Y:\Sales\Target Customer\2005 Raw\" PathDest = "Y:\Sales\Target Customer\2005 Raw - Main\" srcList = Array("Raw 1.xls", _ "Raw 2.xls", _ "Raw 3.xls") For i = LBound(srcList) To UBound(srcList) Set bkSrc = Workbooks.Open(PathSrc & srcList(i)) sDest = bkSrc.Name sDest = Left(sDest, Len(sDest) - 4) & "M.xls" Set bkDest = Workbooks.Open(PathDest & sDest) bkSrc.Worksheets(1).Rows(1).Resize(50).Copy _ Destination:=bkDest.Worksheets(1).Range("A1") bkSrc.Close SaveChanges:=False bkDest.Close SaveChanges:=True Next End Sub -------------- This uses two lists. Also tested and it works. Sub RAW() Dim PathSrc As String, PathDest As String Dim srcList As Variant, destList As Variant Dim i As Long Dim bkSrc As Workbook, bkDest As Workbook PathSrc = "Y:\Sales\Target Customer\2005 Raw\" PathDest = "Y:\Sales\Target Customer\2005 Raw - Main\" srcList = Array("Raw 1.xls", _ "Raw 2.xls", _ "Raw 3.xls") destList = Array("Raw 1M.xls", _ "Raw 2M.xls", _ "Raw 3M.xls") For i = LBound(srcList) To UBound(srcList) Set bkSrc = Workbooks.Open(PathSrc & srcList(i)) Set bkDest = Workbooks.Open(PathDest & destList(i)) bkSrc.Worksheets(1).Rows(1).Resize(50).Copy _ Destination:=bkDest.Worksheets(1).Range("A1") bkSrc.Close SaveChanges:=False bkDest.Close SaveChanges:=True Next End Sub -- Regards, Tom Ogilvy "KENNY" wrote in message ... If I haven't burnt all my goodwill, I'm now get the error: "Copy Method of Rangle Class Failed" -----Original Message----- One last problem: The below creats a "Run-time error 1004 Application-defined or object-defined error" Any help is greatly appreciated Sub RAW() Dim PathSrc As String, PathDest As String Dim srcList As Variant, destList As Variant Dim i As Long Dim bkSrc As Workbook, bkDest As Workbook PathSrc = "Y:\Sales\Target Customer\2005 Raw\" PathDest = "Y:\Sales\Target Customer\2005 Raw - Main\" srcList = Array("Raw 1.xls", _ "Raw 2.xls") destList = Array("Raw 1M.xls", _ "Raw 2M.xls") For i = LBound(srcList) To UBound(srcList) Set bkSrc = Workbooks.Open(PathSrc & srcList(i)) Set bkDest = Workbooks.Open(PathDest & destList(i)) bkSrc.Worksheets(1).Rows(1).Resize(50).Copy _ Destination:=bkDest bkSrc.Close SaveChanges:=False bkDest.Close SaveChanges:=True Next End Sub -----Original Message----- Expand the lists to include all of your source and corresponding destination files. Sub CopyFiles() Dim PathSrc as String, PathDest as String Dim srcList as Variant, destList as Variant Dim i as Long Dim bkSrc as Workbook, bkDest as Workbook PathSrc = "C:\MyFiles\" PathDest = "C:\YourFiles\" srcList = Array("AE1234.xls", _ "AB3567.xls", _ "RN2134.xls", _ "ZZ9999.xls") destList = Array("bb1111.xls", _ "cc2222.xls", _ "dd3333.xls", _ "zz1111.xls") for i = lbound(srcList) to ubound(srcList) set bksrc = workbooks.Open(PathSrc & srcList(i)) set bkdest = workbooks.Open(PathDest & destlist(i)) bksrc.worksheets(1).Rows(1).Resize(50).copy _ Destination:=bkDest bksrc.close SaveChanges:=False bkDest.close SaveChanges:=True Next End Sub If you had come sequence to our names and some corresondence between source and destination filenames, this could be simpler. -- Regards, Tom Ogilvy "KENNY" wrote in message ... What if I know precisely the name of the destination file, as well as its location (Lookup?)? -----Original Message----- This can definitely be done in a macro if you are willing to start the macro and then find the destination file in an open prompt...the macro would then open that file...put in the 50 rows (how many columns?)...and then close and save the file. . . . |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data from one workbook to another of same name
SUCCESS!!!
I can't begin to tell you how much I appreciate your help... I think you MVP's that save us hacks are AWESOME! -----Original Message----- There was a problem on one line. I fixed that. This uses the fact about just adding the M, so it only needs the srclist. I tested it and it worked for me: Sub RAW_AA() Dim PathSrc As String, PathDest As String Dim srcList As Variant Dim i As Long, sDest As String Dim bkSrc As Workbook, bkDest As Workbook PathSrc = "Y:\Sales\Target Customer\2005 Raw\" PathDest = "Y:\Sales\Target Customer\2005 Raw - Main\" srcList = Array("Raw 1.xls", _ "Raw 2.xls", _ "Raw 3.xls") For i = LBound(srcList) To UBound(srcList) Set bkSrc = Workbooks.Open(PathSrc & srcList(i)) sDest = bkSrc.Name sDest = Left(sDest, Len(sDest) - 4) & "M.xls" Set bkDest = Workbooks.Open(PathDest & sDest) bkSrc.Worksheets(1).Rows(1).Resize(50).Copy _ Destination:=bkDest.Worksheets(1).Range("A1") bkSrc.Close SaveChanges:=False bkDest.Close SaveChanges:=True Next End Sub -------------- This uses two lists. Also tested and it works. Sub RAW() Dim PathSrc As String, PathDest As String Dim srcList As Variant, destList As Variant Dim i As Long Dim bkSrc As Workbook, bkDest As Workbook PathSrc = "Y:\Sales\Target Customer\2005 Raw\" PathDest = "Y:\Sales\Target Customer\2005 Raw - Main\" srcList = Array("Raw 1.xls", _ "Raw 2.xls", _ "Raw 3.xls") destList = Array("Raw 1M.xls", _ "Raw 2M.xls", _ "Raw 3M.xls") For i = LBound(srcList) To UBound(srcList) Set bkSrc = Workbooks.Open(PathSrc & srcList(i)) Set bkDest = Workbooks.Open(PathDest & destList(i)) bkSrc.Worksheets(1).Rows(1).Resize(50).Copy _ Destination:=bkDest.Worksheets(1).Range("A1") bkSrc.Close SaveChanges:=False bkDest.Close SaveChanges:=True Next End Sub -- Regards, Tom Ogilvy "KENNY" wrote in message ... If I haven't burnt all my goodwill, I'm now get the error: "Copy Method of Rangle Class Failed" -----Original Message----- One last problem: The below creats a "Run-time error 1004 Application-defined or object-defined error" Any help is greatly appreciated Sub RAW() Dim PathSrc As String, PathDest As String Dim srcList As Variant, destList As Variant Dim i As Long Dim bkSrc As Workbook, bkDest As Workbook PathSrc = "Y:\Sales\Target Customer\2005 Raw\" PathDest = "Y:\Sales\Target Customer\2005 Raw - Main\" srcList = Array("Raw 1.xls", _ "Raw 2.xls") destList = Array("Raw 1M.xls", _ "Raw 2M.xls") For i = LBound(srcList) To UBound(srcList) Set bkSrc = Workbooks.Open(PathSrc & srcList(i)) Set bkDest = Workbooks.Open(PathDest & destList(i)) bkSrc.Worksheets(1).Rows(1).Resize(50).Copy _ Destination:=bkDest bkSrc.Close SaveChanges:=False bkDest.Close SaveChanges:=True Next End Sub -----Original Message----- Expand the lists to include all of your source and corresponding destination files. Sub CopyFiles() Dim PathSrc as String, PathDest as String Dim srcList as Variant, destList as Variant Dim i as Long Dim bkSrc as Workbook, bkDest as Workbook PathSrc = "C:\MyFiles\" PathDest = "C:\YourFiles\" srcList = Array("AE1234.xls", _ "AB3567.xls", _ "RN2134.xls", _ "ZZ9999.xls") destList = Array("bb1111.xls", _ "cc2222.xls", _ "dd3333.xls", _ "zz1111.xls") for i = lbound(srcList) to ubound(srcList) set bksrc = workbooks.Open(PathSrc & srcList(i)) set bkdest = workbooks.Open(PathDest & destlist(i)) bksrc.worksheets(1).Rows(1).Resize(50).copy _ Destination:=bkDest bksrc.close SaveChanges:=False bkDest.close SaveChanges:=True Next End Sub If you had come sequence to our names and some corresondence between source and destination filenames, this could be simpler. -- Regards, Tom Ogilvy "KENNY" wrote in message ... What if I know precisely the name of the destination file, as well as its location (Lookup?)? -----Original Message----- This can definitely be done in a macro if you are willing to start the macro and then find the destination file in an open prompt...the macro would then open that file...put in the 50 rows (how many columns?)...and then close and save the file. . . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to copy and match data from one workbook to another workbook | Excel Worksheet Functions | |||
Search Data from one Workbook and copy it into another Workbook | Excel Discussion (Misc queries) | |||
Copy Data from Workbook into specific Worksheet in other Workbook? | Excel Discussion (Misc queries) | |||
copy excel workbook to new workbook without data | Excel Worksheet Functions | |||
conditional copy data from workbook to another workbook | Excel Programming |