Forum Discussion
Excel Macro
I am trying to move values from one sheet to another using a macro,
it works when I want to move the whole row as it is but I need to rearrange it a bit before I copy it to the next sheet, I have tried to run the command 3 times to do this but it doesn't seem to place the values in the correct place i.e. the next available blank row.
This is the command i am using.
Sheets("Front Sheet").Select
Range("AZ6:AZ6" & Cells(Rows.Count, "I").End(xlUp).Row).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Results").Select
Range("I2").Select
With ActiveCell
Range("I" & Rows.Count).End(xlUp).Offset(1).Select
End With
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Front Sheet").Select
It looks like there is an issue with the way you are trying to copy and paste the values. You can rearrange the code to directly refer to the ranges without using Select and Copy.
Here is an example:
Vba code is untested, please backup your file.
Dim frontSheet As Worksheet Dim resultsSheet As Worksheet Dim lastRow As Long Set frontSheet = ThisWorkbook.Sheets("Front Sheet") Set resultsSheet = ThisWorkbook.Sheets("Results") ' Find the last row in column I of Front Sheet lastRow = frontSheet.Cells(frontSheet.Rows.Count, "I").End(xlUp).Row ' Copy values from column AZ in Front Sheet frontSheet.Range("AZ6:AZ" & lastRow).Copy ' Find the next available row in column I of Results Sheet lastRow = resultsSheet.Cells(resultsSheet.Rows.Count, "I").End(xlUp).Offset(1, 0).Row ' Paste values to the next available row in column I of Results Sheet resultsSheet.Range("I" & lastRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False ' Clear the clipboard Application.CutCopyMode = FalseThis code finds the last row in column I of the "Front Sheet," copies the values from column AZ, and pastes them to the next available row in column I of the "Results" sheet. The Application.CutCopyMode = False line clears the clipboard after the paste operation. The text and steps were edited with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
Thanks for the reply,
the issue i am having is i have a row with columns from A-F i want to copy A-B and paste to A-B and copy
E-F and paste to C-D and copy C-D and paste to E-F if that make sense.
=CHOOSECOLS(A1:F10,1,2,5,6,3,4)
Does this formula return the intended result? For illustration the dataset and the result is in the same sheet. After the result is populated you can copy the result and paste only the values in order to remove the formula. In my opinion this is easier than a macro however the CHOOSECOLS function is only available in Office 365 and Excel for the web.
