useing an existing module to extract info

Z

Zygoid

what I would like to do is this.

when a command_button is clicked on a spreadsheet, it will cop
information from a certain cell in that workbook and paste into th
cell of another spreadsheet of a different workbook.

example;

I have a workbook(wb1) with sheet1 and another workbook(WB2) wit
sheet2

I would like to copy cell d2 from wb1, sheet1 to wb2, sheet2 cell b
 
D

Dave Peterson

Is the button on sheet1 of that wb1 workbook?

Option Explicit

Private Sub CommandButton1_Click()
'I have a workbook(wb1) with sheet1 and another workbook(WB2) with Sheet2
'I would like to copy cell d2 from wb1, sheet1 to wb2, sheet2 cell b1

Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = Me 'current sheet

Set ws2 = Nothing
On Error Resume Next
Set ws2 = Workbooks("wb2.xls").Worksheets("sheet2")
On Error GoTo 0

If ws2 Is Nothing Then
MsgBox "Please open the second workbook"
Exit Sub
End If

ws1.Range("d2").Copy _
Destination:=ws2.Range("B1")

End Sub
 
Z

Zygoid

i think i can use this code, but am not sure on some parts of it.

Application.ScreenUpdating = False
Set SourceWb = Workbooks.Open("f:\wb2.xls")


ActiveWorkbook.Sheets("sh3").Activate
Range("b1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True


ActiveCell.Value =
ActiveCell.Offset(0, 1) = * this is where I'm not sure what
ActiveCell.Offset(0, 2) = should be. or even if it woul
work.
ActiveCell.Offset(0, 3) =
ActiveCell.Offset(0, 4) =
ActiveCell.Offset(0, 5) =
ActiveCell.Offset(0, 6) =


SourceWb.Close Tru
 
D

Dave Peterson

It looks like you're opening up wb2.xls.

Then you look at sh3 inside wb2.xls.

Then you find the first empty cell in column B and want to put something in it.

I like to start at the last row, and work my up to find that last used cell.
Then come down one row. This works in a lot of cases for what I do.

So here's one way of opening that other workbook and putting some data in sh3.

Option Explicit
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False

Dim RngToCopy As Range
Dim SourceWb As Workbook
Dim SourceWks As Worksheet
Dim DestCell As Range

Set SourceWb = Workbooks.Open("f:\wb2.xls")
Set SourceWks = SourceWb.Worksheets("sh3")

'start at the bottom of column B and find the last
'used cell. Then come down one.
With SourceWks
Set DestCell = .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0)
End With

Set RngToCopy = Me.Range("D2").Resize(1, 7) '1 row x 7 columns?

DestCell.Resize(RngToCopy.Rows.Count, RngToCopy.Columns.Count).Value _
= RngToCopy.Value

SourceWb.Close True

End Sub

=======
I am kind of confused about your variable names. SourceWb would usually mean
that it's the workbook sending the data--not receiving it.

So I'm not quite sure if I did stuff backwards or if it's the way you want.
 
Z

Zygoid

my statements haven't been very clear, sorry...

what i have is this.. on a spreadsheet i have a button to "save job"
it will save the job to a specific folder and name it according to wha
is in certain cells.

what I would like to do is add coding to the button so that when th
button is clicked, it will not only save_as the file. but also cop
text that is in certain cells and paste them to certain cells o
another workbook.

i.e; cell d2 copied from workbook1 and pasted to cell b3 on workbook
 
Top