If cell value = X then copy rows X1, X2, X6 to a separate worksheet

W

wearles

New Member
Joined
Feb 28, 2013
Messages
1
Hi,

First post on this so hope I have included all the right info, I have pretty basic Excel knowledge but what seems to be a complex query so I have attached my workbook and here is my query:

I am looking to copy the following data: Rows 1, 2, 3, 4, 5, 6, 9, 15 from one worksheet (Report1) to another (Report2) : when Row 2 = Brand A. The data needs to be copied in to the relevant cells in the Report2 worksheet (I have input an example in to Report2).

This can be a manual update where I click refresh each time I want the data to be populated so don't worry about needing to have it automated each time I open up the workbook.

Example:
Check B2 value = Brand A
True so copy data from cells B1, B2, B3, B4, B5, B6, B9, B15 to Report 2

Check C2 value = Brand A
False
Go to next Cell in row

Check C3 value = Brand A
False
Go to next Cell in row

etc..

I ideally would like to have this copy data to a separate workbook but have put them in the same workbook to make my query as straight forward as possible.

Many Thanks in advance for any help you can provide

Using Windows 7 and Excel 2007

Excel 2007
ABCDEFG
Value 1
Value 2
Value 1
Value 2
Item 7
Value 1
Value 2
Cost B
Cost C

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #E6B9B8"]Date[/TD]
[TD="align: center"]07 Jan 13[/TD]
[TD="align: center"]24 Jan 13[/TD]
[TD="align: center"]29 Jan 13[/TD]
[TD="align: center"]14 Feb 13[/TD]
[TD="align: center"]19 Feb 13[/TD]
[TD="align: center"]05 Mar 13[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #DBEEF3"]Brand[/TD]
[TD="bgcolor: #DBEEF3, align: center"]Brand A[/TD]
[TD="bgcolor: #DBEEF3, align: center"]Brand B[/TD]
[TD="bgcolor: #DBEEF3, align: center"]Brand B[/TD]
[TD="bgcolor: #DBEEF3, align: center"]Brand A[/TD]
[TD="bgcolor: #DBEEF3, align: center"]Brand C[/TD]
[TD="bgcolor: #DBEEF3, align: center"]Brand C[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #E6B9B8"]Brand 2[/TD]
[TD="align: center"]X1[/TD]
[TD="align: center"]X2[/TD]
[TD="align: center"]X3[/TD]
[TD="align: center"]X4[/TD]
[TD="align: center"]X5[/TD]
[TD="align: center"]X6[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #E6B9B8"]Brand 3[/TD]
[TD="align: center"]AA0001[/TD]
[TD="align: center"]AA0002[/TD]
[TD="align: center"]AA0003[/TD]
[TD="align: center"]AA0004[/TD]
[TD="align: center"]AA0005[/TD]
[TD="align: center"]AA0006[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #E6B9B8"]Size[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]70[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]42[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #E6B9B8"]Item 1[/TD]
[TD="align: center"]ABC1[/TD]
[TD="align: center"]ABC2[/TD]
[TD="align: center"]ABC3[/TD]
[TD="align: center"]ABC4[/TD]
[TD="align: center"]ABC5[/TD]
[TD="align: center"]ABC6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #E6B9B8"]Item 6[/TD]
[TD="align: center"]FGH1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]FGH2[/TD]
[TD="align: center"]FGH3[/TD]
[TD="align: center"]FGH4[/TD]
[TD="align: center"]FGH5[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]12[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]GHI1[/TD]
[TD="align: center"]GHI2[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]14[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #E6B9B8"]Cost A[/TD]
[TD="align: center"]£101.00[/TD]
[TD="align: center"]£102.00[/TD]
[TD="align: center"]£103.00[/TD]
[TD="align: center"]£104.00[/TD]
[TD="align: center"]£105.00[/TD]
[TD="align: center"][/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]£55.00[/TD]
[TD="align: center"]£55.00[/TD]
[TD="align: center"]£55.00[/TD]
[TD="align: center"]£55.00[/TD]
[TD="align: center"]£55.00[/TD]
[TD="align: center"][/TD]

[TD="align: center"]17[/TD]

[TD="align: center"]46%[/TD]
[TD="align: center"]46%[/TD]
[TD="align: center"]47%[/TD]
[TD="align: center"]47%[/TD]
[TD="align: center"]48%[/TD]
[TD="align: center"][/TD]

</tbody>
Report1​




Excel 2007
ABCDEFGH
AA0001JanuaryX1FGHI1 ABC1

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Brand 3[/TD]
[TD="align: center"]Month[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Brand 2[/TD]
[TD="align: center"]Item 6[/TD]
[TD="align: center"]Notes[/TD]
[TD="align: center"]Item 1[/TD]
[TD="align: center"]Cost A[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]07/01/2013[/TD]

[TD="align: right"][/TD]

[TD="align: right"] £ 101[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Report2​
 
You must log in or register to reply here.

Similar threads

A
  • Solved
Change calculation by insert new column for two sheets for separated ranges
Replies
2
Views
78
abdo meghari
A
A
  • Solved
compare brands between two sheets and show minus and positive values based on date (today)
Replies
6
Views
279
abdo meghari
A
A
  • Solved
copy data from listbox on form to sheet based on restrictions
2
Replies
14
Views
283
Ali M
A
C
  • Question Question
Create a new row for every date in row and create multiple copies of data to new worksheet based on cell number value for each date in the row VBA
Replies
0
Views
195
Chris258
C
A
  • Solved
Extraction same sheet name from files in folder and merge the same price
2 3
Replies
23
Views
507
mumps
mumps

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