Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error 1004, paste method of Worksheet class failed | Excel Programming | |||
Copy paste to another Workbook, Error 1004 | Excel Programming | |||
Error 1004 Paste method of Worksheet class failed | Excel Programming | |||
runtime error 1004 paste method of worksheet class failed | Excel Programming | |||
Run Time error 1004 Paste Method of Worksheet Class Failed | Excel Programming |