Copy/Select a Row

  • Thread starter Thread starter Cesar
  • Start date Start date
C

Cesar

Hello,
I'm retrieving data from SQL and creating a table from, lets say, columns
A:E, the first column contains a product serial number (unique) and the
following columns have the cost of diferent chemicals used on the product
fabrications, lets say, ChemA, ChemB, ChemC and ChemD. Using a Combo Box
looking at the column A for the ListFillRange, I select a product from the
list and put it on the cell F1(LinkedCell). What do I need to do to copy all
the cells (Chems) that belong to that particular chosen product?, in other
words, I want to copy all the Chemicals used for that product from cell F1 to
F5?

Thanks
 
I like using the Import External data wizard for creating my macros.

1) Start Recording a macro from worksheet menu Tools - Macro - Record New
Macro
2) Import Data from worksheet menu Data - Import External Data - Mew Web
Query or New Database Query. Select the options you want from the wazard
menues. The last menu where you select finish you can use the Query Editor
to see the SQL statements by select Edit Query
3) Stop Recording from worksheet menu Tools - Macro - Stop Recording.


You can now edit the recorded macro as required.
 
Thanks Joel for your answer, but that's not what I was looking for, I
probably didn't explain my problem right.
I have no problems retrieving the data from SQL, I even go back to the Query
and modify my request with some criteria, etc. My problem is once I pull all
the data that I want into Excel like I said, from columns A:E, (Column A
->product serial number, Column B,C,D,E show the cost of diferent chemicals
used on the product fabrication, ChemA, Chem B, ChemC and ChemD). I need to
select (I should say the User) a specific product from this list and paste
all the 5 cells into a different location (F1:J1), for instance, if the
product that I select is in the cell A10, I want to copy cells
A10,B10,C10,D10 E10 into F1:J1. I have to give the User the choice of
view/select which product he want to copy. I was using a Combo Box to do this
function, with a drop down list looking at the column A for the ListFillRange
parameter and F1 as LinkedCell parameter. What do I need to do to copy all
the cell asociated with that chosen Product (A10:E10) and paste in the cells
F1:F5?
Is there a different way to do this? I'm not sure but I think is something
related with the ColumnBound or Columncount parameters on the combo Box that
can give me what I want.
Thanks again,
 
Product = Combobox1.Text
Set c = Columns("A").Find(What:=Product, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Range("A" & c.Row & ":E" & c.Row).Copy
Range("F1").PasteSpecial _
Paste:=xlPasteValues, _
Transpose:=True
 
Thanks! that works perfect!!
--
Cesar


Joel said:
Product = Combobox1.Text
Set c = Columns("A").Find(What:=Product, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Range("A" & c.Row & ":E" & c.Row).Copy
Range("F1").PasteSpecial _
Paste:=xlPasteValues, _
Transpose:=True
Click to expand...
 
Back
Top