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


New Member
Nov 17, 2005
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 :confused:

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)) :biggrin:

Ok to visualise this

WO is laid out like so
1st range of cells

A5 | B5
Wingding1 | 100.00
WinBat6 | 50.00
SomePart10 | 200.50

2nd Group
F5 | G5
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
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
'    MsgBox "No Columns left!", vbCritical
'End If

'On Error Resume Next

'If PasteRng.Row <> i Then GoTo 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

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back