Copy-Paste between sheets in dif WB's

[Guest]

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

 

[Bernie Deitrick]

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

 

[Guest]

Thanks Bernie,

This tip will be useful for me in my future work!
Great!

/konpego