PDA

View Full Version : Copy specific data



inajica
02-12-2008, 02:51 PM
I have six columns of data in Workbook "3" and Workbook "4". I would like to copy certain rows of data in each workbook and paste it in Workbook "Random" sheet3 and sheet4 respectively. The rows that I would want to be copied will be based on numbers, fifty of them, in Workbook "Random" in sheet1 A1:A50.

For example.

In Workbook "Random" A1 has number 513. I would like to copy data in Workbook "3" A513:f513 and paste into Workbook "Random" sheet3, and do the same with Workbook "4" and paste it into sheet4.

Thank you for any help with this.

RonMcK3
02-12-2008, 08:08 PM
Using your example, [Random]:A1 is 513; when I copy from workbooks 3/4:

what worksheet name am I taking the data from, sheet1?
is the worksheet name the same in the two source files?
where do I put a513:f513 on sheets 3/4? In A1:F1? Or some other location?

rbrhodes
02-12-2008, 10:28 PM
Hi inajica,

In addition to Ron's questions above:

Will all 3 workbooks all be open?



PS take a look at this.

RonMcK
02-13-2008, 12:14 PM
Hi rbrhodes,

Thanks for using the Range for copy and paste; I was trying to remember where I had seen it used before with little success. I combined the two loops into one For/Next and dropped the With/End With part by qualifying the .Range instances. I also found that I needed to qualify the Cells() used in setting myRow; before I did that, after the first pass, cells(i,1) was returning '4 a1' and giving me an error.

'Do all rows
For i = 1 To lastRow
myRow = wbRandom.Cells(i, 1)
wbSource3.Range(Cells(myRow, 1).Address, Cells(myRow, 6).Address).Copy Destination:=wsDest3.Cells(i, 1)
wbSource4.Range(Cells(myRow, 1).Address, Cells(myRow, 6).Address).Copy Destination:=wsDest4.Cells(i, 1)
Next i


Here is an updated copy of the Random zip file, as well.

mdmackillop
02-13-2008, 02:49 PM
Address and Destination are not needed (unless you like to code that way)

wbSource3.Range(Cells(myRow, 1), Cells(myRow, 6)).Copy wsDest3.Cells(i, 1)
wbSource4.Range(Cells(myRow, 1), Cells(myRow, 6)).Copy wsDest4.Cells(i, 1)

RonMcK
02-13-2008, 03:30 PM
Address and Destination are not needed (unless you like to code that way)

wbSource3.Range(Cells(myRow, 1), Cells(myRow, 6)).Copy wsDest3.Cells(i, 1)
wbSource4.Range(Cells(myRow, 1), Cells(myRow, 6)).Copy wsDest4.Cells(i, 1)


Malcolm,

Even better! Thanks, I appreciate the point-out. I keep learning bit by bit.

Thanks,

Ron
Windermere, FL (near Orlando, in the shadow of a wee rodent)