Macro help!

[LostinVA]

I am trying to create a macro to copy values from multiple row cells and paste those values into multiple row cells on another tab. THEN: If the user changes any of the values that were copied and pasted previously (from the first tab) and the macro is run again, the data is again copied but then pasted on the next row BELOW where the previous data was pasted.

This allows the user to run the macro to copy/paste data, change some of the values, and copy again and paste to a second row.. creating multiple rows worth of data.

The only way I can get it to work right now is the initial copy/paste macro.

Appreciate any help you can provide!

 

[Yongle]

VBA below copies entire rows of any selected cells and pastes to "OtherTab"
-it would be convenient for user if attached to a shortcut

Sub copyRows()
  Selection.Resize(, Columns.Count).Copy Sheets("OtherTab").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub

- assumes that column A in OtherTab contains a value in every row

 

[LostinVA]

Yongle -

Thank you for taking the time to look at my issue! I'm not sure I explained it well.. and I'm not sure if a macro would necessarily be the way to go..

I'm looking for a way to have a button that copies a set range of cells from one tab and pastes onto Row 5 (for example) on another tab. If the user then changes some values in the set range of cells from the first tab, when the button is pressed again, the set range of cells are copied and then pasted on the other tab on the next row - Row 6... and so on each time the button is pressed.

Hope that makes sense.. I'm an excel novice so appreciate all the help I can get!

 

[Yongle]

Try putting my code behind a button and testing it

If you want to copy the same range every time, then use something like:

Sub copyRows()
  Range("A2:T10).Copy Sheets("OtherTab").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub

The code automatically finds the next row in tab named "OtherTab"

 

[LostinVA]

Complete noob here.. So I entered your code like this but am getting a Compile Error: Syntax Error

Private Sub CommandButton1_Click()
copyRows()
Range("C2:C25).Copy Sheets("OtherTab").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub

 

[Yongle]

Private Sub CommandButton1_Click()
  Range("C2:C25[COLOR=#ff0000]"[/COLOR]).Copy Sheets("[COLOR=#ff0000]OtherTab[/COLOR]").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub

Is your other tab named "OtherTab" ?

Apologies I had omitted the closing " after the range - oops

 

[LostinVA]

No worries! Yep, I named the other tab to "OtherTab". I added the closing " after the range but am still getting the Compile Error: Syntax Error

Private Sub CommandButton1_Click()
copyRows()
Range("C2:C25").Copy Sheets("OtherTab").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub

 

[Yongle]

You did not spot that I deleted

copyRows()

copyRows was the name of the original macro I gave you

There is only one line in my macro in post#6

 

[LostinVA]

Ha. I did miss that! Worked like a charm, thank you so much!

Is there a way to modify the code so that the column data that is copied is transposed on the OtherTab as row data?? And keep the same functionality where if the user changes the column data and hits the button again, the data is pasted on the next row on the OtherTab.

Really appreciate your help with all of this!