I'm fairly new to this and am having difficulty trying to solve what to me
seems like an easy problem!!!! (spent hours on line searching)

I want to import data in specific cells from one sheet to another sheet in a
different workbook using VBA

I don't want to link the two files and I'm hoping to avoid using loads and
loads of code. (as this makes it difficult when changes have to be made.

I could use the macro recorder and copy contents of cell A1 and paste it
into cell C3 in the second book and so on. But there are more than 80 cells
i want to copy from the first worksheet to the second - there is a set
pattern e.g. cell a1 to cell c3, cell a2 to cell d5 (obviously too many to
list here)

I would like some form of solution that lists the from and to cell
references on a spare worksheet and uses these lists to pass values that VBA
can loop through (struggling to gert to grips with single loops let alone
using 2 together)

Any suggestions appreciated

Gazza, Is there any pattern among the cells, such as workbook1 cell1
workbook2 plus 2 cells to right? If there a common formula or patter
through the 80 cells, then yes a simply VBA loop can be created.

JoJ

--
JoJ
-----------------------------------------------------------------------
JoJo's Profile: http://www.excelforum.com/member.php...fo&userid=1130
View this thread: http://www.excelforum.com/showthread.php?threadid=46610

We have sent you direct precise code and we give it again

workbooks(1).sheets("a_a").range("a1").copy
workbooks(2).sheets("ca_a").range("c3")

Your destination ranges appear random. You cannot loop this.

"Gazza" <mallin"nospam" wrote:

I'm fairly new to this and am having difficulty trying to solve what to me
seems like an easy problem!!!! (spent hours on line searching)

I want to import data in specific cells from one sheet to another sheet in a
different workbook using VBA

I don't want to link the two files and I'm hoping to avoid using loads and
loads of code. (as this makes it difficult when changes have to be made.

I could use the macro recorder and copy contents of cell A1 and paste it
into cell C3 in the second book and so on. But there are more than 80 cells
i want to copy from the first worksheet to the second - there is a set
pattern e.g. cell a1 to cell c3, cell a2 to cell d5 (obviously too many to
list here)

I would like some form of solution that lists the from and to cell
references on a spare worksheet and uses these lists to pass values that VBA
can loop through (struggling to gert to grips with single loops let alone
using 2 together)

Any suggestions appreciated

Further, if you do not vary the cells every time (which you said you would),
and you list the 80 or 250 pairs of cells in a spare worksheet, loop by all
means.

for i = 1 to 80
fromcell=range(...)
tocell=range(...)
workbooks(1).sheets("a_a").range(fromcell).copy
workbooks(2).sheets("ca_a").range(tocell)
next i

"PY & Associates" wrote:

We have sent you direct precise code and we give it again

workbooks(1).sheets("a_a").range("a1").copy
workbooks(2).sheets("ca_a").range("c3")

Your destination ranges appear random. You cannot loop this.

"Gazza" <mallin"nospam" wrote:

I'm fairly new to this and am having difficulty trying to solve what to me
seems like an easy problem!!!! (spent hours on line searching)

I want to import data in specific cells from one sheet to another sheet in a
different workbook using VBA

I don't want to link the two files and I'm hoping to avoid using loads and
loads of code. (as this makes it difficult when changes have to be made.

I could use the macro recorder and copy contents of cell A1 and paste it
into cell C3 in the second book and so on. But there are more than 80 cells
i want to copy from the first worksheet to the second - there is a set
pattern e.g. cell a1 to cell c3, cell a2 to cell d5 (obviously too many to
list here)

I would like some form of solution that lists the from and to cell
references on a spare worksheet and uses these lists to pass values that VBA
can loop through (struggling to gert to grips with single loops let alone
using 2 together)

Any suggestions appreciated

To copy from Book1, sheet "B1Data" to Book2, sheet "B2Data". From range
values (eg A1) listed in column A starting row 2 in Book1 "Sheet1" and
to range values (eg C3) listed in column B.

Sub cpy()
Dim bk1 As Workbook
Dim bk2 As Workbook
Dim eRow As Long
Dim i As Long
Dim FCell As String
Dim TCell As String
Set bk1 = Workbooks("Book1.xls")
Set bk2 = Workbooks("Book2.xls")

With bk1.Sheets("Sheet1")
eRow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To eRow
FCell = .Cells(i, 1).Value
TCell = .Cells(i, 2).Value
bk1.Sheets("B1Data").Range(FCell).Copy _
bk2.Sheets("B2Data").Range(TCell)
Next i
End With

End Sub

Hope this helps
Rowan

Gazza wrote:
I'm fairly new to this and am having difficulty trying to solve what to me
seems like an easy problem!!!! (spent hours on line searching)

I want to import data in specific cells from one sheet to another sheet in a
different workbook using VBA

I don't want to link the two files and I'm hoping to avoid using loads and
loads of code. (as this makes it difficult when changes have to be made.

I could use the macro recorder and copy contents of cell A1 and paste it
into cell C3 in the second book and so on. But there are more than 80 cells
i want to copy from the first worksheet to the second - there is a set
pattern e.g. cell a1 to cell c3, cell a2 to cell d5 (obviously too many to
list here)

I would like some form of solution that lists the from and to cell
references on a spare worksheet and uses these lists to pass values that VBA
can loop through (struggling to gert to grips with single loops let alone
using 2 together)

Any suggestions appreciated

Hey guys, I have a similar problem.

I have nearly 700 soft copies that I need several specific cells from each
book.

I think the info I am importing from are inside pivot tables in the 700
books, dont know if this will make a difference or not.

lets say I need to take C3, E7, and F4 of ecah book and paste it all in a
new book in columns A,B, and C separately.

Im looking at previous resposes by PY&Associates, JoJo, and Rowan, but I
cant seem to get it working.

Im new to macros, so thanks for all the help.

peace out~

"Rowan" wrote:

To copy from Book1, sheet "B1Data" to Book2, sheet "B2Data". From range
values (eg A1) listed in column A starting row 2 in Book1 "Sheet1" and
to range values (eg C3) listed in column B.

Sub cpy()
Dim bk1 As Workbook
Dim bk2 As Workbook
Dim eRow As Long
Dim i As Long
Dim FCell As String
Dim TCell As String
Set bk1 = Workbooks("Book1.xls")
Set bk2 = Workbooks("Book2.xls")

With bk1.Sheets("Sheet1")
eRow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To eRow
FCell = .Cells(i, 1).Value
TCell = .Cells(i, 2).Value
bk1.Sheets("B1Data").Range(FCell).Copy _
bk2.Sheets("B2Data").Range(TCell)
Next i
End With

End Sub

Hope this helps
Rowan

Gazza wrote:
I'm fairly new to this and am having difficulty trying to solve what to me
seems like an easy problem!!!! (spent hours on line searching)

I want to import data in specific cells from one sheet to another sheet in a
different workbook using VBA

I don't want to link the two files and I'm hoping to avoid using loads and
loads of code. (as this makes it difficult when changes have to be made.

I could use the macro recorder and copy contents of cell A1 and paste it
into cell C3 in the second book and so on. But there are more than 80 cells
i want to copy from the first worksheet to the second - there is a set
pattern e.g. cell a1 to cell c3, cell a2 to cell d5 (obviously too many to
list here)

I would like some form of solution that lists the from and to cell
references on a spare worksheet and uses these lists to pass values that VBA
can loop through (struggling to gert to grips with single loops let alone
using 2 together)

Any suggestions appreciated

Method is the same.
Put all 700 filenames in range and loop through each of them.

"emile" wrote:

Hey guys, I have a similar problem.

I have nearly 700 soft copies that I need several specific cells from each
book.

I think the info I am importing from are inside pivot tables in the 700
books, dont know if this will make a difference or not.

lets say I need to take C3, E7, and F4 of ecah book and paste it all in a
new book in columns A,B, and C separately.

Im looking at previous resposes by PY&Associates, JoJo, and Rowan, but I
cant seem to get it working.

Im new to macros, so thanks for all the help.

peace out~

"Rowan" wrote:

To copy from Book1, sheet "B1Data" to Book2, sheet "B2Data". From range
values (eg A1) listed in column A starting row 2 in Book1 "Sheet1" and
to range values (eg C3) listed in column B.

Sub cpy()
Dim bk1 As Workbook
Dim bk2 As Workbook
Dim eRow As Long
Dim i As Long
Dim FCell As String
Dim TCell As String
Set bk1 = Workbooks("Book1.xls")
Set bk2 = Workbooks("Book2.xls")

With bk1.Sheets("Sheet1")
eRow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To eRow
FCell = .Cells(i, 1).Value
TCell = .Cells(i, 2).Value
bk1.Sheets("B1Data").Range(FCell).Copy _
bk2.Sheets("B2Data").Range(TCell)
Next i
End With

End Sub

Hope this helps
Rowan

Gazza wrote:
I'm fairly new to this and am having difficulty trying to solve what to me
seems like an easy problem!!!! (spent hours on line searching)

I want to import data in specific cells from one sheet to another sheet in a
different workbook using VBA

I don't want to link the two files and I'm hoping to avoid using loads and
loads of code. (as this makes it difficult when changes have to be made.

I could use the macro recorder and copy contents of cell A1 and paste it
into cell C3 in the second book and so on. But there are more than 80 cells
i want to copy from the first worksheet to the second - there is a set
pattern e.g. cell a1 to cell c3, cell a2 to cell d5 (obviously too many to
list here)

I would like some form of solution that lists the from and to cell
references on a spare worksheet and uses these lists to pass values that VBA
can loop through (struggling to gert to grips with single loops let alone
using 2 together)

Any suggestions appreciated