Copy a row...

N

niloc85

New Member
Joined
Apr 29, 2015
Messages
20
Hi,

Im looking for some help as I dont use excel much!

I would like to have a formula/script to do the following...

I have a cell (C3) within sheet one which will contain a text value based upon a dropdown list. That text could be for example A, B or C. Based upon that text value of A, B or C within C3 I would like to copy a row from sheet two C20-30 to sheet one location C20-30.

Is this possible in a easy enough way?

Thanks if your able to help.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Hi niloc85,

Welcome to the board, and yes that is possible.

It is a bit vague exactly what you are shooting at, but start with something like this in sheet 1 sheet module.

Does not copy a "whole" row, just column D over to K. Changing the12 here can increase or decrease the number of
columns copied - cFind.Resize(1, 12).Copy

Alt + f11 to access the sheet vb Editor and copy the code there, Alt + f11 back to sheet 1.

Run from a button or Alt + f8 and click on the macro name then click Run.

Howard

Code: 
Option Explicit

Sub aCopyThing()
Dim cVal As String
Dim cFind As Range, shTwo As Range

Set shTwo = Sheets("Sheet2").Range("C20:C30")

cVal = Range("C3").Value

Set cFind = shTwo.Find(What:=cVal, _
   LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
   SearchDirection:=xlNext, MatchCase:=False)
   
 If Not cFind Is Nothing Then
    cFind.Resize(1, 12).Copy Sheets("Sheet1").Range("C20")
    'Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
   Else
    MsgBox "No match found."
    Exit Sub
  End If


End Sub
 
Upvote 0
Hi again,

Another way using a formula for the same range on sheet 2 as the code does.

On sheet 1 select the cells C2 over to J20 and while STILL SELECTED, type in the formula here:


=VLOOKUP(C3,Sheet2!C20:K30,{2,3,4,5,6,7,8,9},0)
Click to expand...


Then hold Ctrl & Shift keys down and hit Enter. This is Array Enter and Excel will encase the formula with { } 's ,do not add these your self.

If you make changes to the formula, you MUST select ALL the cells holding the formula, make you changes then Array Enter again.

As you change your drop down selections, the formula will up date.

Howard
 
Upvote 0
Thanks for your answer, I realise I haven't really given a good explanation!

So I have a spreadsheet that is used to calculate some bandwidth maths. What I want to do is have a sheet called "Capacity" that does these calcs. I also have antoher sheet called "Devices" which has all the info about the devices I want to do the calculations with. I want someone to be able to change the "Devices" sheet with info that is relevent with them, so to do the calculations I want to bring that info into sheet 1 "Capacity" based on a drop down list selection so I can do the calcs.

Within "Capacity" I will have a number of fields from B11-B30 that have a drop down, this dropdown will contain a list of devices from the "Devices" worksheet from C4:C30. If the user for example selects "iPad 1" from the list in "Capacity" B11 I want to copy from sheet 2 "Devices" cells M11:W11, however if B11 iPod I would want it to copy the data from M12:W12 and so on..... all the way up to M30:W30.

Does this make sense?
 
Upvote 0
niloc85 said:
Thanks for your answer, I realise I haven't really given a good explanation!

So I have a spreadsheet that is used to calculate some bandwidth maths. What I want to do is have a sheet called "Capacity" that does these calcs. I also have antoher sheet called "Devices" which has all the info about the devices I want to do the calculations with. I want someone to be able to change the "Devices" sheet with info that is relevent with them, so to do the calculations I want to bring that info into sheet 1 "Capacity" based on a drop down list selection so I can do the calcs.

Within "Capacity" I will have a number of fields from B11-B30 that have a drop down, this dropdown will contain a list of devices from the "Devices" worksheet from C4:C30. If the user for example selects "iPad 1" from the list in "Capacity" B11 I want to copy from sheet 2 "Devices" cells M11:W11, however if B11 iPod I would want it to copy the data from M12:W12 and so on..... all the way up to M30:W30.

Does this make sense?
Click to expand...

Yes, that narrows it down some. What you want to do is a fairly basic copy/paste like operation. Referring correctly to the cells involved is somewhat confusing.

..."Capacity" I will have a number of fields from B11-B30 that have a drop down... Is a "field" a cell or a bunch of cells? Probably a cell, and "...have A drop down" is probably several drop downs.

Also, where is the range on "Devices" sheet that hold the values "iPad" and "iPod" etc. Probably in column L?

When the row M to W is copied, where does it go on sheet "Capacity".
Do the copied rows overwrite the last copy, or do you want to "List" them, one row below the last copy. I am thinking in column C over to column M, as noted in your per first post.

