[RESOLVED] Copy from one workbook to a open workbook

[virgiliocabrera]

Hi, I know way to little of VBA, i need some help in copying values from one workbook to another workbook.... these is the problem.... I have book1.xls with some data, and book2.xlsm where i do the calculation.

I want to search for a specific string values in a specific column (lets say "D") from rows 3 to the last filled row. if the value is true..... then copy the row in a sheet name "configuration" in the next blank row in book2.xlm.

I'm sending a sample where i'm testing the formulas to see how far i can get. but i feel stuck at the moment.

please help

[virgiliocabrera]

Hello Again..... Well I'm still trying and this is my new code..... Please Help.. Still Getting the

"Subscript out of range" Error

Code:

Sub CopyCopy()
Dim rng1 As Range, rng2 As Range
Dim i As Long
Dim LastRowColB As Long, LastRowP As Long, InsertRow As Long

LastRowColB = Workbooks("configuraciones_codigo_2.xls").Sheets("Codigo").Range("B65536").End(xlUp).Row
LastRowP = Workbooks("calculos Pedidos_macro.xlsm").Sheets("configuraciones").Range("b65536").End(x1Up).Row
InsertRow = LastRowP

For i = 3 To LastRowColB

If Workbooks("configuraciones_codigo_2.xls").Sheets("Codigo").Cells(i, 3).Value = "La Frontera Pallets" Then

Set rng1 = Workbooks("configuraciones_codigo_2.xls").Sheets("codigo").Range(i, 1).EntireRow
Set rng2 = Workbooks("calculos Pedidos_macro.xlsm").Sheets("Configuraciones").Range(InsertRow, 1).EntireRow
rng1.Copy rng2

InsertRow = InsertRow + 1
Else
Workbooks("vbf.xlsm").Sheets("MyDate").Range("A2") = 0

End If
Next i

End Sub

[seenu_1st]

this code just copy data from another closed workbook, u wil get some idea

Dim Wb1 As Workbook
Application.ScreenUpdating = False
Set Wb1 = Workbooks.Open("C:\sample.xls")

Wb1.Sheets(1).Range("A1:A10").Copy
Wb1.Close SaveChanges:=False

ThisWorkbook.Sheets(1).Range("a1").PasteSpecial Paste:=xlPasteAll
Application.ScreenUpdating = True

[virgiliocabrera]

Tanks for your reply Seenu, but that will not solve my problem, i had to find a way to solve it.

this code is working for my, just wanted to posed a final code


Sub Conf()

Dim i As Integer
Dim Source As Workbook
Dim LastRow As Long
Dim OriginalWorkBook As Workbook
Set OriginalWorkBook = ThisWorkbook
Const MyDir As String = "C:\Users\Shadow\Desktop\Frontera\Pedido de Madera\"
Set Source = Workbooks.Open(MyDir & "configuraciones_codigo_2.xlsx")
Application.ScreenUpdating = False
LastRow = Source.Worksheets("codigo").Range("b" & Rows.Count).End(xlUp).Row

For i = 3 To LastRow


If Source.Worksheets("codigo").Cells(i, 3) = "La Frontera Pallets" Then

Source.Worksheets("codigo").Rows(i).Copy


OriginalWorkBook.Activate
OriginalWorkBook.Worksheets("Configuraciones").Range("C65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Rows(ActiveCell.Row).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


Else

End If

Next


Application.ScreenUpdating = True

End Sub

[seenu_1st]

ok fine, nothing is wrong, just u can change this

OriginalWorkBook.Worksheets("Configuraciones").Range("C65536").End(xlUp).Select

to

OriginalWorkBook.Worksheets("Configuraciones").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).EntireRow.Select

so u can avoid the next 2 lines, other than fine.
even u can use the paste also in the same line

[virgiliocabrera]

Thanks Seenu...... It Worked Fine and it looks cleaner !!!

virgiliocabrera