Hi all,

How can I setup a macro that will pull data in from a range.. From one
xls spreadsheet(testLog.xls)(summary tab) to another
(Script.xls)(findings tab)?


--
lbargers
------------------------------------------------------------------------
lbargers's Profile: http://www.excelforum.com/member.php...o&userid=32798
View this thread: http://www.excelforum.com/showthread...hreadid=527216

Hi there lbargers,

You could try something like this ...

Sub CopyMyDataFromBookToBook()

Dim wbCopy as Workbook, wbPaste as Workbook
Dim wsCopy as Worksheet, wsPaste as Worksheet
Dim rngCopy as Range, rngPaste as Range

Set wbCopy = Workbooks("testLog.xls")
Set wbPaste = Workbooks("Script.xls")
Set wsCopy = wbCopy.Sheets("summary")
Set wsPaste = wbPaste.Sheets("findings")
Set rngCopy = wsCopy.Range("A1:A10")
Set rngPaste = wsPaste.Range("A1:A10")

rngCopy.Copy Destination:=rngPaste
Application.CutCopyMode = False

End Sub

Change the ranges to suit.

HTH

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"lbargers" wrote in
message ...

Hi all,

How can I setup a macro that will pull data in from a range.. From one
xls spreadsheet(testLog.xls)(summary tab) to another
(Script.xls)(findings tab)?


--
lbargers
------------------------------------------------------------------------
lbargers's Profile:
http://www.excelforum.com/member.php...o&userid=32798
View this thread: http://www.excelforum.com/showthread...hreadid=527216

Hey Zach, Thanks for your fast response...

Will the method you showed copy all the data in the fields, or just
those that contain data? The file are located in different directories,
how do I set the path?

Thanks again, your help is greatly appreciated...


--
lbargers
------------------------------------------------------------------------
lbargers's Profile: http://www.excelforum.com/member.php...o&userid=32798
View this thread: http://www.excelforum.com/showthread...hreadid=527216

It will only copy the range you specify. Are you looking at a
variable/dynamic range? All of the data? Also, I assumed the workbooks
would be open. If they are not (or you don't know if they will be at
runtime) you can use something like this ...

Sub CopyMyDataFromBookToBook()

Dim wbCopy As Workbook, wbPaste As Workbook
Dim wsCopy As Worksheet, wsPaste As Worksheet
Dim rngCopy As Range, rngPaste As Range

If IsWbOpen("testLog.xls") Then
Set wbCopy = Workbooks("testLog.xls")
Else
Set wbCopy = Workbooks.Open("C:\YourPathHere\testLog.xls")
End If

If IsWbOpen("Script.xls") Then
Set wbPaste = Workbooks("Script.xls")
Else
Set wbPaste = Workbooks.Open("C:\YourPathHere\Script.xls")
End If

Set wsCopy = wbCopy.Sheets("summary")
Set wsPaste = wbPaste.Sheets("findings")
Set rngCopy = wsCopy.Range("A1:A10")
Set rngPaste = wsPaste.Range("A1:A10")

rngCopy.Copy Destination:=rngPaste
Application.CutCopyMode = False

End Sub

Function IsWbOpen(strName As String) As Boolean
On Error Resume Next
IsWbOpen = Len(Workbooks(strName).Name)
End Function

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"lbargers" wrote in
message ...

Hey Zach, Thanks for your fast response...

Will the method you showed copy all the data in the fields, or just
those that contain data? The file are located in different directories,
how do I set the path?

Thanks again, your help is greatly appreciated...


--
lbargers
------------------------------------------------------------------------
lbargers's Profile:
http://www.excelforum.com/member.php...o&userid=32798
View this thread: http://www.excelforum.com/showthread...hreadid=527216

I'll explain a little bit further as I am brand new to excel and was
assigned an excel automation project at work. I am an applications
developer and understand VB code. My question is how can I set up a
marco or other code routine to pull data into my spreadsheet from a
seperate spreadsheet at a different directory location.

The first spreadsheet (testScript.xls) contains a [Summary Tab].
The second spreadsheet(SummaryScript.xls) will contain a [Findings
Tab].

I want the second spreadsheet (SummaryScript.xls) to pull data from the
[Summary Tab] of the testScript.xls.

The data I want pulled will come from row 3, starting at Column E
through Column IV(E3:IV3) As the loop occurs I would like data in this
range to populate rows in The [Findings Tab] of the SummaryScript.xls
Spreadsheet.

