Macro to Copy / paste from 1 workbook to another


D

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
 
Ad

Advertisements

D

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?
 
D

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
 
R

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




David P. said:
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
 
Ad

Advertisements

D

David P.

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top