Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 29
Default VBA error 1004 on With worksheet.Paste

I have a worksheet with data that I sometimes want to copy to 3 other worksheets rather than entering the data on each sheet individually.

I just recently protected the worksheets and am getting "error 1004", when running the macro. I added the worksheet.unprotect and protect method to account for this, but am getting the error at the ".paste" portion of the macro.

Anyone know whats causing it? Code Below:

ub mastertermscopy(ByVal wksname As String, ibox() As Integer)

Dim z As Byte

Worksheets(wksname).Unprotect Password:="xxxxxx"
Worksheets(wksname).Range("a11:h51").Copy

For z = 3 To 5

If Worksheets(z).Name = wksname Then

Else
Worksheets(z).Unprotect Password:="xxxxxx"

With Worksheets(z)
.Select
.Range("a11:h51").Select
.Paste <-------------------------debugger points to this part of code
.ComboBox1.ListIndex = ibox(0)
.ComboBox2.ListIndex = ibox(1)
.ComboBox3.ListIndex = ibox(2)
.ComboBox4.ListIndex = ibox(3)
.CheckBox1.Value = CBool(ibox(4))
.CheckBox2.Value = CBool(ibox(5))
.CheckBox3.Value = CBool(ibox(6))
.CheckBox4.Value = CBool(ibox(7))
.CheckBox5.Value = CBool(ibox(8))
.Range("m7").Select
End With

Worksheets(z).Protect Password:="xxxxxx"

End If
Next z

Worksheets(wksname).Select
Worksheets(wksname).Protect Password:="xxxxxx"


End Sub

Any help would be appreciated....AJ
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default VBA error 1004 on With worksheet.Paste

This may seem a bit 'long-in-tooth' for you, but here's how I handle
multi-sheet projects for doing similar tasks...

A:
Each sheet has its own UserInterface (UI) settings stored as a local
scope defined name. In this case the UI setting for sheet protect is
named "uiProtect" and its RefersTo is either 0 (zero) or 1 where 1
indicates the sheet gets protected.

B:
When the workbook opens each sheet gets its uiProtect setting applied
as follows;

Protection gets removed...
wks.Unprotect PWRD
'//where PWRD is a global scope string constant

If wks.Names("uiProtect").RefersTo 0 then 'protection gets reset...
Call wsProtect(wks.Name)
End If

The sheet protection is reset when the workbook opens because I make
use of the *UserInterfaceOnly* option so I don't have to 'toggle' sheet
protection whenever code makes changes to locked cells. Protection gets
applied according to Excel version as far as its Options go...


Sub wksProtect(Optional Wks As Worksheet)
' Protects specified sheets according to Excel version.
' Assumes Public Const PWRD as String contains the password, even if
there isn't one.
'
' Arguments: Wks [In] Optional. Ref to the sheet to be protected.
' (Defaults to ActiveSheet if missing)

If Wks Is Nothing Then Set Wks = ActiveSheet
On Error Resume Next
With Wks
If Val(Application.VERSION) = 10 Then
'Copy/paste the desired parameters above the commented line.
.Protect Password:=PWRD, _
DrawingObjects:=False, Contents:=True, Scenarios:=True, _
UserInterfaceOnly:=True, _
AllowFiltering:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowFormattingCells:=True ', _
AllowDeletingColumns:=True, _
AllowDeletingRows:=True, _
AllowInsertingColumns:=True, _
AllowInsertingHyperlinks:=True, _
AllowInsertingRows:=True, _
AllowUsingPivotTables:=True
Else
.Protect Password:=PWRD, _
DrawingObjects:=False, Contents:=True, Scenarios:=True, _
UserInterfaceOnly:=True
End If
' .EnableAutoFilter = True
' .EnableOutlining = True

' .EnableSelection = xlNoRestrictions
' .EnableSelection = xlUnlockedCells
.EnableSelection = xlNoSelection
End With

End Sub

