Next

ActiveWorkbook.Unprotect ("xxx")
Sheets("ws4").Visible = True
Sheets("ws4").Select
.DisplayPageBreaks = False
Sheets("ws4").Range("a2:m21").Select
Selection.copy
Sheets("ws4").Visible = False
ActiveWorkbook.Protect ("xxx")
Sheets("Sheet2").Select


End With
Next

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With


If you can't tell, I want to select and copy a2:m21 on sheet ws1 (which
is hidden). Then, what I do is have a seperate workbook with a
PasteSpecial - values macro to place this info into a data sheet for
use with a PivotTable.

After the code above executes, what was selected no longer appears on
the clipboard. Ideas?


--
BigDave
------------------------------------------------------------------------
BigDave's Profile: http://www.excelforum.com/member.php...fo&userid=7741
View this thread: http://www.excelforum.com/showthread...hreadid=379486

I think code clears the clipboard - not sure...

But you can do it easier without selection

Workbooks("PasteTo").Sheets("Sheet2").Range("a2:m2 1").Value = _
Workbooks("PasteFrom").Sheets("ws4").Range("a2:m21 ").Value

just rememer that when transfering values - the 2 ranges must be the same
size.
--
steveB

Remove "AYN" from email to respond
"BigDave" wrote in
message ...


Next

ActiveWorkbook.Unprotect ("xxx")
Sheets("ws4").Visible = True
Sheets("ws4").Select
.DisplayPageBreaks = False
Sheets("ws4").Range("a2:m21").Select
Selection.copy
Sheets("ws4").Visible = False
ActiveWorkbook.Protect ("xxx")
Sheets("Sheet2").Select


End With
Next

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With


If you can't tell, I want to select and copy a2:m21 on sheet ws1 (which
is hidden). Then, what I do is have a seperate workbook with a
PasteSpecial - values macro to place this info into a data sheet for
use with a PivotTable.

After the code above executes, what was selected no longer appears on
the clipboard. Ideas?


--
BigDave
------------------------------------------------------------------------
BigDave's Profile:
http://www.excelforum.com/member.php...fo&userid=7741
View this thread: http://www.excelforum.com/showthread...hreadid=379486

Macro execution nixes the undo and the clip board. You need to process the
whole thing in onw fell swoop, not in two seperate macros. domething similar
to this (untested as my computer is tied up)...

sub CopyData()
dim wbkCopyForm as workbook
dim wbkCopyTo as workbook
dim rngCopyFrom as Range
dim rngcopyTo as Range

set wbkCopyFrom = thisworkbook
set wbkCopyTo = workbook.open ("C:\Mybook.xls")
set rngCopyfrom = wbkCopyFrom.Sheets("ws4").Range("A2:M21")
set rngCopyto = wbkCopyTo.Sheets("Sheet1").Range("A65535").end(xlU p)

rngcopyfrom.copy rngcopyto

wbkcopyto.close
end sub
--
HTH...

Jim Thomlinson


"BigDave" wrote:



Next

ActiveWorkbook.Unprotect ("xxx")
Sheets("ws4").Visible = True
Sheets("ws4").Select
.DisplayPageBreaks = False
Sheets("ws4").Range("a2:m21").Select
Selection.copy
Sheets("ws4").Visible = False
ActiveWorkbook.Protect ("xxx")
Sheets("Sheet2").Select


End With
Next

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With


If you can't tell, I want to select and copy a2:m21 on sheet ws1 (which
is hidden). Then, what I do is have a seperate workbook with a
PasteSpecial - values macro to place this info into a data sheet for
use with a PivotTable.

After the code above executes, what was selected no longer appears on
the clipboard. Ideas?


--
BigDave
------------------------------------------------------------------------
BigDave's Profile: http://www.excelforum.com/member.php...fo&userid=7741
View this thread: http://www.excelforum.com/showthread...hreadid=379486