Forum Discussion
macro for cutting and pasting values
Hello! I am very new to VBA and Macros in general, so any feedback is greatly appreciated!
I am essentially trying to make a title page for my workbook. I would like there to be a button labeled "submit data" that will cut and paste the data from a cell on the title page, to multiple other locations on multiple different sheets.
I can not use basic excel formulas (cell value = to) because I get #SPILL! errors. Reformatting and fixing the #SPILL! errors does not seem to be an option, because it would be significant effort to re-format the sheets based on the solutions excel gives for #SPILL! errors (split into merged cells section).
I tried using the "Record Macro" button on the Developer tab, and it did not work as I hoped it would. The main issue I see in the macro that was recorded, is it always pastes the data that was present when the macro was recorded (in this example, "xxxxx-xx" was cut and pasted when recording). See code below:
Sub Test()
'
' Test Macro
'
'
Range("F2:J2").Select
ActiveCell.FormulaR1C1 = ""
Sheets("Paste data 1").Select
Range("A13:D13").Select
Selection.FormulaR1C1 = "xxxxx-xx"
Sheets("Title Page").Select
Range("F3:J3").Select
End Sub
If I change the data in the cell and re run the macro, it still pastes the "xxxxx-xx". Is there a much easier way to do this, or a different formula I can use on that line?
Thank you for your time!
First off I seriously doubt you can't do this using simple cell formula(s). If needed you could fill ranges or individual cells and don't use ranges to start with (i.e. no spill possible). Do you realize that you are starting with 5 cells and pasting into 4 cells (maybe that is why you have spill error?).
As for easier VBA code:
ThisWorkbook.Sheets("copyFromSheet").Range("F2:J2").copy ThisWorkbook.Sheets("Paste data1").range("A13:D13")note the above is all 1 line with a single space (i.e. RANGE.copy DESTINATION )
3 Replies
Can you tell us:
- Which cells on the Title Page sheet do you want to copy?
- Where do you want to copy them to?
The macro that you posted doesn't provide a real clue: F2:J2 consists of 5 cells, but A13:D13 only 4 cells, so you can't copy one to the other...
First off I seriously doubt you can't do this using simple cell formula(s). If needed you could fill ranges or individual cells and don't use ranges to start with (i.e. no spill possible). Do you realize that you are starting with 5 cells and pasting into 4 cells (maybe that is why you have spill error?).
As for easier VBA code:
ThisWorkbook.Sheets("copyFromSheet").Range("F2:J2").copy ThisWorkbook.Sheets("Paste data1").range("A13:D13")note the above is all 1 line with a single space (i.e. RANGE.copy DESTINATION )
- Both of these solutions worked, thank you. Starting with 5 cells and pasting into 4 was the reason for the spill error.
