I need to copy information out of one workbook and pastelink it into a seperate workbook. It would be best if this could all be done using a combination of RefEdit boxes (2). The code that I’ve pasted below allows me to do this within one workbookfrom sheet to sheet, but not from workbook to workbook. Can anyone help me??
Private Sub btnCopy_Paste_Click()
Dim WorkupRange, WorkupRange_, BidsheetRange, BidsheetRange_, Activate, x As String
Dim temp1, temp2 As Integer
ActiveSheet.Unprotect
‘Looks at the user selected cells for Workup and Copys
‘Reads users cells into macro
WorkupRange = RefEdit_Workup.Text
‘Removes colomn letter from cell address to replace with “Z”
temp1 = InStr(WorkupRange, “$”)temp2 = InStrRev(WorkupRange, “$”)
‘Extends selection through “Z” and copies
WorkupRange_ = WorkupRange & “:” & Left(WorkupRange, temp1) & “Z” & Right(WorkupRange, Len(WorkupRange) – temp2 + 1)
temp1 = InStr(WorkupRange, “!”)
Activate = Left(WorkupRange, temp1 – 2)
Activate = Right(Activate, temp1 – 3)
Worksheets(Activate).Activate
Range(WorkupRange_).Select
Selection.Copy
‘Reads users cells into macro
BidsheetRange = RefEdit_Bidsheet.Text
‘Shifts from column “P” to column “AC”
temp1 = InStr(BidsheetRange, “$”)
temp2 = InStrRev(BidsheetRange, “$”)
BidsheetRange_ = Left(BidsheetRange, temp1) & “AC” & Right(BidsheetRange, Len(BidsheetRange) – temp2 + 1)
temp1 = InStr(BidsheetRange, “!”)
Activate = Left(BidsheetRange, temp1 – 2)
x = Right(Activate, temp1 – 3)
Worksheets(x).Activate
ActiveSheet.Unprotect
Range(BidsheetRange_).Select
‘Past link from workup
‘Unprotects Active Bidsheet
Application.ScreenUpdating = True
ActiveSheet.Paste Link:=True
Application.CutCopyMode = False
End Sub