52233

Run-time error 1004

Question:

I've run across a new VBA problem: some lines of code will only execute if the sheet in question is active, otherwise they generate an error (Run-time error '1004': Application-defined or object-defined error"). I need the code to be able to run in the background.

Perhaps someone with a fresh pair of (VBA-savvy) eyes can find what's wrong with the lines. (I'm posting them together here but they're actually from different macros.)

Dim senaste As Range Sheets("PA").Range(Cells(senaste.Row, (senaste.Column - 2)), Cells(senaste.Row, senaste.Column)).Copy Worksheets("GL").Range(Cells(3, 1), Cells(Sheets("GL").UsedRange.Rows.Count, Sheets("GL").UsedRange.Columns.Count)).Delete

Edit: Great answer below! Did the same with a different line and the coordinates went crazy, what happened? (-4 and -9 used to be -1.)

Set pt = Worksheets("PA").PivotTables("A") pt.DataBodyRange.Range(pt.DataBodyRange.Cells(-4, (pt.DataBodyRange.Cells.Columns.Count - 9)), pt.DataBodyRange.Cells((pt.DataBodyRange.Cells.Rows.Count - 6), (pt.DataBodyRange.Cells.Columns.Count - 7))).Copy

Answer1:

You're not qualifying all the ranges properly - you need to qualify all the Range and Cells calls so that they specify the same sheet:

Dim senaste As Range Sheets("PA").Range(Sheets("PA").Cells(senaste.Row, (senaste.Column - 2)), Sheets("PA").Cells(senaste.Row, senaste.Column)).Copy Worksheets("GL").Range(Worksheets("GL").Cells(3, 1), Worksheets("GL").Cells(Sheets("GL").UsedRange.Rows.Count, Sheets("GL").UsedRange.Columns.Count)).Delete

or you can tidy up by using With blocks:

Dim senaste As Range With Sheets("PA") .Range(.Cells(senaste.Row, (senaste.Column - 2)), .Cells(senaste.Row, senaste.Column)).Copy End With

and similarly:

With Worksheets("GL") .Range(.Cells(3, 1), .Cells(.UsedRange.Rows.Count, .UsedRange.Columns.Count)).Delete End With

Note the dots before all the .Range and .Cells calls.

For your updated part, you want this:

Set pt = Worksheets("PA").PivotTables("A") With pt.DataBodyRange pt.Parent.Range(.Cells(1, .Columns.Count - 3), .Cells(.Rows.Count - 1, .Columns.Count - 1)).Select End With

Note that the first Range call needs to be qualified with the Worksheet, not the DataBodyRange, otherwise you will get a range offset from where you want.

Recommend

  • Database Design - Four-Level Parent-Child Relationships
  • How can I properly import the environment from running a subcommand in Perl?
  • Infer constraints for both if and else of type equality
  • How to replace a contour (rectangle) in an image with a new image using Python?
  • Avoiding cookies while requesting static content
  • Duplicate Class in Dart
  • How is a header file being compiled into the executable file?
  • Macro to call a function
  • Why do I have to use LD_PRELOAD for libcl.2 to use XML::LibXML::Common on HP/UX?
  • Why must we Forward Declare a class and include the corresponding header file in a header file
  • Disable check for override in gcc
  • TextBox AutoCompleteStringCollection Suggest
  • Cythonized function unexpectedly slow
  • How do i disable a text box within an iframe
  • Create a multiple screen android application
  • Upper limits for fibonnacci
  • remove unicode characters but keep all special and English characters with preg_replace
  • Double-click autofill - dynamic based on adjacent cell
  • Excel VBA URLDownloadToFile Error for https Ressource
  • Rodeo UnicodeDecodeError: 'ascii' codec can't decode byte 0xef in position 0: ordinal
  • Creating a Multidimensional, Associative Array in VBScript
  • Write output of for loop to multiple files
  • Python cosine function precision [duplicate]
  • Outputting SharePoint Hyperlink Column as URL
  • In matplotlib, how do you change the fontsize of a single figure?
  • Conversion from string “a” to type 'Boolean' is not valid
  • Calling Worksheet functions from vba in foreign language versions of Excel
  • Excel's Macro-Recorder usage
  • Meteor: Do Something On Email Verification Confirmation
  • Where to put my custom functions in Wordpress?
  • How to handle AllServersUnavailable Exception
  • VBA Convert delimiter text file to Excel
  • RestKit - RKRequestDelegate does not exist
  • WPF Applying a trigger on binding failure
  • How can I remove ASP.NET Designer.cs files?
  • python draw pie shapes with colour filled
  • Is there any way to bind data to data.frame by some index?
  • Reading document lines to the user (python)
  • How can i traverse a binary tree from right to left in java?
  • Why do underscore prefixed variables exist?