Copy and Paste based on cell reference

cjc155

Board Regular
Joined
Apr 21, 2005
Messages
70
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
ABCD
1Attainment#'sFilePathC:\
2GoalSheetSales
3RegionSoutheast
4CurrentMonth5
5FileNameSales.xls
6
7AccountsFilePathC:\
8FileNameAccounts.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
ABCDEFGH
1InvDateInv#HospitalNameHosp#Contract#akklRegion
211385/12/2008AMW
311385/12/2008BMW
411385/15/2008CMW
511385/21/2008DMW
611385/22/2008EMW
711385/29/2008FMW
811385/29/2008GMW
911385/29/2008HMW
1011395/21/2008AE
1111405/22/2008BE
1211415/29/2008CE
1311425/30/2008AE
1411435/31/2008BE
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 -
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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
Code:
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

Board Regular
Joined
Apr 21, 2005
Messages
70
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

Board Regular
Joined
Apr 21, 2005
Messages
70
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,822
Messages
5,598,304
Members
414,224
Latest member
Crazy_FC

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
Top