VBA Copy - Paste - Paste special not working-CodePudding

Good morning, I'm currently learning VBA coding, so I'm not sure if I'm doing something wrong. I have some problems with Paste and PasteSpecial method. Even when running a script from a recorded macro it is showing an error called "Runtime error '1004': Application-defined or object-defined error". The simple and trivial recorded macro is the following.

Sub Macro1()
'
' Macro1 Macro
'

'
    Sheets("Aggressive").Select
    ActiveCell.Range("A1:D15").Select
    Selection.Copy
    ActiveCell.Offset(18, 7).Range("A1").Select
    Sheets("All Portfolios").Select
    ActiveCell.Offset(2, -3).Range("A1").Select
    ActiveSheet.Paste
End Sub

I'm not sure why this is happenening, and the same happens for written "ex novo" code and also for PasteSpecial method applied to a Range obj. Is there something I am missing? I also want to specify that it worked for some time (both Paste and PasteSpecial) then out of the blue this error happened and keeps happening. I tried some solution i found on the internet but it still keeps happening and i don't think it's normal that a recorded macro doesn't work. Thank you in advance!

---Edit Thank you very much @Dominique, this was in fact an error. I've already read the URL you posted when looking for a solution for my problem, but since for now my programs are very simple I didn't apply the "variables" way to write the codes yet. However i have just the same some problems with the copy and paste method, even when trying to apply the specified url method of variables instead of selection. The previously posted code was from a recorded macro so honestly idk why it produced a negative offset. However the following is a code i wrote while attending an online vba course, and I can't grasp if the problems lays again in offset method use or something else.

Sub GenRep()
Dim x As Integer
For x = 1 To Worksheets.Count - 1
Worksheets(x).Select
Range("B6").Select
Selection.CurrentRegion.Select
Range("B2000").Select
Selection.End(xlUp).Select
ActiveCell.Offset(2, 0).Select
ActiveCell.Value = Worksheets(x).Name & "portfolio"
ActiveCell.Offset(2, 0).Select
ActiveSheet.Paste
Next x
End Sub

CodePudding user response：

I believe this is the problem:

Sheets("All Portfolios").Select
ActiveCell.Offset(2, -3).Range("A1").Select

When you select a sheet, you automatically select cell "A1", which does not have a negative offset (-3).

I also advise you to check this URL, where you can learn how to avoid using too much Select, Copy and Paste in Excel.

CodePudding user response：

I don´t know what are you exactly trying to achieve, It seems that you recorded your macro with "use relative references" activated. The negative offset is normal. You didn't select A1 when you recorded the macro, that's why it has that negative offset. You could have selected from column D to the right, any column. Also you recorded useless steps. Maybe if you unmark the "use relative references", you will achieve what you really want to do, I couldn't really know for sure.

I will explain what your code does:

Sheets("Aggressive").Select selects the sheet "aggressive"

ActiveCell.Range("A1:D15").Select selects the active cell (the one that was selected the last time on the sheet) and then it will select the "A1:D15" from the actual selection, for example: if your selection was on [E5] it will select the range E5:H19. In this particular case A1 is the E5. If you had put "B1:D15" instead of "A1:D15" it would have selected "F5:H19"

Selection.Copy copies the last selection

ActiveCell.Offset(18, 7).Range("A1").Select selects 18 rows down from the previous selection and seven columns to the right (in my case, from [E5], it would be [L22]; which is useless, beacause it's a selection before the following selection. Note that the Range("A1") is refering to the [L22] (in my example) itself as described above.

Sheets("All Portfolios").Select selects "All Portfolios" sheet

ActiveCell.Offset(2, -3).Range("A1").Select selects the last selected cell on the sheet plus two rows, minus three columns. So, if you were on A1, as Dominique said, it hasn't negative offset, so that's why there's usually an error. But, if the last selection on "All Portfolios" would have been on [F3], for example, it would have selected [C5].

ActiveSheet.Paste It pastes the copied values/formulas/whatever on the last selection (the one from above). If we have used my example, it would have copied the values on the range ("C5:F19")

Having Said that, here is an example for copy paste

Sheets("Aggressive").Range("yourRange").Copy
Sheets("All Portfolios").Range("yourRange").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipBlanks:=False, Transpose:=False
Application.CutCopyMode = False`

You can look up for the different types of "Paste" and "Operation

Second code:

For x = 1 To Worksheets.Count - 1 Worksheets.count depends on how many worksheets there are on your workbook, so If you have one, the code will do nothing (1-1=0). If you have 2 worksheets the code will work on the first one (2-1=1), if you have three worksheets it will work on the first two worksheets (3-1=2), if you have four worksheets it will work on the first three worksheets (4-1=3), and so on and so forth (it's a loop)

Crude example of the loop:

Worksheets(1).Select 'selects first worksheet
Range("B6").Select
Selection.CurrentRegion.Select
Range("B2000").Select
Selection.End(xlUp).Select
ActiveCell.Offset(2, 0).Select
ActiveCell.Value = Worksheets(x).Name & "portfolio"
ActiveCell.Offset(2, 0).Select
ActiveSheet.Paste

Worksheets(2).Select 'selects second worksheet
Range("B6").Select
Selection.CurrentRegion.Select
Range("B2000").Select
Selection.End(xlUp).Select
ActiveCell.Offset(2, 0).Select
ActiveCell.Value = Worksheets(x).Name & "portfolio"
ActiveCell.Offset(2, 0).Select
ActiveSheet.Paste 

Worksheets(3).Select 'selects third worksheet
Range("B6").Select
Selection.CurrentRegion.Select
Range("B2000").Select
Selection.End(xlUp).Select
ActiveCell.Offset(2, 0).Select
ActiveCell.Value = Worksheets(x).Name & "portfolio"
ActiveCell.Offset(2, 0).Select
ActiveSheet.Paste 

(...)

The second selection below is not useful. The "B6" and "B2000" selections aren't tidy. Besides, using select is not recommended at all, you should avoid it. Dominique quoted that famous article/question, you should check it out.

Range("B6").Select
Selection.CurrentRegion.Select
Range("B2000").Select

Notice that there is no copy action on the code. You have to copy something manually for it to work.

I reiterate, there is not much information about what you're trying to achieve. If you could be more specific...