Stuck: Copy Cells based on specific cell to another sheet ..

[MrKB]

Hi Everyone!

Ok I'm stuck and this is my first post. I have pulled from a lot of fine information from everyone on this board to get me through past issues but I need something a bit more specific than solutions I have seen answered. Plus I really need to learn this concept - bear with me Im a database guy

First preliminaries - I have a workbook (SHEET "WO") that is modeled after a form (its for a repair shop). This workbook has several sheets that are imported from a database that holds current inventory. The key thing here is the stock number. One sheet is left alone with this raw data (SHEET "3"), one sheet is sorted by stock number and using VLOOKUP feeds the "WO" Sheet. So the user enters the stock num on "WO" and it pulls all the relevant stuff... All this is fine.

Heres my issue...
I need the data entered into "WO" to go back into RAW - SHEET3.
What I have is 2 monkeys -
-I get the stock number in a specific cell
-I have 2 ranges of cells in WO that I need to copy to the RAW Sheet
-The data is allways changing on the RAW sheet. so code takes the stock number (from WO) then finds the proper row on RAW, then has to find the first empty cell out of a certian number of collums.

((RAW is then sent back to a DOS app ... with very detailed variations))

Ok to visualise this

WO is laid out like so
1st range of cells

A5 | B5
DISCRIPTION | PRICE
Wingding1 | 100.00
WinBat6 | 50.00
SomePart10 | 200.50
...

2nd Group
F5 | G5
LABOR | COST
Install Ding | 50.00
Install Bat | 10.00
....

This goes to RAW which is like so -
A1 | B1 | C1 | D1 | E1 | F1 | G1 |...| X1 | Y1 | Z1 |
B123 | Fried | F100 | 10.00 | 0 | 0 | 0 |...|work| 0 | 0 |
B124 | Doog | Z128 | 0 | 0 | 0 | 0 |...|0 | 0 | 0 |

If the stock number was B123 in the above 'Wingding would be copied to Y1 and the price would go to E1 with the rest of the data following. If it was B124 it would go to X1 and D1 with the rest following...

I appologize in advance for being so long winded and for the sloppyness of the following code. This code was working fine in testing with everything on the same sheet - WO range and RAW - and with only 2 cells to copy (no loop for the whole range). It will find the empty slot. But now that the data is on 2 different sheets I am not referenceing the cell properly to paste into.

Public Sub PostLines()

Dim PasteRng As Range
Dim PasteNum As Range
Dim i As Long
Dim x As Integer
Dim myCol As Variant
Dim lookUpCol As Variant
Dim stockNum As Variant
Dim toPost As String
Dim toPostNum As String

Dim foundRow As Integer

' Stock Num is in Col A
lookUpCol = "A"

' Pick up the Stock Num to Search for
'Worksheets("WO").Activate
stockNum = ActiveSheet.Range("C1").Value

' Pick up the line item to Post
' This will become an Array to pick up all Line Items
toPost = ActiveSheet.Range("C3").Value
toPost = UCase(toPost)
toPostNum = ActiveSheet.Range("D3").Value

' Array for columns to paste to
myCol = Array("BQ", "BR", "BS", "BT", "BU")
myColNums = Array("AU", "AV", "AW", "AX", "AY")

' Find the right row first by matching the Stock Num
For i = 1 To 400
    If Sheets(3).Cells(i, 1) = stockNum Then
        Debug.Print "Found stockNum in row " & i
        foundRow = i
                
        For x = LBound(myCol) To UBound(myCol)
        Debug.Print foundRow & myCol(x)
            If Cells(foundRow, myCol(x)) = "" Then
                Set PasteRng = Sheets(3).Cells(foundRow, myCol(x))
                Set PasteNum = Sheet3(3).Cells(foundRow, myColNums(x))
                Debug.Print "Posting cell:" & foundRow & myCol(x) & "::" & myColNums(x)
                PasteRng.Value = toPost
                PasteNum.Value = toPostNum
                'Sheets(3).Cells(foundRow, myCol(x)).Value = toPost
                'Sheets(3).Cells(foundRow, myColNums(x)).Value = toPostNum
            Exit For ' exit loop
            
            End If
        Next x
    
    End If
    
Next i

'MsgBox "No Columns left to Post into!", vbCritical

'If PasteRng.Row <> "" Then

'    Debug.Print "First Empty IS:" & PasteRng.Row & PasteRng.Column
'    PasteRng.Value = toPost
'Else
'    MsgBox "No Columns left!", vbCritical
'End If

'On Error Resume Next

'If PasteRng.Row <> i Then GoTo NoCol

'End

'NoCol:
'MsgBox "No Columns left to Post into!", vbCritical

    
End Sub

I was looking to do another sub which reads the ranges and calls PostLines so that it handles the finding of the empty slot to start pasting, but everything might as well be together.

A swift kick in the right direction is what I need -

Thanks Again in Advance