VBA: Copy cell values to another sheet
Solved
Max49
-
michel_m Posted messages 18903 Registration date Status Contributeur Last intervention -
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
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
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
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