TechArena Community

TechArena Community (
-   Software Development (
-   -   excel spread sheet data transfer help (

elijah58 29-08-2012 02:59 AM

excel spread sheet data transfer help
Hi Guys,
I need some help with a excel spread sheet data transfer. I will try to explain the best I can.

Ok here goes, what I need to do is I have a spare parts list that has 4 columns and many rows. the columns are as follows:
Part #, Description, Price and Quantity.
And I have an order form on another sheet in the same workbook. what I want to do is to enter the quantity in the Quantity cloumn and have the sheet recognize only the rows that have 1 or more in the quan. and move them to the next sheet, into rows and add rows as needed to finish the order form. so in the end I have an order form that has all of the ordered parts and info. but only that info and none of the marked "0" Cells. Hope this is clear enough.
Thanks for any help.

JAMES_911 29-08-2012 11:48 AM

Re: excel spread sheet data transfer help
Can you try the below code and see if that helps you out:


Private Sub CommandButton1_Click()

Dim C As Long
Dim Cell As Range
Dim NextRow As Long
Dim Rng As Range
Dim EntryWks As Worksheet
Dim DBWks As Worksheet
C = 1
Set EntryWks = Worksheets("Sheet1")
Set DBWks = Worksheets("Sheet2")
Set Rng = EntryWks.Range("B6:ad6")

NextRow = DBWks.UsedRange.Rows.Count
NextRow = IIf(NextRow = 1, 1, NextRow + 1)

For Each RA In Rng.Areas
For Each Cell In RA
C = C + 1
DBWks.Cells(NextRow, C) = Cell
Next Cell
Next RA
End Sub

elijah58 29-08-2012 04:24 PM

Re: excel spread sheet data transfer help
1 Attachment(s)
Thanks, it works partially, I am attaching a copy of what I am looking for to get a idea of what I need.
Please change the extension to xlsm.

All times are GMT +5.5. The time now is 12:25 PM.