PDA

View Full Version : copy cells to one page from others



moose123
06-10-2012, 12:55 AM
Hi guys!
Could you tell me how to copy cells to one page from other pages

I need cells to be copied like this way

='page1'!A1
='page2'!A1
='page3'!A1

but it does

='page1'!A1
='page1'!A2
='page1'!A3

Will much appreciated

Aussiebear
06-10-2012, 03:38 AM
Try this link

http://office.microsoft.com/en-us/excel-help/edit-data-on-multiple-worksheets-HP001108262.aspx

moose123
06-10-2012, 04:40 AM
Thanks Aussiebear
but it's not what I need
it's copping data from one sheet cell to same cells on another sheets
And I need to copy formula like like this

cell A1 ='page1'!A1
cell A2 ='page2'!A1
cell A3 ='page3'!A1

CodeNinja
06-10-2012, 05:11 AM
Moose,
Looks like you just want a few cells to be pasted different sheets... You can use sheets(integer) as a way to move data around. You could always use pastespecial or whatever method you want to move to those sheets... the following might work for your purposes...

good luck.



Dim i As Integer
Dim str As String

For i = 1 To 3
str = Sheet1.Cells(i, 1)
Sheets(i).Cells(1, 1) = str
Next i


End Sub

moose123
06-10-2012, 09:33 AM
Thank you, but it's not works and not what I need ((


Will try to explain again
I have 5 sheets
1-st name "report"
2-nd name "Mike"
3-d name "Billy"
4-th name "John"
on sheets 2-4 there is a table
A1 -name
B1 - data

I need to copy from A1 and B1 of each of this tables to sheet ""report" which have such table

A2 B2 - from sheet2
A3 B3 - from sheet3
A4 B4 - from sheet4


In real it's much bigger that's why I need a macros or something which I can use to copy data.

Thank you

remy988
06-11-2012, 04:35 AM
moose,
perhaps something like this should get you started. no copying necessary.
Sheets("report").Range("a2:b2").Value = Sheets("Mike").Range("a2:b2").Value
Sheets("report").Range("a3:b3").Value = Sheets("Billy").Range("a3:b3").Value
Sheets("report").Range("a4:b4").Value = Sheets("John").Range("a4:b4").Value

IanFScott
06-11-2012, 06:21 AM
Try something like:


Dim i as Integer
Dim WB as Workbook
Set WB=Thisworkbook ' or Workbooks("xxxx")
For i = 2 to Sheets.Count
WB.Sheets(1).Cells(i,1) = WB.Sheets(i).Cells("A1")
WB.Sheets(1).Cells(i,2) = WB.Sheets(i).Cells("B1")
Next


It then doesn't matter how many sheets there are or what theri names are.

CodeNinja
06-11-2012, 06:28 AM
I am still not quite sure what you want... it would help if you gave a printscreen of what you want it to look like...

moose123
06-11-2012, 09:24 PM
Thank you IanFScott

Your code looks like good, but it's not working (

it's show an error

run-time error '5' invalid procedure call or argument. and highlights this string

WB.Sheets(1).Cells(i, 6) = WB.Sheets(i).Cells("A2")

moose123
06-11-2012, 10:57 PM
Cool! I found a solution!


WB.Sheets(1).Cells(i, 1) = WB.Sheets(i).Range("A2")


thread is resolved!

IanFScott
06-12-2012, 12:39 AM
Yes - sorry about that - I originally wrote .Cells(1, 2) and as I wrote the post realised I could just use "A2" but forgot to change it to .Range("A2")
As we say in the UK - "Stupid Boy"

moose123
06-12-2012, 04:50 AM
No, it's okey! :)
You helped me so much!!!