Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy-Paste between sheets in dif WB's
Hello,
I seem to miss some vital info in how to copy-paste a selection between one WS in a WB to the WS name in another WB. I wan to do this in VB and I tried to record a macro and translate it into my VB-code. This is what I have done: Application.ScreenUpdating = False ' Set copy range for WS in secondary WB mySel = gci_FirstRow_Data & ":" & nLastRowNo Rows(mySel).Select ' Copy source range Selection.Copy ' Activate primary WB where the ' selection will be pasted Windows(sPrimaryPAP).Activate ' Activate and unprotect right worksheet ' where the selection will be pasted Worksheets(sPrimConfigWSName).Activate ActiveSheet.Unprotect ' ----- Set paste range in primary WB mySel = gci_FirstRow_Data & ":" & nLastRowNo Rows(mySel).Select ' ----- Paste source range Selection.Insert Shift:=xlDown ' Protect Sheet... ActiveSheet.Protect ' ... and activate secondary WB Windows(sSecondWBName).Activate Worksheets(sSecConfigWSName).Activate Application.ScreenUpdating = True Can anybody help me with this. It seems that the range to be copied are not collected from the other WB. Thanks in advance! /konpego |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy-Paste between sheets in dif WB's
konpego,
Unprotecting a worksheet clears the clipboard in Excel. See your modified code below for how to copy to a protected sheet. HTH, Bernie MS Excel MVP Application.ScreenUpdating = False ' Activate primary WB where the ' selection will be pasted Windows(sPrimaryPAP).Activate ' Activate and unprotect right worksheet ' where the selection will be pasted Worksheets(sPrimConfigWSName).Activate ActiveSheet.Unprotect ' ... and go back and activate secondary WB Windows(sSecondWBName).Activate Worksheets(sSecConfigWSName).Activate ' Set copy range for WS in secondary WB mySel = gci_FirstRow_Data & ":" & nLastRowNo Rows(mySel).Select ' Copy source range Selection.Copy ' Re-Activate primary WB where the ' selection will be pasted Windows(sPrimaryPAP).Activate ' ----- Set paste range in primary WB mySel = gci_FirstRow_Data & ":" & nLastRowNo Rows(mySel).Select ' ----- Paste source range Selection.Insert Shift:=xlDown ' Protect Sheet... ActiveSheet.Protect ' ... and re-activate secondary WB Windows(sSecondWBName).Activate Worksheets(sSecConfigWSName).Activate Application.ScreenUpdating = True "konpego" wrote in message ... Hello, I seem to miss some vital info in how to copy-paste a selection between one WS in a WB to the WS name in another WB. I wan to do this in VB and I tried to record a macro and translate it into my VB-code. This is what I have done: Application.ScreenUpdating = False ' Set copy range for WS in secondary WB mySel = gci_FirstRow_Data & ":" & nLastRowNo Rows(mySel).Select ' Copy source range Selection.Copy ' Activate primary WB where the ' selection will be pasted Windows(sPrimaryPAP).Activate ' Activate and unprotect right worksheet ' where the selection will be pasted Worksheets(sPrimConfigWSName).Activate ActiveSheet.Unprotect ' ----- Set paste range in primary WB mySel = gci_FirstRow_Data & ":" & nLastRowNo Rows(mySel).Select ' ----- Paste source range Selection.Insert Shift:=xlDown ' Protect Sheet... ActiveSheet.Protect ' ... and activate secondary WB Windows(sSecondWBName).Activate Worksheets(sSecConfigWSName).Activate Application.ScreenUpdating = True Can anybody help me with this. It seems that the range to be copied are not collected from the other WB. Thanks in advance! /konpego |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy-Paste between sheets in dif WB's
Thanks Bernie,
This tip will be useful for me in my future work! Great! /konpego |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and Paste with Macro Between sheets | Excel Discussion (Misc queries) | |||
Copy&paste of several sheets | Excel Discussion (Misc queries) | |||
copy from one sheet and paste into other sheets | Excel Discussion (Misc queries) | |||
copy & paste to all Sheets to the same cells | Excel Programming | |||
MS Excel Sheets...how to copy and paste ? | Excel Programming |