General discussion

Locked

VBA/Excel Workbook to Workbook

By CBDepadua ·
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

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

VBA/Excel Workbook to Workbook

by john_wills In reply to VBA/Excel Workbook to Wor ...

This won't help with your problem; it's just a piece of VBA you don't seem to know. Your two variable declarations define mostly variant-type variables. You seem to think that one AS at the end of a DIM statement covers all the preceding variables, but it does not. As for your actual problem I do intend to come back later.

Collapse -

VBA/Excel Workbook to Workbook

by CBDepadua In reply to VBA/Excel Workbook to Wor ...

thanks, but i figured it out

Collapse -

VBA/Excel Workbook to Workbook

by CBDepadua In reply to VBA/Excel Workbook to Wor ...

This question was closed by the author

Back to Web Development Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums