Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Copy / paste from 1 workbook to another
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 -- David P. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Copy / paste from 1 workbook to another
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "David P." wrote in message ... 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 -- David P. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Copy / paste from 1 workbook to another
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. "Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "David P." wrote in message ... 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 -- David P. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Copy / paste from 1 workbook to another
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. "Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "David P." wrote in message ... 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 -- David P. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Copy / paste from 1 workbook to another
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." wrote in message ... 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. "Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "David P." wrote in message ... 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 -- David P. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Copy / paste from 1 workbook to another
I was using a shorcut key that's why it wasn't working. Thanks.
-- David P. "Ron de Bruin" wrote: 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." wrote in message ... 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. "Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "David P." wrote in message ... 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 -- David P. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Copy / paste from 1 workbook to another
I was using a keyboard shortcut. That was the problem with my original code
not working. Many thanks. -- David P. "Ron de Bruin" wrote: 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." wrote in message ... 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. "Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "David P." wrote in message ... 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 -- David P. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy workbook to 2nd workbook paste is greyed out | Excel Worksheet Functions | |||
macro to copy and paste into another workbook | Excel Discussion (Misc queries) | |||
Macro to open workbook and copy and paste values in to orig workbo | Excel Worksheet Functions | |||
copy and paste using code from workbook to workbook | Excel Discussion (Misc queries) | |||
I cannot paste from one workbook to another. Copy works, paste do. | Excel Discussion (Misc queries) |