Copying Data Between Two Spreadsheets

[Guest]

I need to copy a column of data from spreadsheet “ABC†(not its real name)
that’s 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

 

[Guest]

Bob,

Didn't I answer this for you already?

http://www.microsoft.com/office/com...5298&catlist=&dglist=&ptlist=&exp=&sloc=en-us

Mike

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Mike,
Your code works like a charm. Thanks a million for all your help!
Regards, Bob