使用VBScript复制Excel 365指定列范围（M:P）：粘贴位置错误及运行时错误1004的解决求助

Let's tackle both of your problems one by one and get your script working as expected:

Problem 1: Paste Starts at M1 Instead of M4 #

The root cause here is that you're copying the entire M:P columns (EntireColumn) from your Master file. When you paste this into the target range M:P, Excel defaults to starting at the top-left cell of that range (which is M1).

To fix this, you need to:

• Copy only the used rows from Master's M:P columns (not the entire column)
• Paste directly into the target starting at M4, matching the number of rows from the source

Problem 2: Runtime Error '1004' on Paste Method #

This error usually happens due to one of these issues:

• Unnecessary worksheet activation that confuses Excel's focus
• Trying to paste into a protected sheet (you unlocked the source sheet instead of the target in your second code version)
• Reliance on the clipboard, which can be unstable in automation scripts

A better approach is to avoid using Copy/Paste entirely and directly assign values between ranges—it's faster and less error-prone.

Corrected Working Script #

Here's a revised version that fixes both issues:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts = False ' Suppress overwrite/save prompts

' Open both workbooks
Set objWorkbook = objExcel.Workbooks.Open("C:\Master.xlsx")
Set objWorkbook2 = objExcel.Workbooks.Open("C:\Copy_2022.xlsx")

' Define source and target worksheets
Set objSourceSheet = objWorkbook.Worksheets(1)
Set objTargetSheet = objWorkbook2.Worksheets(1)

' Unprotect target sheet (only needed if it's protected)
objTargetSheet.UnProtect ' Add password inside quotes if needed: UnProtect "yourPassword"

' Get the used range in source M:P columns (avoids copying empty rows)
Set sourceRange = objSourceSheet.Range("M1:P" & objSourceSheet.Cells(objSourceSheet.Rows.Count, "M").End(-4162).Row)

' Define target range starting at M4, matching the number of rows from source
Set targetRange = objTargetSheet.Range("M4:P" & 4 + sourceRange.Rows.Count - 1)

' Directly assign values (no clipboard needed)
targetRange.Value = sourceRange.Value

' Protect target sheet again if needed
objTargetSheet.Protect ' Add password inside quotes if needed: Protect "yourPassword"

' Save and clean up
objWorkbook2.Save
objWorkbook2.Close
objWorkbook.Close
objExcel.Quit

' Release objects
Set targetRange = Nothing
Set sourceRange = Nothing
Set objTargetSheet = Nothing
Set objSourceSheet = Nothing
Set objWorkbook2 = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing

Key Changes Explained: #

• No more EntireColumn: We copy only the rows with data in Master's M:P columns using End(-4162) (which is equivalent to Excel's End(xlUp)).
• Direct value assignment: Instead of Copy/Paste, we set targetRange.Value = sourceRange.Value—this avoids clipboard-related errors and is more reliable.
• Correct sheet protection: We only unprotect the target sheet (since that's where we're writing data), not the source.
• Explicit range targeting: The target range starts at M4 and matches the number of rows from the source, so data lands exactly where you want it.

内容的提问来源于stack exchange，提问作者Lando Marossi