Copy cell (C6) from multiple sheets in a wbook to another workbook
 

Hello all,
Your help is appreciated in advance.
I have two workbooks
Book#1
Name = end of shift report data entry.xls with 28 sheets each
representing one day in February
Sheet 1 name = 02-01
Sheet 2 name = 02-02
Sheet 3 name = 02-03
..
..
Sheet 28 name = 02-28

Book#2
Name = end of shift report.xls
It has one sheet called "Minilinks & Flangeless"

I need to write a code that picks cell ("C6") from each sheet of the
first workbook "end of shift
report data entry.xls" and copy them at row #30 of the second workbook
"end of shift report.xls"
starting at cell ("B30")

Like this
cell ("C6") from sheet 02-01 TO Cell("B30")
cell ("C6") from sheet 02-02 TO Cell("C30")
cell ("C6") from sheet 02-03 TO Cell("D30")
cell ("C6") from sheet 02-04 TO Cell("E30")

and so on for all 28 sheets

Here what I have in mind but I do not know the syntax in VBA

For i = 1 to 28
Workbooks (end of shift report data entry).sheets ("02-0"&i).Range
("$C6").copy Workbooks
(end of shift report).sheets ("Minilinks & Flangeless").
Range("Something to point at cell
B30")

now somehow I need to offset ("Minilinks & Flangeless") from ("$B30")
TO ("$C30") using the Offset(1,0)

Next

Or may be using
For Each Sht In "end of shift report data entry.xls"
to loop through the sheets

I hope I explained the problem well

Thanks
Gami

Copy cell (C6) from multiple sheets in a wbook to another workbook
 

Gami,

Why not choose cell B30 BEFORE the For/Next loop
Copy the data into it using activecell=?????
and then use activecell.offset(0,1).select to select the next cell C, D, E,
--- AC


" wrote:

Hello all,
Your help is appreciated in advance.
I have two workbooks
Book#1
Name = end of shift report data entry.xls with 28 sheets each
representing one day in February
Sheet 1 name = 02-01
Sheet 2 name = 02-02
Sheet 3 name = 02-03
..
..
Sheet 28 name = 02-28

Book#2
Name = end of shift report.xls
It has one sheet called "Minilinks & Flangeless"

I need to write a code that picks cell ("C6") from each sheet of the
first workbook "end of shift
report data entry.xls" and copy them at row #30 of the second workbook
"end of shift report.xls"
starting at cell ("B30")

Like this
cell ("C6") from sheet 02-01 TO Cell("B30")
cell ("C6") from sheet 02-02 TO Cell("C30")
cell ("C6") from sheet 02-03 TO Cell("D30")
cell ("C6") from sheet 02-04 TO Cell("E30")

and so on for all 28 sheets

Here what I have in mind but I do not know the syntax in VBA

For i = 1 to 28
Workbooks (end of shift report data entry).sheets ("02-0"&i).Range
("$C6").copy Workbooks
(end of shift report).sheets ("Minilinks & Flangeless").
Range("Something to point at cell
B30")

now somehow I need to offset ("Minilinks & Flangeless") from ("$B30")
TO ("$C30") using the Offset(1,0)

Next

Or may be using
For Each Sht In "end of shift report data entry.xls"
to loop through the sheets

I hope I explained the problem well

Thanks
Gami

Copy cell (C6) from multiple sheets in a wbook to another workbook
 

Thanks Vic for your time
That is a good idea, but how can I write the code??
Thanks
gami

On Feb 9, 12:01 pm, Vic wrote:
Gami,

Why not choose cell B30 BEFORE the For/Next loop
Copy the data into it using activecell=?????
and then use activecell.offset(0,1).select to select the next cell C, D, E,
--- AC



" wrote:
Hello all,
Your help is appreciated in advance.
I have two workbooks
Book#1
Name = end of shift report data entry.xls with 28 sheets each
representing one day in February
Sheet 1 name = 02-01
Sheet 2 name = 02-02
Sheet 3 name = 02-03
..
..
Sheet 28 name = 02-28

Book#2
Name = end of shift report.xls
It has one sheet called "Minilinks & Flangeless"

I need to write a code that picks cell ("C6") from each sheet of the
first workbook "end of shift
report data entry.xls" and copy them at row #30 of the second workbook
"end of shift report.xls"
starting at cell ("B30")

Like this
cell ("C6") from sheet 02-01 TO Cell("B30")
cell ("C6") from sheet 02-02 TO Cell("C30")
cell ("C6") from sheet 02-03 TO Cell("D30")
cell ("C6") from sheet 02-04 TO Cell("E30")

and so on for all 28 sheets

Here what I have in mind but I do not know the syntax in VBA

For i = 1 to 28
Workbooks (end of shift report data entry).sheets ("02-0"&i).Range
("$C6").copy Workbooks
(end of shift report).sheets ("Minilinks & Flangeless").
Range("Something to point at cell
B30")

now somehow I need to offset ("Minilinks & Flangeless") from ("$B30")
TO ("$C30") using the Offset(1,0)

Next

Or may be using
For Each Sht In "end of shift report data entry.xls"
to loop through the sheets

I hope I explained the problem well

Thanks
Gami- Hide quoted text -

- Show quoted text -