What I want to do is copy the cell data in row 3 of any column (there
could be up to 255) where data is present, I would like that data sent
or pullled into column C of the [Findings Tab] in he SummaryScript.xls.
But it should be place row after row.

For instance if columns E3, L3, M3, AD3, AJ3... Contain a value I want
these values respectively placed into column C in the Findings Tab...

Thanks,
Larry Bargers


--
lbargers
------------------------------------------------------------------------
lbargers's Profile: http://www.excelforum.com/member.php...o&userid=32798
View this thread: http://www.excelforum.com/showthread...hreadid=527216

So if E3 has data, then no data from F3:J3, do not copy those, etc? And you
want this data pasted into column C, starting at row 3? Do you want the
blanks (i.e. F3:J3) pasted as well, or only those with data? Will this be
done multiple times? Do we need to find the last row in column C? I'm
assuming you'll be calling this from the SummaryScript workbook?

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM

"lbargers" wrote in
message ...

I'll explain a little bit further as I am brand new to excel and was
assigned an excel automation project at work. I am an applications
developer and understand VB code. My question is how can I set up a
marco or other code routine to pull data into my spreadsheet from a
seperate spreadsheet at a different directory location.

The first spreadsheet (testScript.xls) contains a [Summary Tab].
The second spreadsheet(SummaryScript.xls) will contain a [Findings
Tab].

I want the second spreadsheet (SummaryScript.xls) to pull data from the
[Summary Tab] of the testScript.xls.

The data I want pulled will come from row 3, starting at Column E
through Column IV(E3:IV3) As the loop occurs I would like data in this
range to populate rows in The [Findings Tab] of the SummaryScript.xls
Spreadsheet.

What I want to do is copy the cell data in row 3 of any column (there
could be up to 255) where data is present, I would like that data sent
or pullled into column C of the [Findings Tab] in he SummaryScript.xls.
But it should be place row after row.

For instance if columns E3, L3, M3, AD3, AJ3... Contain a value I want
these values respectively placed into column C in the Findings Tab...

Thanks,
Larry Bargers


--
lbargers
------------------------------------------------------------------------
lbargers's Profile:
http://www.excelforum.com/member.php...o&userid=32798
View this thread: http://www.excelforum.com/showthread...hreadid=527216

Yes that is right, I only want to copy the fields in row 3 where the
cells contain values. I then want those values pasted into column C of
SummaryScript(Findings tab) workbook. I would like to find values all
the way through the last row column C.

This process should update each time the first workbook(Summary) sheet
is adjusted...

Thanks

Larry Bargers


--
lbargers
------------------------------------------------------------------------
lbargers's Profile: http://www.excelforum.com/member.php...o&userid=32798
View this thread: http://www.excelforum.com/showthread...hreadid=527216

What of duplicate entries? We can make this happen anytime a cell in your
Summary sheet is changed, but what logic should be followed? If/Then/what?

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"lbargers" wrote in
message ...

Yes that is right, I only want to copy the fields in row 3 where the
cells contain values. I then want those values pasted into column C of
SummaryScript(Findings tab) workbook. I would like to find values all
the way through the last row column C.

This process should update each time the first workbook(Summary) sheet
is adjusted...

Thanks

Larry Bargers


--
lbargers
------------------------------------------------------------------------
lbargers's Profile:
http://www.excelforum.com/member.php...o&userid=32798
View this thread: http://www.excelforum.com/showthread...hreadid=527216

What of duplicate entries? We can make this happen anytime a cell in
your
Summary sheet is changed, but what logic should be followed?
If/Then/what?

--
Duplicate entries are ok, as far as logic, do you mean setting up a
loop to iterate through the range of values??


--
lbargers
------------------------------------------------------------------------
lbargers's Profile: http://www.excelforum.com/member.php...o&userid=32798
View this thread: http://www.excelforum.com/showthread...hreadid=527216

Well I see it as two issues. This can run everytime you change a cell in
row 3, it can take the value and paste it into the next available row in
column C of the other workbook. Or you can set this up to run on command
and it will find all values in row 3 and copy to the next set of available
rows in column C of the other workbook.

Are you looking for one? Both?

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"lbargers" wrote in
message ...

What of duplicate entries? We can make this happen anytime a cell in
your
Summary sheet is changed, but what logic should be followed?
If/Then/what?

--
Duplicate entries are ok, as far as logic, do you mean setting up a
loop to iterate through the range of values??