The important line is the one where the .Protect arguments are set. All
other 'Allow/Enable' option lines are uncommented as desired. (for v10
and later, the 'Allow' options need to be placed above the comment
indicator (see 4th option) as desired.

In your scenario I'd code this task something like this...

Sub MasterTerms_Copy(WksName$, sNames$, sCriteria$)
' Copies data to other sheets and sets sheet controls.
'
' Args: WksName The sheetname of the source data.
' sNames Comma delimited string of the sheetnames to copy
' data to.
' sCriteria Comma delimited string of settings to apply to
' sheet controls.

Dim vData, vSettings, ws

vData = Sheets(WksName).Range("CopyData")
vSettings = Split(sCriteria, ",")

For Each ws in Split(sNames, ",")
With Sheets(ws)
.Range("CopyData").Resize(LBound(vData), LBound(vData, 2) = vData
.ComboBox1.ListIndex = CLng(vSettings(0)
.ComboBox2.ListIndex = CLng(vSettings(1)
.ComboBox3.ListIndex = CLng(vSettings(2)
.ComboBox4.ListIndex = CLng(vSettings(3)

.CheckBox1.Value = CBool(vSettings(4))
.CheckBox2.Value = CBool(vSettings(5))
.CheckBox3.Value = CBool(vSettings(6))
.CheckBox4.Value = CBool(vSettings(7))
.CheckBox5.Value = CBool(vSettings(8))
.Range("AfterCopy").Select
End With 'Sheets(ws)
Next 'ws
End Sub

...where the source/target ranges are local scope defined names on each
sheet so I don't have to revise hard-code refs if the range[s] change
size or location over time for any reason.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default VBA error 1004 on With worksheet.Paste

Note that the RefersTo will include "=" and so...

If wks.Names("uiProtect").RefersTo = "=1" then 'reset protection
Call wsProtect(wks.Name)
End If

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 29
Default VBA error 1004 on With worksheet.Paste

On Thursday, March 17, 2016 at 10:43:17 PM UTC-7, GS wrote:
This may seem a bit 'long-in-tooth' for you, but here's how I handle
multi-sheet projects for doing similar tasks...

A:
Each sheet has its own UserInterface (UI) settings stored as a local
scope defined name. In this case the UI setting for sheet protect is
named "uiProtect" and its RefersTo is either 0 (zero) or 1 where 1
indicates the sheet gets protected.

B:
When the workbook opens each sheet gets its uiProtect setting applied
as follows;

Protection gets removed...
wks.Unprotect PWRD
'//where PWRD is a global scope string constant

If wks.Names("uiProtect").RefersTo 0 then 'protection gets reset...
Call wsProtect(wks.Name)
End If

The sheet protection is reset when the workbook opens because I make
use of the *UserInterfaceOnly* option so I don't have to 'toggle' sheet
protection whenever code makes changes to locked cells. Protection gets
applied according to Excel version as far as its Options go...


Sub wksProtect(Optional Wks As Worksheet)
' Protects specified sheets according to Excel version.
' Assumes Public Const PWRD as String contains the password, even if
there isn't one.
'
' Arguments: Wks [In] Optional. Ref to the sheet to be protected.
' (Defaults to ActiveSheet if missing)

If Wks Is Nothing Then Set Wks = ActiveSheet
On Error Resume Next
With Wks
If Val(Application.VERSION) = 10 Then
'Copy/paste the desired parameters above the commented line.
.Protect Password:=PWRD, _
DrawingObjects:=False, Contents:=True, Scenarios:=True, _
UserInterfaceOnly:=True, _
AllowFiltering:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowFormattingCells:=True ', _
AllowDeletingColumns:=True, _
AllowDeletingRows:=True, _
AllowInsertingColumns:=True, _
AllowInsertingHyperlinks:=True, _
AllowInsertingRows:=True, _
AllowUsingPivotTables:=True
Else
.Protect Password:=PWRD, _
DrawingObjects:=False, Contents:=True, Scenarios:=True, _
UserInterfaceOnly:=True
End If
' .EnableAutoFilter = True
' .EnableOutlining = True

' .EnableSelection = xlNoRestrictions
' .EnableSelection = xlUnlockedCells
.EnableSelection = xlNoSelection
End With

End Sub

The important line is the one where the .Protect arguments are set. All
other 'Allow/Enable' option lines are uncommented as desired. (for v10
and later, the 'Allow' options need to be placed above the comment
indicator (see 4th option) as desired.

In your scenario I'd code this task something like this...

Sub MasterTerms_Copy(WksName$, sNames$, sCriteria$)
' Copies data to other sheets and sets sheet controls.
'
' Args: WksName The sheetname of the source data.
' sNames Comma delimited string of the sheetnames to copy
' data to.
' sCriteria Comma delimited string of settings to apply to
' sheet controls.

Dim vData, vSettings, ws

vData = Sheets(WksName).Range("CopyData")
vSettings = Split(sCriteria, ",")

For Each ws in Split(sNames, ",")
With Sheets(ws)
.Range("CopyData").Resize(LBound(vData), LBound(vData, 2) = vData
.ComboBox1.ListIndex = CLng(vSettings(0)
.ComboBox2.ListIndex = CLng(vSettings(1)
.ComboBox3.ListIndex = CLng(vSettings(2)
.ComboBox4.ListIndex = CLng(vSettings(3)

.CheckBox1.Value = CBool(vSettings(4))
.CheckBox2.Value = CBool(vSettings(5))
.CheckBox3.Value = CBool(vSettings(6))
.CheckBox4.Value = CBool(vSettings(7))
.CheckBox5.Value = CBool(vSettings(8))
.Range("AfterCopy").Select
End With 'Sheets(ws)
Next 'ws
End Sub

..where the source/target ranges are local scope defined names on each
sheet so I don't have to revise hard-code refs if the range[s] change
size or location over time for any reason.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


Garry,

Thanks very much for the explanation and fix! IT works great!

AJ
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default VBA error 1004 on With worksheet.Paste

Thanks very much for the explanation and fix! IT works great!

Glad to help! I appreciate the feedback...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Error 1004, paste method of Worksheet class failed Jim at Michoud Excel Programming 3 August 14th 09 02:30 PM
Copy paste to another Workbook, Error 1004 Steve[_87_] Excel Programming 7 April 16th 07 05:49 PM
Error 1004 Paste method of Worksheet class failed skvabbili Excel Programming 1 May 4th 06 11:03 PM
runtime error 1004 paste method of worksheet class failed wilsoj Excel Programming 12 August 10th 05 08:20 PM
Run Time error 1004 Paste Method of Worksheet Class Failed Ken Nunn Excel Programming 3 June 29th 04 03:23 PM


All times are GMT +1. The time now is 12:02 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"