Need help w/ a Macro

[dlb]

I have a simple macro set up to copy information from one worksheet to another. The way it run's now, it paste's the information into the cell that I clicked on while recording the macro.

The info I am coping is three numbers, first being the sheet number from which the info came, second being the item piece number, and the third being the quantity (each in their own cell). I would like this information to be copied into a seperate worksheet that has rows listed 1 thru 100. If the first number (the sheet number) is 1, I want it to paste into the row labeled 1, if the first number is 2, I want it to paste into the row labeled 2, and so on.

How would I go about doing this?

This is the maco I currently have:

Range("DD7:DF7").Select
Selection.Copy
Sheets("JOIST INPUT SHEET").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("SS").Select
Application.CutCopyMode = False

Any help would be appreciated.

dlb

[Leith Ross]

Hello Dlb,

So, you have three cells DD7, DE7, and DF7. I am assuming DD7 = Sheet Number, DE7 = Price Number and DF7 = Quantity. You want to copy these cells and then paste then onto the worksheet "JOIST INPUT SHEET".

When the cells are copied to the second worksheet "JOIST INPUT SHEET", the cells should be pasted into the row (column "D" ?) that matches DD7's value.

If I am following you correctly on this, post back and let me know if I am or not. If not, fill me in on any details I missed.

Regards,
Leith Ross

[dlb]

That's correct. The DD7 (sheet #), DE7 (PC #), DF7 (QTY) will be pasted into the "Joist Input Sheet" to cells: B2, C2, & D2 if it is being copied from the first sheet, if from the second sheet it will be B3, C3, D3, and so on.

[Leith Ross]

Hello DLB,

This is actually quite simple using VBA to do it, but difficult and messy using the macro recorder. So you can delete the code in your macro and do a copy and paste of this code.
________________________________________________________________

Dim PieceNumber
Dim Quantiity
Dim NewRow As Integer
Dim SheetNumber As Integer

SheetNumber = Range("DD7").Value
Pieceumber = Range("DE7").Value
Quantity = Range("DF7").Value

' Paste the Information to "Joist Input Sheet" in Columns "B", "C", and "D"
NewRow = SheetNumber + 1
With Worksheets("Joist Input Sheet")
.Cells(NewRow, 2).Value = SheetNumber
.Cells(NewRow , 3).Value = PieceNumber
.Cells(NewRow , 4).Value = Quantity
End with
________________________________________________________________

The worksheet name must match as you have it. You can change it if you need to. It is highlighted in blue.

Hope this helps,
Leith Ross

[dlb]

Work's great, thank you.

Have another quick question for you, I'm replacing the Piece number (DE7) with text (R, C, L, G, or B), and called it PieceType, but since it's not a value, it won't work. I've also added another number called Sqc, which is working fine.

Question is, what do I need to change to get the program to recognize the text, rather than a value in the Piece Type?

Here is what I have so far, PieceType is still set up as a value.

Dim PieceType
Dim Quantity
Dim Sqc
Dim NewRow As Integer
Dim SheetNumber As Integer

SheetNumber = Range("DD7").Value
PieceType = Range("DE7").Value
Quantity = Range("DF7").Value
Sqc = Range("DG7").Value

' Paste the Information to "JOIST INPUT SHEET" in Columns "B", "C", and "D"
NewRow = SheetNumber + 1
With Worksheets("Joist Input Sheet")
.Cells(NewRow, 2).Value = SheetNumber
.Cells(NewRow, 3).Value = PieceType
.Cells(NewRow, 4).Value = Quantity
.Cells(NewRow, 5).Value = Sqc
End With

Thanks again for the help

dlb

[Leith Ross]

Hello DLB,

Sorry for the late reply, work sometimes prevents me from answering right away. I am not sure I understand what the problem is. The changes you made to the code seem fine. PieceType should be pasting into column "C" on "Joist Input". I suspect it has something to do with using the cell's contents elsewhere that is the problem. Show me what you want and what the problem is and we can go from there.

Thanks,
Leith Ross

[dlb]

Everything in that last code I put up there is working correctly, but only if all the cells have numbers in them.

The second cell will be a letter, such as "R" or "C". With the current code, the letter will not show up in the "JOIST INPUT SHEET".

I've tried changing the PieceType from .value to .text, and NewRow from As Integer, to As String, but doesn't seem to work. I don't know much of anything about VBA, just started fiddling w/ it about 2 days ago.

dlb

[dlb]

Nevermind, it's working now, not sure why it wasn't working before.

Thank's for all the help, I appreciate it.

dlb