| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Data Between Two Spreadsheets
I need to copy a column of data from spreadsheet ABC (not its real name)
thats located on a shared drive, to spreadsheet XYZ (also not its real name) located on my local hard drive. The range of cells to be copied from spreadsheet ABC will always be Detail Data!H6:H990. The destination starting cell in spreadsheet XYZ will always be Consolidated Data!A2. The macro needs to be run from within spreadsheet XYZ. Since spreadsheet ABC is located on a shared drive, the macro would need to prompt the user for its location (using Windows standard File | Open dialog box). In addition, there may be times when spreadsheet ABC is in use by another user and therefore locked, except for read-only capabilities. Consequently, it would be nice if the macro could check for that condition, and if it exists, automatically select the read-only option. Being new to VBA, I would greatly appreciate any help in writing the aforementioned macro, especially since two spreadsheets are involved. Thanks, Bob |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying Data Between Two Spreadsheets
Bob,
Didn't I answer this for you already? http://www.microsoft.com/office/comm...xp=&sloc=en-us Mike "Bob" wrote: I need to copy a column of data from spreadsheet ABC (not its real name) thats located on a shared drive, to spreadsheet XYZ (also not its real name) located on my local hard drive. The range of cells to be copied from spreadsheet ABC will always be Detail Data!H6:H990. The destination starting cell in spreadsheet XYZ will always be Consolidated Data!A2. The macro needs to be run from within spreadsheet XYZ. Since spreadsheet ABC is located on a shared drive, the macro would need to prompt the user for its location (using Windows standard File | Open dialog box). In addition, there may be times when spreadsheet ABC is in use by another user and therefore locked, except for read-only capabilities. Consequently, it would be nice if the macro could check for that condition, and if it exists, automatically select the read-only option. Being new to VBA, I would greatly appreciate any help in writing the aforementioned macro, especially since two spreadsheets are involved. Thanks, Bob |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying Data Between Two Spreadsheets
Mike,
Yes you did, thank you. When I didn't hear back from you after a few days on a supplemental question to my original question, I assumed you were tied up. Thanks again for all your help. I sincerely appreciated it. Regards, Bob "crazybass2" wrote: Bob, Didn't I answer this for you already? http://www.microsoft.com/office/comm...xp=&sloc=en-us Mike "Bob" wrote: I need to copy a column of data from spreadsheet ABC (not its real name) thats located on a shared drive, to spreadsheet XYZ (also not its real name) located on my local hard drive. The range of cells to be copied from spreadsheet ABC will always be Detail Data!H6:H990. The destination starting cell in spreadsheet XYZ will always be Consolidated Data!A2. The macro needs to be run from within spreadsheet XYZ. Since spreadsheet ABC is located on a shared drive, the macro would need to prompt the user for its location (using Windows standard File | Open dialog box). In addition, there may be times when spreadsheet ABC is in use by another user and therefore locked, except for read-only capabilities. Consequently, it would be nice if the macro could check for that condition, and if it exists, automatically select the read-only option. Being new to VBA, I would greatly appreciate any help in writing the aforementioned macro, especially since two spreadsheets are involved. Thanks, Bob |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying Data Between Two Spreadsheets
Mike,
Forgive me for bothering you, but instead of simply copying the data, I need to paste just the Values into the destination spreadsheet. I tried to modify your code using the PasteSpecial Method (Operation:=xlPasteValues), but I can't seem to get it to work. I would greatly appreciate your help on this. Thanks, Bob "crazybass2" wrote: Bob, Didn't I answer this for you already? http://www.microsoft.com/office/comm...xp=&sloc=en-us Mike "Bob" wrote: I need to copy a column of data from spreadsheet ABC (not its real name) thats located on a shared drive, to spreadsheet XYZ (also not its real name) located on my local hard drive. The range of cells to be copied from spreadsheet ABC will always be Detail Data!H6:H990. The destination starting cell in spreadsheet XYZ will always be Consolidated Data!A2. The macro needs to be run from within spreadsheet XYZ. Since spreadsheet ABC is located on a shared drive, the macro would need to prompt the user for its location (using Windows standard File | Open dialog box). In addition, there may be times when spreadsheet ABC is in use by another user and therefore locked, except for read-only capabilities. Consequently, it would be nice if the macro could check for that condition, and if it exists, automatically select the read-only option. Being new to VBA, I would greatly appreciate any help in writing the aforementioned macro, especially since two spreadsheets are involved. Thanks, Bob |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying Data Between Two Spreadsheets
Bob,
You may have left in some of the copy code....try this revised version. I have commented out the code that is replaces. You will need to do the same or just delete it. Mike Sub consolidate() Dim origin As String Dim orgn As Workbook, dest As Workbook Application.ScreenUpdating = False origin = Application.GetOpenFilename("Microsoft Office Excel Files (*.xl*;*.xls;*.xla;*.xlm;*.xlc;*.xlw),*.xl*;*.xls; *.xla;*.xlm;*.xlc;*.xlw") If origin = "False" Then Exit Sub Workbooks.Open origin, 0, True Set orgn = ActiveWorkbook If ThisWorkbook.ReadOnly Then MsgBox ("The destination file has been opened as a Read-Only file and cannot be written to...Cancelling") GoTo inuse End If orgn.Sheets("Detail Data").Range("H6:H990").Copy '_ 'Destination:=ThisWorkbook.Sheets("Consolidated Data").Range("A2") ThisWorkbook.Sheets("Consolidated Data").Range("A2").PasteSpecial (xlPasteValues) ThisWorkbook.Save inuse: orgn.Close Application.ScreenUpdating = True End Sub "Bob" wrote: Mike, Forgive me for bothering you, but instead of simply copying the data, I need to paste just the Values into the destination spreadsheet. I tried to modify your code using the PasteSpecial Method (Operation:=xlPasteValues), but I can't seem to get it to work. I would greatly appreciate your help on this. Thanks, Bob "crazybass2" wrote: Bob, Didn't I answer this for you already? http://www.microsoft.com/office/comm...xp=&sloc=en-us Mike "Bob" wrote: I need to copy a column of data from spreadsheet ABC (not its real name) thats located on a shared drive, to spreadsheet XYZ (also not its real name) located on my local hard drive. The range of cells to be copied from spreadsheet ABC will always be Detail Data!H6:H990. The destination starting cell in spreadsheet XYZ will always be Consolidated Data!A2. The macro needs to be run from within spreadsheet XYZ. Since spreadsheet ABC is located on a shared drive, the macro would need to prompt the user for its location (using Windows standard File | Open dialog box). In addition, there may be times when spreadsheet ABC is in use by another user and therefore locked, except for read-only capabilities. Consequently, it would be nice if the macro could check for that condition, and if it exists, automatically select the read-only option. Being new to VBA, I would greatly appreciate any help in writing the aforementioned macro, especially since two spreadsheets are involved. Thanks, Bob |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying Data Between Two Spreadsheets
Bob,
Note that the (xlPasteValues) should be on the same line as the preceding statement. My reply shows a new line instead of a space between ..PasteSpecial and (xlPasteValues) Mike "crazybass2" wrote: Bob, You may have left in some of the copy code....try this revised version. I have commented out the code that is replaces. You will need to do the same or just delete it. Mike Sub consolidate() Dim origin As String Dim orgn As Workbook, dest As Workbook Application.ScreenUpdating = False origin = Application.GetOpenFilename("Microsoft Office Excel Files (*.xl*;*.xls;*.xla;*.xlm;*.xlc;*.xlw),*.xl*;*.xls; *.xla;*.xlm;*.xlc;*.xlw") If origin = "False" Then Exit Sub Workbooks.Open origin, 0, True Set orgn = ActiveWorkbook If ThisWorkbook.ReadOnly Then MsgBox ("The destination file has been opened as a Read-Only file and cannot be written to...Cancelling") GoTo inuse End If orgn.Sheets("Detail Data").Range("H6:H990").Copy '_ 'Destination:=ThisWorkbook.Sheets("Consolidated Data").Range("A2") ThisWorkbook.Sheets("Consolidated Data").Range("A2").PasteSpecial (xlPasteValues) ThisWorkbook.Save inuse: orgn.Close Application.ScreenUpdating = True End Sub "Bob" wrote: Mike, Forgive me for bothering you, but instead of simply copying the data, I need to paste just the Values into the destination spreadsheet. I tried to modify your code using the PasteSpecial Method (Operation:=xlPasteValues), but I can't seem to get it to work. I would greatly appreciate your help on this. Thanks, Bob "crazybass2" wrote: Bob, Didn't I answer this for you already? http://www.microsoft.com/office/comm...xp=&sloc=en-us Mike "Bob" wrote: I need to copy a column of data from spreadsheet ABC (not its real name) thats located on a shared drive, to spreadsheet XYZ (also not its real name) located on my local hard drive. The range of cells to be copied from spreadsheet ABC will always be Detail Data!H6:H990. The destination starting cell in spreadsheet XYZ will always be Consolidated Data!A2. The macro needs to be run from within spreadsheet XYZ. Since spreadsheet ABC is located on a shared drive, the macro would need to prompt the user for its location (using Windows standard File | Open dialog box). In addition, there may be times when spreadsheet ABC is in use by another user and therefore locked, except for read-only capabilities. Consequently, it would be nice if the macro could check for that condition, and if it exists, automatically select the read-only option. Being new to VBA, I would greatly appreciate any help in writing the aforementioned macro, especially since two spreadsheets are involved. Thanks, Bob |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying Data Between Two Spreadsheets
Mike,
Your code works like a charm. Thanks a million for all your help! Regards, Bob "crazybass2" wrote: Bob, Note that the (xlPasteValues) should be on the same line as the preceding statement. My reply shows a new line instead of a space between .PasteSpecial and (xlPasteValues) Mike "crazybass2" wrote: Bob, You may have left in some of the copy code....try this revised version. I have commented out the code that is replaces. You will need to do the same or just delete it. Mike Sub consolidate() Dim origin As String Dim orgn As Workbook, dest As Workbook Application.ScreenUpdating = False origin = Application.GetOpenFilename("Microsoft Office Excel Files (*.xl*;*.xls;*.xla;*.xlm;*.xlc;*.xlw),*.xl*;*.xls; *.xla;*.xlm;*.xlc;*.xlw") If origin = "False" Then Exit Sub Workbooks.Open origin, 0, True Set orgn = ActiveWorkbook If ThisWorkbook.ReadOnly Then MsgBox ("The destination file has been opened as a Read-Only file and cannot be written to...Cancelling") GoTo inuse End If orgn.Sheets("Detail Data").Range("H6:H990").Copy '_ 'Destination:=ThisWorkbook.Sheets("Consolidated Data").Range("A2") ThisWorkbook.Sheets("Consolidated Data").Range("A2").PasteSpecial (xlPasteValues) ThisWorkbook.Save inuse: orgn.Close Application.ScreenUpdating = True End Sub "Bob" wrote: Mike, Forgive me for bothering you, but instead of simply copying the data, I need to paste just the Values into the destination spreadsheet. I tried to modify your code using the PasteSpecial Method (Operation:=xlPasteValues), but I can't seem to get it to work. I would greatly appreciate your help on this. Thanks, Bob "crazybass2" wrote: Bob, Didn't I answer this for you already? http://www.microsoft.com/office/comm...xp=&sloc=en-us Mike "Bob" wrote: I need to copy a column of data from spreadsheet ABC (not its real name) thats located on a shared drive, to spreadsheet XYZ (also not its real name) located on my local hard drive. The range of cells to be copied from spreadsheet ABC will always be Detail Data!H6:H990. The destination starting cell in spreadsheet XYZ will always be Consolidated Data!A2. The macro needs to be run from within spreadsheet XYZ. Since spreadsheet ABC is located on a shared drive, the macro would need to prompt the user for its location (using Windows standard File | Open dialog box). In addition, there may be times when spreadsheet ABC is in use by another user and therefore locked, except for read-only capabilities. Consequently, it would be nice if the macro could check for that condition, and if it exists, automatically select the read-only option. Being new to VBA, I would greatly appreciate any help in writing the aforementioned macro, especially since two spreadsheets are involved. Thanks, Bob |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copying Web Tables To Spreadsheets | New Users to Excel | |||
| How can I automate copying data from one into several spreadsheets | Excel Discussion (Misc queries) | |||
| copying excel spreadsheets | Excel Discussion (Misc queries) | |||
| Copying Data Between Two Spreadsheets | Excel Programming | |||
| Copying Data from various spreadsheets | Excel Programming | |||
Linear Mode
