How Can You Fix PC Dll Errors Effectively?: Fast Solution to Problem: Copy and Paste to a non-active sheet using vba

2016年9月23日星期五

Fast Solution to Problem: Copy and Paste to a non-active sheet using vba

Copy and Paste to a non-active sheet using vba

Hi to All,

When I run the macro below from sheet("Weekly Total") it create the following error:

Run time error 1004: Application - Defined or Object -defined error
When I run the macro from sheet("Sheet1") it works fine

Sub test1()
'Copy Weekly Call Count Data
Worksheets("Sheet1").Range(Cells(4, 12), Cells(15, DatePart("ww", Date, vbSunday))) = _
Sheets("Weekly Total").Range("H3:H14").Value
'Copy Weekly Percent data
Worksheets("Sheet1").Range(Cells(21, 12), Cells(31, DatePart("ww", Date, vbSunday))) = _
Sheets("Weekly Total").Range("I3:I13").Value

I would appreciate any help

Thanks in advance

Anwsers to the Problem Copy and Paste to a non-active sheet using vba

Download SmartPCFixer to Fix It (Free)

If you define a range like this:

Worksheets("Sheet1").Range(Cells(4, 12), Cells(15, DatePart("ww", Date, vbSunday)))

The "Cells()" part will allways reference the active sheet, and not necessarily Sheet1. You get an error because (when sheet1 isn't active) the "Cells()" parts define a range not on sheet1.
This should work:

with Worksheets("Sheet1")
.Range(.Cells(4, 12), .Cells(15, DatePart("ww", Date, vbSunday))) .value= _
Sheets("Weekly Total").Range("H3:H14").Value
end with

Note the dot before "cells()" links it to Sheet1 and not the active sheet.

Tim

Note: It is recommended that you back up your files and folders before attempting this step:

Move the mouse cursor over to the upper right side of the screen.
A menu will pop up on the left. Click on the "Settings" feature on the menu.
On the Settings window, click on the "Change PC settings."
Now, left click on the "Update and recovery" feature.
For the next step, left click on the "Recovery" feature.
There is an option here saying "Refresh your PC without affecting your files." There, left click on the "Get started" button.
Now, follow the instructions on the screen to finish the Refresh process.
After the Refresh process has finished restart your Windows 8.1 or Windows 8 device.
Check again to see if the error Copy and Paste to a non-active sheet using vba has been fixed.

If you do not possess the technical expertise required to accomplish this yourself or do not feel comfortable doing so, download and install a powerful automated tool to get the job done.