Macro to Copy / paste from 1 workbook to another

[David P.]

I'm using a macro to copy from one workbook to another in the same instance
of Excel. The macro successfully copies the data to the clipboard but does
not proceed to paste it in the new workbook. No error screen pops up it just
doesn't paste. Your help is appreciated. Here is my current macro code:

Sub CopyPasteToNCTthree()
'
' CopyPasteToNCTthree Macro
' Macro recorded 4/20/2009 by _
'
' Keyboard Shortcut: Ctrl+Shift+M
'
Sheets("Installer").Select
Range("O2:O8").Select
Selection.Copy
Workbooks.Open Filename:="C:\Zip Documents\Customers\New Customer
Test.xls"
Range("O2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("O2:O8").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 1
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("O8").Select
End Sub

 

[Ron de Bruin]

Hi David

Not have time to test your code now but see if this is working for you
http://www.rondebruin.nl/copy1.htm

See the example
What if the Database sheet is in another workbook

You can use most of the code in the example

 

[David P.]

Thanks Ron. It's getting stuck at bIsBookOpen_RB. I have changed all the file
& sheet names. When you say "database workbook" do you just mean new
workbook? Should I alter the Lr = LastRow (DestSh) and Set DestRange... part?

 

[David P.]

this is what I've done so far and initially gets stuck on bIsBookOpen:

Sub CopyToAnotherWorkbook()
Dim SourceRange As Range
Dim DestRange As Range
Dim DestWB As Workbook
Dim DestSh As Worksheet
Dim Lr As Long
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
If bIsBookOpen_RB("New Customer Test.xls") Then
Set DestWB = Workbooks("New Customer Test.xls")
Else
Set DestWB = Workbooks.Open("J:\Zip Documents\Customers\New Customer
Test.xls")
End If
Set SourceRange = ThisWorkbook.Sheets("Installer").Range("O2:O8")
Set DestSh = DestWB.Worksheets("Installer")
Lr = LastRow(DestSh)
Set DestRange = DestSh.Range("A" & Lr + 1)
With SourceRange
Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)
End With
DestRange.Value = SourceRange.Value
End Sub

 

[Ron de Bruin]

You find the two functions below the macro
This example will copy the data each time below the other data

You can remove
Lr = LastRow(DestSh)

And use this if you want a fixed range (you not need the lastrow function then)
Set DestRange = DestSh.Range("A10" )

Bedtime for me so goodnight


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

this is what I've done so far and initially gets stuck on bIsBookOpen:

Sub CopyToAnotherWorkbook()
Dim SourceRange As Range
Dim DestRange As Range
Dim DestWB As Workbook
Dim DestSh As Worksheet
Dim Lr As Long
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
If bIsBookOpen_RB("New Customer Test.xls") Then
Set DestWB = Workbooks("New Customer Test.xls")
Else
Set DestWB = Workbooks.Open("J:\Zip Documents\Customers\New Customer
Test.xls")
End If
Set SourceRange = ThisWorkbook.Sheets("Installer").Range("O2:O8")
Set DestSh = DestWB.Worksheets("Installer")
Lr = LastRow(DestSh)
Set DestRange = DestSh.Range("A" & Lr + 1)
With SourceRange
Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)
End With
DestRange.Value = SourceRange.Value
End Sub

 

[David P.]

I was using a shorcut key that's why it wasn't working. Thanks.

 

[David P.]

I was using a keyboard shortcut. That was the problem with my original code
not working. Many thanks.