Copying and pasting in another sheet

Question

I have my workbook with a couple of sheets. I have a button with a macro asigned. This Button is located in Sheet 2. When I press the button with the below Macro assigned, Excel shows the error

"Runtime Error '1004': Application-defined or object-defined error.

But when i run de macro in the vba editor and at the same time being located in sheet1, it does run perfectly. Its just a copy and paste . The error appears in the following line

.Range(Cells(3, 1), Cells(3 + B, 10)).Select

Here is the code And the variables are just to locate the range to copy and where to paste it. A B is the number that counts the columns to copy and C is the next row after the table ends, where the copied range will be pasted

   Dim A As Integer
   Dim B As Integer
   Dim C As Integer


A = Worksheets("Sheet1").Range("table1").Rows.Count
B = Worksheets("Sheet1").Range("A1").Value
C = A + 8

   With Worksheets("Sheet1")
   .Range(Cells(3, 1), Cells(3 + B, 10)).Select
   .Range(Cells(3, 1), Cells(3 + B, 10)).Copy
   .Range("A" & C).PasteSpecial Paste:=xlPasteValues
   End With

In a With statement you also have to use the dot (.) for Cells to refer to "Sheet1" in this case, e.g. .Range(.Cells(3, 1), .Cells(3 + B, 10)).Value. — VBasic2008
@VBasic2008 please don't answer in comments. It's even written in the comments box. — teylyn

Simon Vallejo Simon Vallejo · Accepted Answer · 2020-06-15T03:10:59

I did not use .cells as stated in one of the comments, but what I did is to put Application before the .range statement and it worked!

Application.Range(Cells(3, 1), Cells(3 + B, 10)).Select
Application.Range(Cells(3, 1), Cells(3 + B, 10)).Copy
Application.Range("A" & C).PasteSpecial Paste:=xlPasteValues

Copying and pasting in another sheet

1 Answers