Copy and Paste based on cell reference

[cjc155]

Hi everyone,

I have seen similar questions (http://www.mrexcel.com/forum/showthread.php?t=329973&highlight=copy+data+based+cell) and tried to convert code to my needs, but I haven't been able to get anything to work.

Here is my situation. I have 3 workbooks.

WB 1 - Input.xls - Is where I put my cell references to run some macros
WB 2 - Sales.xls - A list of sales by region
WB 3 - Output.xls - basically where I am formatting the results of the macros, but for this example, we can consider it completely blank.

I am trying to add a macro that will look at the Input workbook and take the value in cell C5 which is the region pictured below

Input.xls
	A	B	C	D
1	Attainment#'s	FilePath	C:\
2		GoalSheet	Sales
3		Region	Southeast
4		CurrentMonth	5
5		FileName	Sales.xls
6
7	Accounts	FilePath	C:\
8		FileName	Accounts.xls
9
10
Sheet1

and based on that value copy the shaded rows in the workbook Sales.xls in which the region matches.

Sales.xls
	A	B	C	D	E	F	G	H
1		InvDate	Inv#	HospitalName	Hosp#	Contract#	akkl	Region
2	1138	5/12/2008		A				MW
3	1138	5/12/2008		B				MW
4	1138	5/15/2008		C				MW
5	1138	5/21/2008		D				MW
6	1138	5/22/2008		E				MW
7	1138	5/29/2008		F				MW
8	1138	5/29/2008		G				MW
9	1138	5/29/2008		H				MW
10	1139	5/21/2008		A				E
11	1140	5/22/2008		B				E
12	1141	5/29/2008		C				E
13	1142	5/30/2008		A				E
14	1143	5/31/2008		B				E
Sheet1

and then paste that data into my output file.

It is late where I am so won't be able to check until tomorrow, but be assured I am very
thankful of any help offered. Thanks everyone -

 

[venkat1926]

quote
copy the shaded rows in the workbook Sales.xl
unquote

I am not clear about this . all the rows in sales.xls seem to be shaded. so I have written a macro to copy the whole sheet in sales.xls in to output. If you want only part of it you can modify the mcro instead of usedrange use the range address.
ALL THE WORKBOKS SHOLD BE OPEN OTHERIWSE YOU HAVE TO USE THE PATHS ALSO.
the macro is

Sub test()
Dim wb As String

With Workbooks("input.xls").Worksheets("sheet1")
wb = .Range("c5")
With Workbooks(wb).Worksheets("sheet1").UsedRange
.Copy
End With
With Workbooks("output.xls").Worksheets("sheet1")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
End With
Application.CutCopyMode = False
End Sub

 

[cjc155]

Thanks, I will try this. -Sorry about the shading

 

[cjc155]

Hi,

So I tried your code and it appers to copy the entire selection from the Sales. Sorry, probably didn't describe what I wanted good enough. Basically, in the input section the user has the ability to select a region he wants the report from, for example the midwest (or MW). I want the macro to then look at the sales.xls sheet for sales where the region column equals "MW" and copy only those rows where the regions match. Thanks for your help. Hope this is clearer than before

 

[cjc155]

Hi,

Sorry again but I looked over my pastes and realize they didn't match the text I had entered. The region I need to reference is in cell C3 in the input file. So when the value in the input C3 can be matched in the Region column from the sales spreadsheet. Sorry again for the confusion. Thanks for the help - C