Transferring data to subsequent rows in a different sheet

[portiany8]

I have rooted through the forum and have found several items that are similar to the solution that I'm hunting, but, unfortunately, I don't have the skill with excel to be able to extrapolate that information into a useable form. Any help will be greatly appreciated! I'm using Excel 2002.

Sheet1 contains several dropdowns (locations A4, C4, E4, H4, A7, E7, H7, etc.) that are pulling from lists in Sheet2. Ideally, I'd like to have a button macro on Sheet1 that will dump the information pulled from the dropdowns in Sheet1 into Sheet3 Row1 (Sheet1A4 = Sheet3A1, Sheet1C4 = Sheet3B1,,,, Sheet1H7 = Sheet3G1, etc.). The next time the macro is run, I need the info from Sheet1 to be transferred to Sheet3 Row2. Each time after that, I need the info to drop to the subsequent row in Sheet3. There will be several locations on Sheet1 that will be blank each time the macro is run.

Again, any help that anyone can give me will be greatly appreciated! I know what I want to happen, but I'm just not competent enough in excel to be able to get the results that I'm looking for. If my explanation seemed like gibberish, or if you'd just like me to email you the spreadsheet, then just let me know and I'll get it out to you asap. Thanks!

 

[Iggy]

Dear portiany8,
I am fairly New to this as well so TEST, TEST, TEST before you put this into play. But Please let me know if this works for you. I just had a similar need and I modified what works for me to try and match yours.

I'm making a few assumptions here.
1) that the cells you provided that are on "Sheet1" are the only ones we are worrying about. A4, C4, E4, H4, A7, C7, E7, H7
2) That there are no other formulas in the "Target Rows" on Sheet 3. If there are they will get over written with what I have here.
~If there are no Formulas at all on Sheet3 there is an even easier way of doing it. But either way it should work for you.



Option Explicit


Sub Next_Row()

'
'
' Find bottom Row, Insert Formulas in the next blank row. Then Copy and paste the values into the Rows,
' This is based on there being data in Colum A on your Sheet 3 ALL THE TIME.
' It uses the Offset Command to move the cursor around on your Sheet3
' In the Formula it uses "R1C1" instead of A1. R=Row C=Column so R3C5 would be Cell E3.

'
Dim TargetCell As Object
Sheets("Sheet3").Select
Set TargetCell = Range("A65536").End(xlUp)
TargetCell.Select
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=Sheet1!R4C1"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=Sheet1!R4C3"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=Sheet1!R4C5"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=Sheet1!R4C8"

'Copy and Paste
Dim TargetRow As Object
Set TargetRow = Range("A65536").End(xlUp)
Rows(TargetRow.Row).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Now for Row 7 Fields
Sheets("Sheet3").Select
Set TargetCell = Range("A65536").End(xlUp)
TargetCell.Select
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=Sheet1!R7C1"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=Sheet1!R7C3"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=Sheet1!R7C5"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=Sheet1!R7C8"
ActiveCell.Offset(0, 1).Select

'Copy and Paste
Set TargetRow = Range("A65536").End(xlUp)
Rows(TargetRow.Row).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet1").Select


End Sub

 

[portiany8]

It didn't work exactly, but I see what you've done in the coding that I didn't know how to do, so I think I might be able to make it work. Thanks so much for the help!

FYI: Instead of running the entire row in a straight line, the macro dumped the first four cells in the first row of Sheet3, and the next cells in various locations on the next row. Everything else was fairly flawless though. It dropped down to the next available line every time. The only other problem I had with it was that instead of just leaving a blank space on Sheet3 when there is a blank value in Sheet1, it entered a "0" on sheet three.

 

[Iggy]

My Bad. I mis-read your original request, and I guess I assumed since the values on Sheet1 were in 2 different rows that you wanted the result of the macro to be in 2 rows as well on Sheet3. (The 'Various locations' has me a bit perplexed though I was trying to put it in 2 rows of 4 A-D).
Nevertheless - If you can rewrite the parts you need now that is good.
Since the result is all in one row, you can remove the middle 'Copy and Paste section, Down to just before the comment 'Now for Row 7 Fields....
but at the end you will need to Declare the variable again. put this right after the 'copy and paste comment near the end:
Dim TargetRow As Object

As for the 0 you may be able to write in an 'If/Then statement' to not include the blank ones.

If you have any other questions - hit me back.

 

[portiany8]

Iggy,
Again, thanks for the help. With 20-30 more minutes of work, I was able to get exactly the workbook I was hoping for. I just wanted to put the finished product up on the board so that I might be able to save someone else a little bit of hassle.


Sub Next_Row()
Dim TargetCell As Object
Sheets("LOGSHEETSUMMARY").Select
Set TargetCell = Range("A65536").End(xlUp)
TargetCell.Select
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=FIRSTAIDLOGSHEET!R4C1"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=FIRSTAIDLOGSHEET!R4C3"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=FIRSTAIDLOGSHEET!R4C5"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=FIRSTAIDLOGSHEET!R4C8"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=FIRSTAIDLOGSHEET!R7C1"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=FIRSTAIDLOGSHEET!R7C5"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=FIRSTAIDLOGSHEET!R7C8"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=FIRSTAIDLOGSHEET!R10C1"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=FIRSTAIDLOGSHEET!R10C5"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=FIRSTAIDLOGSHEET!R10C8"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=FIRSTAIDLOGSHEET!R11C1"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=FIRSTAIDLOGSHEET!R11C5"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=FIRSTAIDLOGSHEET!R11C8"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=FIRSTAIDLOGSHEET!R12C1"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=FIRSTAIDLOGSHEET!R12C5"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=FIRSTAIDLOGSHEET!R12C8"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=FIRSTAIDLOGSHEET!R13C1"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=FIRSTAIDLOGSHEET!R13C5"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=FIRSTAIDLOGSHEET!R13C8"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=FIRSTAIDLOGSHEET!R14C1"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=FIRSTAIDLOGSHEET!R14C5"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=FIRSTAIDLOGSHEET!R14C8"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=FIRSTAIDLOGSHEET!R17C1"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=FIRSTAIDLOGSHEET!R20C1"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=FIRSTAIDLOGSHEET!R23C2"
'Copy and Paste
Dim TargetRow As Object
Set TargetRow = Range("A65536").End(xlUp)
Rows(TargetRow.Row).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("FIRSTAIDLOGSHEET").Select

End Sub



I recorded a macro that cleared the entry cells in the first sheet to make data entry a little bit easier. Getting rid of the "0"s was just a matter of unchecking a "Display zero values" box.

 

[Iggy]

Looks good. Glad I was able to assist.