Macro to copy specific cells from one workbook to another
 

Hey community,

I really need help on this one, Iam a newbie to excel macros and got
stuck with this task. Basically we need a macro that can perfom the
following function;

1)Copy specific cells from workbook1 (e.g A3, D5, C4)
2)Open and existing workbook (workbook2) on the computer
3)Paste cells from workbook1 into the next avialble row in workbook2 in
a certain order. (i.e A3 first, D5 second, and C4last)

I hope the instructions were clear, and appreciate any help i could
get.

Thanks,
Dwight

Macro to copy specific cells from one workbook to another
 

Try this
http://www.rondebruin.nl/copy1.htm


--
Regards Ron De Bruin
http://www.rondebruin.nl



wrote in message oups.com...
Hey community,

I really need help on this one, Iam a newbie to excel macros and got
stuck with this task. Basically we need a macro that can perfom the
following function;

1)Copy specific cells from workbook1 (e.g A3, D5, C4)
2)Open and existing workbook (workbook2) on the computer
3)Paste cells from workbook1 into the next avialble row in workbook2 in
a certain order. (i.e A3 first, D5 second, and C4last)

I hope the instructions were clear, and appreciate any help i could
get.

Thanks,
Dwight

Macro to copy specific cells from one workbook to another
 

Hey Ron thanks for the help but I am stilling running into problems. A
the moment I am able open a new document from a saved location on the
compter, which is great. I am able to copy one of the cells from work
book1 to workbook2, which is a good start. The problem is, the cell
from workbook one is not being pasted into the right cell, that I want
in workbook2. Also i cant seem to get the macro to do mutiple
copy/paste with out running into errors. Below is the code I have so
far.

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
Function bIsBookOpen(ByRef szBookName As String) As Boolean
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

Function Lastcol(sh As Worksheet)
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function

Sub copy_to_another_workbook()
Dim sourceRange As Range
Dim destrange As Range
Dim destWB As Workbook
Dim Lr As Long

Application.ScreenUpdating = False
If bIsBookOpen("test.xls") Then
Set destWB = Workbooks("test.xls")
Else
Set destWB = Workbooks.Open("h:\test.xls")
End If
Lr = LastRow(destWB.Worksheets("Sheet1")) + 1
Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A1:A6")
Set destrange = destWB.Worksheets("Sheet1").Range("A" & Lr)
sourceRange.Copy
destrange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
destWB.Close True
Application.ScreenUpdating = True
End Sub

Macro to copy specific cells from one workbook to another
 

You can create a loop like this example do
http://www.rondebruin.nl/copy1.htm#range2

But it is much easier to insert a few formulas in a row below your data

In A50 =A3
In B50 =D5
In C50 =C4

You can hide this row and copy the range A50:C50


--
Regards Ron De Bruin
http://www.rondebruin.nl



wrote in message oups.com...

Hey Ron thanks for the help but I am stilling running into problems. A
the moment I am able open a new document from a saved location on the
compter, which is great. I am able to copy one of the cells from work
book1 to workbook2, which is a good start. The problem is, the cell
from workbook one is not being pasted into the right cell, that I want
in workbook2. Also i cant seem to get the macro to do mutiple
copy/paste with out running into errors. Below is the code I have so
far.

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
Function bIsBookOpen(ByRef szBookName As String) As Boolean
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

Function Lastcol(sh As Worksheet)
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function

Sub copy_to_another_workbook()
Dim sourceRange As Range
Dim destrange As Range
Dim destWB As Workbook
Dim Lr As Long

Application.ScreenUpdating = False
If bIsBookOpen("test.xls") Then
Set destWB = Workbooks("test.xls")
Else
Set destWB = Workbooks.Open("h:\test.xls")
End If
Lr = LastRow(destWB.Worksheets("Sheet1")) + 1
Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A1:A6")
Set destrange = destWB.Worksheets("Sheet1").Range("A" & Lr)
sourceRange.Copy
destrange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
destWB.Close True
Application.ScreenUpdating = True
End Sub

Macro to copy specific cells from one workbook to another
 

Ok I have changed the Range from A1:A6 to A50:C50. As for where to
place the

In A50 =A3
In B50 =D5
In C50 =C4

formulas in the code, that am a bit confused. If you couldd paste
exactly where is supposed to in the code that will be helpful. Also for
the "In"statement i think it requires some sort of declaration before
it like the "For" statement, but this im not too sure.

Dwight

Macro to copy specific cells from one workbook to another
 

I not understand you

If you create the links in row 50 to the cells (in the order you want ) you only have to
change the range to A50:C50 in this example
http://www.rondebruin.nl/copy1.htm#workbook

And you can hide row 50 so you not see it

--
Regards Ron De Bruin
http://www.rondebruin.nl



wrote in message ups.com...
Ok I have changed the Range from A1:A6 to A50:C50. As for where to
place the

In A50 =A3
In B50 =D5
In C50 =C4

formulas in the code, that am a bit confused. If you couldd paste
exactly where is supposed to in the code that will be helpful. Also for
the "In"statement i think it requires some sort of declaration before
it like the "For" statement, but this im not too sure.

Dwight