--
lbargers
------------------------------------------------------------------------
lbargers's Profile:
http://www.excelforum.com/member.php...o&userid=32798
View this thread: http://www.excelforum.com/showthread...hreadid=527216

Both ways would be very nice, if you have the time. You guys never cease
to amaze.. Thanks... Larry


--
lbargers
------------------------------------------------------------------------
lbargers's Profile: http://www.excelforum.com/member.php...o&userid=32798
View this thread: http://www.excelforum.com/showthread...hreadid=527216

Okay, here are a couple of solutions that give you a little latitude with
regards to what you want to do. It passes a few variables back and forth as
so you can have your cake and eat it too. ;)

In your workbook "testScript.xls", in the worksheet "Summary", right click
the tab and select View Code. Paste this in there ...


Private Sub Worksheet_Change(ByVal Target As Range)
'exit if more than one cell is changed
If Target.Cells.Count 1 Then Exit Sub
'exit if the cell changed is not in the specified region
If Intersect(Target, Me.Range("3:3")) Is Nothing Then Exit Sub
'perform the work
Call CopyTo_SummaryScript(False, Me, Target)
End Sub


Now while in the Visual Basic Editor, click Insert | Module. Paste the
following code into it ...


Sub RunManually()
Dim ws As Worksheet, Wks As Worksheet, Rng As Range
Set ws = ThisWorkbook.Sheets("Summary")
Set Rng = ws.Range("E3", ws.Cells(3,
ws.Columns.Count)).SpecialCells(xlCellTypeConstant s)
Call CopyTo_SummaryScript(True, Wks, Rng)
End Sub

Sub CopyTo_SummaryScript(Manual As Boolean, wsCopy As Worksheet, CopyCells
As Range)
Dim wbPaste As Workbook, wsPaste As Worksheet
Dim c As Range
Dim LastRow As Long
Dim WasOpen As Boolean
If IsWbOpen("SummaryScript.xls") Then
Set wbPaste = Workbooks("SummaryScript.xls")
WasOpen = True
Else
Set wbPaste = Workbooks.Open("C:\Documents and Settings\Zack
Barresse\Desktop\SummaryScript.xls")
WasOpen = False
End If
Set wsPaste = wbPaste.Sheets("Findings")
For Each c In CopyCells
LastRow = wsPaste.Cells(wsPaste.Rows.Count, "C").End(xlUp).Row + 1
wsPaste.Cells(LastRow, "C").Value = c.Value 'just value transfer,
not copying
Next c
wbPaste.Save
If Not WasOpen Then wbPaste.Close False
End Sub

Function IsWbOpen(strName As String) As Boolean
On Error Resume Next
IsWbOpen = Len(Workbooks(strName).Name)
End Function


Ensure you change the path to the correct location of your destination file.
Alt + F8 will return you to Excel. Ensure that you save ALL your work
before trying out any of these solutions; always be on the safe side. :)

Now, the routine "RunManually" should allow you to run the code whenever you
want. And with the code in the worksheet module (the change event), anytime
you change a cell on row 3 from column E or past, it will transfer that
value to the first available row in column C of your other workbook.

There is one possiblly large downfall to this. If the workbook to paste
into is not open, and you are using the change event, then anytime you
change a cell, it will open the workbook, perform the operation, save and
close. This can get time consuing and eat up valuable computer [memory]
resources. But they are both available to you if you'd like.

Let me know if you have any questions on any of these. I'll hold onto the
sample files for a bit.


--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"lbargers" wrote in
message ...

Both ways would be very nice, if you have the time. You guys never cease
to amaze.. Thanks... Larry


--
lbargers
------------------------------------------------------------------------
lbargers's Profile:
http://www.excelforum.com/member.php...o&userid=32798
View this thread: http://www.excelforum.com/showthread...hreadid=527216

Genius.... Wow you hit the nail square on the head. Thanks alot Zack!!

--
lbarger
-----------------------------------------------------------------------
lbargers's Profile: http://www.excelforum.com/member.php...fo&userid=3279
View this thread: http://www.excelforum.com/showthread.php?threadid=52721

Not a problem at all. Glad it works for you. :)

Take care.

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"lbargers" wrote in
message ...

Genius.... Wow you hit the nail square on the head. Thanks alot Zack!!!


--
lbargers
------------------------------------------------------------------------
lbargers's Profile:
http://www.excelforum.com/member.php...o&userid=32798
View this thread: http://www.excelforum.com/showthread...hreadid=527216