Most likely very easily solved if you could post a link to an example work book with a sample of your data. Does not need to the whole data base, 5 -10 rows or enough to give a fair representation of the sheets (both of them) data layout. The data itself can be phony, if the real data is sensitive.

You may know you cannot attach a workbook to the forum, so posting a link is acceptable. I use Drop Box, but there are others.

You project is not really a complicated one, It is sometimes a bit difficult 'splaining what you want done. (And, yes, just say it like that... I want this to do that and I want those to go here, and I don't want... etc.)

Howard
 
Upvote 0
L. Howard said:
Yes, that narrows it down some. What you want to do is a fairly basic copy/paste like operation. Referring correctly to the cells involved is somewhat confusing.

..."Capacity" I will have a number of fields from B11-B30 that have a drop down... Is a "field" a cell or a bunch of cells? Probably a cell, and "...have A drop down" is probably several drop downs.

Also, where is the range on "Devices" sheet that hold the values "iPad" and "iPod" etc. Probably in column L?

When the row M to W is copied, where does it go on sheet "Capacity".
Do the copied rows overwrite the last copy, or do you want to "List" them, one row below the last copy. I am thinking in column C over to column M, as noted in your per first post.

Most likely very easily solved if you could post a link to an example work book with a sample of your data. Does not need to the whole data base, 5 -10 rows or enough to give a fair representation of the sheets (both of them) data layout. The data itself can be phony, if the real data is sensitive.

You may know you cannot attach a workbook to the forum, so posting a link is acceptable. I use Drop Box, but there are others.

You project is not really a complicated one, It is sometimes a bit difficult 'splaining what you want done. (And, yes, just say it like that... I want this to do that and I want those to go here, and I don't want... etc.)

Howard
Click to expand...

https://www.dropbox.com/s/zd19hp4hqxgpih5/Leys Calculator.xlsx?dl=0
 
Upvote 0
Hi niloc85,

I have the sheet.

First step was to remove the merged cells. Merged cell within the table, columns, rows of the data you want to manipulate is not a good thing.
I have neither the patience nor the skill level to effectively deal with them in coding and formulas.
If they are a "must have" for this worksheet, then I will have to excuse myself from this project.

Otherwise, I need you to tell me what exactly should happen with this sheet as you posted it.

Step at a time, in plain talk.

Looks like we need to add and name two sheets, "Capacity" and "Devices". Is that correct?

Howard
 
Upvote 0
L. Howard said:
Hi niloc85,

I have the sheet.

First step was to remove the merged cells. Merged cell within the table, columns, rows of the data you want to manipulate is not a good thing.
I have neither the patience nor the skill level to effectively deal with them in coding and formulas.
If they are a "must have" for this worksheet, then I will have to excuse myself from this project.

Otherwise, I need you to tell me what exactly should happen with this sheet as you posted it.

Step at a time, in plain talk.

Looks like we need to add and name two sheets, "Capacity" and "Devices". Is that correct?

Howard
Click to expand...

I see what you mean now, I have uploaded the sheet without the saved changes I had made... so what you are looking at is completely incorrect. I will need to build this again and then update you once completed. Then what I have said previously should make sense!

Sorry and speak soon.
 
Upvote 0
I have the workbook.

I see the drop down in B11, sheet Capacity and have selected iPad.

On sheet Devices I see iPad in cell C4. to the right in cells D4 over to L4 is data relevant to iPad.

Is it those cells D column to L column you want copied to sheet Capacity cell C5 or the data from Devices W4 to M4 copied to C5 on sheet Capacity?

I ask because, first there is no data in the Devices W to M range and second the headers on the Devices D to L range do not match the headers on Capacity where I think you want to copy to.

Perhaps your updated sheets don't match the previous posted instructions??

If the user for example selects "iPad 1" from the list in "Capacity" B11 I want to copy from sheet 2 "Devices" cells M11:W11, however if B11 iPod I would want it to copy the data from M12:W12 and so on..... all the way up to M30:W30.
Click to expand...

So, the question is, what range (row of data from column ? to column ??) gets copied from Devices to Capacity?

Howard
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Loop with column header match and two conditions paste
Replies
3
Views
426
Legacy 143009
L
A
  • Question
VBA Userform List Box to Populate upon entering a specific text in Column B, copy data upon selection and paste data in the same Row in another Column
Replies
3
Views
185
arijitirf
A
I
  • Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
232
Insurance Dave
I
H
  • Question
VBA to Create a speaker object
Replies
0
Views
108
hsandeep
H
B
  • Question
Copy a cell value where that cell is found relative to another cell.
Replies
1
Views
123
mumps
mumps

Forum statistics

Threads
1,215,025
Messages
6,122,732
Members
449,093
Latest member
Mnur

Share this page

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 MrExcel.com.
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 "mrexcel.com".
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
Back
Top