Solved: Paste from one workbook to another using VBA

[timjg]

I have limited expereince in VBA and am now trying to paste from one workbook to another using VBA. My source worksheet has columns a, b, c, d, e, and f where column a contains a location name and the other columns have numbers. For every row if any of the number columns contains a number greater than or equal to 1000 then the cells in column a and the respective number column needs to be pasted into a new row in the other workbook in columns c and e. For each row in the souce workbook there could be five seperate pastes if each of the number columns meets the criteria.


Any help with the code for this would be greatly appreciated.

[thekneeguy]

What you are asking sounds pretty simple on the face of it but I think you might need to explain more about sheet set up and especially the criteria I sort of lost you about the 5 pastes. Also, is this to be pasted into anothe workbook?? or another sheet within your workbook? Is the other workbook always the same one?

[timjg]

OK, let me try to clarify --

Workbook 1, sheet 1 (source doc) has columns a - f, where column a has a name and other columns have numbers.

Workbook 2, sheet 1 (destination doc) has columns a - z, two columns that will receive pasted values are c and e.

VBA code should check every row in workbook 1, sheet one and as an example in row 2, column c has 1000, code should copy the values in row 2, columns a and c and paste into Workbook 2, sheet 1 in row 2 columns c and e respectively.

For each row in Workbook 1, there exists the possiblity of a value of 1000 or greater in any or all of the columns b - f. So the code needs to check each of these columns for each row before continuing to the next row.


If that's still unclear, please let me know.

[thekneeguy]

so, do the same names appear in both workbooks? do the names appear twice in either or both workbooks? you are only interested in values greater than 1000 or the values will only be greater than 1000? do you have the worksheets/books set up? i guess seeing them would be ideal as I get the gist of what you are doing but would need some more specifics.

[timjg]

I've attached a sample doc to show you the result of what I'm attempting. The only difference is that in reality I have two seperate workbooks whereas I placed both sheets in the same workbook here. The sheet labeled source doc shows an example of what the source data might be. The destination doc shows what data is pulled and how it's placed in the doc. I hope this helps.

[malik641]

Hey timjg, and welcom to the forums

I think this will work, give it a try

[VBA]Option Explicit

Sub CopyToWB()
Dim cellNme As Variant
Dim cellAmt As Variant
Dim iLastRow As Integer
Dim iLastCol As Integer
Dim i As Integer
Dim j As Integer

With Workbooks("Book2").Sheets("Destination Doc")
.Range("C1").Value = "Name"
.Range("E1").Value = "Amount"
End With

j = 2
iLastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To iLastRow
cellNme = Cells(i, "A").Value
iLastCol = Cells(i, Columns.Count).End(xlToLeft).Column
For Each cellAmt In Range(Cells(i, 2), Cells(i, iLastCol))
If cellAmt.Value >= 1000 Then
With Workbooks("Book2").Sheets("Destination Doc")
.Range("C" & j).Value = cellNme
.Range("E" & j).Value = cellAmt
End With
j = j + 1
End If
Next cellAmt
Next i
End Sub[/VBA] Just run the macro while your in Workbook 1 Sheet "Source Doc" and make sure workbook 2 is open and has a sheet named "Destination Doc" and that should do it

[timjg]

I've added this code to the worksheet Source Doc in the copy paste.xls file and changed the line With Workbooks("Book2").Sheets("Destination Doc") to With Workbooks("Workbook2").Sheets("Destination Doc")


and get a run time error 9 - subscript out of range. I'm not sure how to correct this.

[malik641]

I've added this code to the worksheet Source Doc in the copy paste.xls file and changed the line With Workbooks("Book2").Sheets("Destination Doc") to With Workbooks("Workbook2").Sheets("Destination Doc")


and get a run time error 9 - subscript out of range. I'm not sure how to correct this.

Worked ok for me, are the workbooks in the same location (i.e. Desktop, or same folder)? Or is Workbook2 spelled correctly (is there a space like "Workbook 2")? And what version of excel are you running?

[timjg]

Both workbooks are on the desktop and I checked that names are all correct. I'm using Office 2004 for Mac which is equivalent to Office 2003 on the Windows platform. I've attached a zip with the two files. Maybe I've missed something but can't make it work. Thanks for your help.

[Airborne]

Hi timjg and malik641 ,

I don't have the total solution to your problem but I played a bit with the code.
I've tried the code and also got run time error 9. You get the error because the name of your sheet in Workbook2 is called Disitnation Doc. I renamed that to Destination Doc. And also In the code I've changed [VBA]Workbooks("Book2").Sheets("Destination Doc") [/VBA] to [VBA]Workbooks("Book2.xls").Sheets("Destination Doc") [/VBA]

The part of copy/paste works for me now.

Cheers,

Rob.

[malik641]

I tested it twice and it works for me just fine... I'm not sure what the problem is. I tried it with both files in a folder, then both files on the desktop...with no problems.

I'm using Windows XP, but I don't think that'll make a difference.

[timjg]

Hmmm, just tried it on my WinXP laptop with the same results. I checked that names were correct and tried with the .xls extension and without and got the same error both ways. What gives?

[timjg]

The issue turned out to be naming the spcific path for the destination file and then activating the source file. Here's the code:

[VBA]Option Explicit

Sub CopyToWB()
Dim cellNme As Variant
Dim cellAmt As Variant
Dim iLastRow As Integer
Dim iLastCol As Integer
Dim i As Integer
Dim j As Integer

ChDir "S:\folder\folder\folder\folder"
Workbooks.Open Filename:= _
"S:\folder\folder\folder\folder\Workbook2.xls"
Windows("copy paste.xls").Activate
With Workbooks("Workbook2.xls").Sheets("Destination Doc")
.Range("C1").Value = "Name"
.Range("E1").Value = "Amount"
End With

j = 2
iLastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To iLastRow
cellNme = Cells(i, "A").Value
iLastCol = Cells(i, Columns.Count).End(xlToLeft).Column
For Each cellAmt In Range(Cells(i, 2), Cells(i, iLastCol))
If cellAmt.Value >= 1000 Then
With Workbooks("Workbook2.xls").Sheets("Destination Doc")
.Range("C" & j).Value = cellNme
.Range("E" & j).Value = cellAmt
End With
j = j + 1
End If
Next cellAmt
Next i
End Sub [/VBA]

Thanks to all for the help.