VBA: Copy cell values to another sheet

Solved
Max49 -  
michel_m Posted messages 18903 Registration date   Status Contributeur Last intervention   -
Hello,

I have recently started learning VBA and I'm trying
I would like to copy the values from one range to another range but on a different sheet. I found several solutions to address my problem, I first used this template for my copy-paste:

Worksheets("Hebdo").Activate
Range(Cells(lig1, col), Cells(lig - 1, col + 2)).Select
Range(Cells(lig1, col), Cells(lig - 1, col + 2)).Copy

ActiveWorkbook.Sheets("GR1").Activate
ActiveWorkbook.Sheets("GR1").Range(Cells(5, 1), Cells(14, 3)).Select
ActiveWorkbook.Sheets("GR1").Paste

This template suited me perfectly until I changed the formatting of my Excel table in order to automatically print my filled tables. I find myself copying cell values of size 22 and pasting them into cells that are supposed to be written in size 11.
I read on this forum that it is easier to perform this copy-paste action by copying only the values and not the entire cell by using the following method:

Range("A1:C5").value = Range("D6:G11").value

Applied to my problem this gives:

Sheets("GR1").Range("A5:C14").Value = Sheets("Hebdo").Range(Cells(lig1, col), Cells(lig - 1, col + 2)).Value

lig and col are two integer variables that are defined beforehand.

However, this code does not work, and I get an error message.

I also read that there is a method with .PasteSpecial but I don't know how to use it.

Does anyone have a solution

Best regards

Max

2 réponses

michel_m Posted messages 18903 Registration date   Status Contributeur Last intervention   3 320
 
Hello,
apparently your ranges are not identical
A5:C14 has 2 rows
so
Sheets("GR1").Range("A5:C14") = Sheets("Hebdo").Range(Cells(lig, col), Cells(lig +2, col + 2)).Value
or
Range(Cells(lig-1, col), Cells(lig +1, col + 2)).Value

note
you had 3 variables Lig1, Lig, and col...
it is not necessary to specify .value in the target cells

Michel
0