PDA

View Full Version : Copy to closed wbk



slamet Harto
03-11-2009, 05:18 AM
Hi Guys,

hope you are doing fine.

Please help me with the code. I want to copy some range into a closed workbook.
Sub PutDataInClosedWorkbook()
Dim wb As Workbook, CurDatRng As Range, NextDatRng As Range, Slastrow As Long
Dim sFileName As String
Application.ScreenUpdating = False ' turn off the screen updating
Sheets("MainData").Activate
With CurDatRng
Set CurDatRng = Range([B3], [B3].End(xlDown))
Set CurDatRng = CurDatRng.Offset(0, -1).Resize(CurDatRng.Rows.Count, 24)
End With
Set wb = Workbooks.Open("F:\MY\CTC\DBase\CDB.xls", True, False)
' read data to the target-from the source workbook...wb is the target
Sheets("WriteTemp").Activate
With ActiveSheet
Slastrow = Cells.SpecialCells(xlCellTypeLastCell).row
End With

wb.Worksheets("MainData").Range(CurDatRng).Formula = Thisworkbook.Worksheets("WriteTemp").Range([A2], "A" & Slastrow + 1).Formula
wb.Save
wb.Close
Set wb = Nothing
Application.ScreenUpdating = True
MsgBox "data transfer complete"
End Sub

code error in this line wb.Worksheets("MainData").Range(CurDatRng).Formula = Thisworkbook.Worksheets("WriteTemp").Range([A2], "A" & Slastrow + 1).Formula

Thanks for assistance.
Rgds, harto

Bob Phillips
03-11-2009, 05:23 AM
It is always best to open the workbook, copy the data, then close it.

stanleydgrom
03-11-2009, 05:31 AM
slamet Harto,

Try this for a start:



wb.Worksheets("MainData").Range(CurDatRng).Copy Thisworkbook.Worksheets("WriteTemp").Range([A2], "A" & Slastrow + 1)




Have a great day,
Stan

Kenneth Hobs
03-11-2009, 05:45 AM
Before that line do this:
debug.print "Slastrow", Slastrow
debug.print wb.Worksheets("MainData").Range(CurDatRng).Address
debug.print Thisworkbook.Worksheets("WriteTemp").Range("A2", "A" & Slastrow + 1).Address This will help you debug the problem. Play the macro and check the Immediate Window to see if the results are as you expected.