PDA

View Full Version : Copy Pasting from one workbook to another



acruthi
12-17-2010, 12:30 PM
I want copy cell AD2 for all the worksheets in the workbook (about 98 worksheets) into a new workbook. I also want to copy the worksheet names so I know which cell belongs to which workbook.

Any help is appreciated.

Thanks much.

Bob Phillips
12-17-2010, 02:13 PM
Set this = Activeworkbook
Set wb = Workbooks.Add
For Each sh In this.Worksheets

wb.Worksheets(1).Cells(Nextrow, "A").Value2 = sh.Name
wb.Worksheets(1).Cells(Nextrow, "B").Value2 = sh.Range("AD2").Value2
Next sh

acruthi
12-20-2010, 06:44 PM
Thanks XLD - but this did not work for me. Is there another way to do this? It basically does not copy the tab name in addition to the cell AD2

Bob Phillips
12-21-2010, 01:11 AM
In what way did it not work?

GTO
12-21-2010, 06:48 AM
In what way did it not work?

For Each sh In this.Worksheets

Just a guess of course... :goofball:

Bob Phillips
12-21-2010, 09:26 AM
For Each sh In this.Worksheets

Just a guess of course... :goofball:

Aah, but did you notice this line

Set this = Activeworkbook

GTO
12-21-2010, 10:55 AM
LOL. Hmmm... I cannot believe I missed that! Well, excepting stopping in for paperwork, I'm off for a bit. Haven't gotten to fixing home connection, so if not back for the week, Merry Christmas and God's best to you and yours :-)

acruthi
12-21-2010, 11:40 AM
I had to change it a bit


Sub beneficial()

Dim rptWks As Worksheet
Dim wks As Worksheet
Dim ActWkbk As Workbook
Dim oRow As Long

Set ActWkbk = ActiveWorkbook

Set rptWks = Workbooks.Add(1).Worksheets(1)

oRow = 0
For Each wks In ActWkbk.Worksheets
oRow = oRow + 1
rptWks.Cells(oRow, "A").Value = wks.Name
rptWks.Cells(oRow, "B").Value = wks.Range("AF3").Value2
Next wks

End Sub

Bob Phillips
12-21-2010, 12:04 PM
You had to change it and it is not working, or you changed it and it is now working?