Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying and Pasting Macro from one worksheet to another
Please help, we have created the following code that should copy a entire row
from the active worksheet, open an existing workbook and paste the copied data into the next available row. However it is currently pasting it into the next available row of the original workbook. Below you will find my current code: Am I doing somthing stupid!!! Much appreciated. Sub CopyLast() Sheets("Summary").Range("A2").EntireRow.Copy Application.ScreenUpdating = False Workbooks.Open "C:\Documents and Settings\david.cope\Desktop\CHR\Calcs.xls", UpdateLinks:=1 Windows("Calcs.xls").Activate With Worksheets("Sheet1") Dim NextRow As Range Set NextRow = Sheet1.Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0) NextRow.PasteSpecial (xlValues) Application.CutCopyMode = False Application.ScreenUpdating = True End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying and Pasting Macro from one worksheet to another
Try this
Option Explicit Sub CopyLast() Dim aWB As Excel.Workbook Dim aWS As Excel.Worksheet Dim myWB As Excel.Workbook Dim myWS As Excel.Worksheet Set aWB = ActiveWorkbook Set aWS = aWB.Sheets("Summary") aWS.Range("A2").EntireRow.Copy Application.ScreenUpdating = False Set myWB = Workbooks.Open _ ("C:\Documents and Settings\david.cope\Desktop\CHR\Calcs.xls", _ UpdateLinks:=1) 'Windows("Calcs.xls").Activate Set myWS = myWB.Worksheets("Sheet1") 'With Worksheets("Sheet1") Dim NextRow As Range Set NextRow = myWS.Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0) NextRow.PasteSpecial (xlValues) Application.CutCopyMode = False Application.ScreenUpdating = True 'End With End Sub HTH, Barb Reinhardt "Neil Holden" wrote: Please help, we have created the following code that should copy a entire row from the active worksheet, open an existing workbook and paste the copied data into the next available row. However it is currently pasting it into the next available row of the original workbook. Below you will find my current code: Am I doing somthing stupid!!! Much appreciated. Sub CopyLast() Sheets("Summary").Range("A2").EntireRow.Copy Application.ScreenUpdating = False Workbooks.Open "C:\Documents and Settings\david.cope\Desktop\CHR\Calcs.xls", UpdateLinks:=1 Windows("Calcs.xls").Activate With Worksheets("Sheet1") Dim NextRow As Range Set NextRow = Sheet1.Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0) NextRow.PasteSpecial (xlValues) Application.CutCopyMode = False Application.ScreenUpdating = True End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying and Pasting Macro from one worksheet to another
FWIW, when you used this line
Set NextRow = Sheet1.Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0) It used Sheet1 of the workbook with the code. "Neil Holden" wrote: Please help, we have created the following code that should copy a entire row from the active worksheet, open an existing workbook and paste the copied data into the next available row. However it is currently pasting it into the next available row of the original workbook. Below you will find my current code: Am I doing somthing stupid!!! Much appreciated. Sub CopyLast() Sheets("Summary").Range("A2").EntireRow.Copy Application.ScreenUpdating = False Workbooks.Open "C:\Documents and Settings\david.cope\Desktop\CHR\Calcs.xls", UpdateLinks:=1 Windows("Calcs.xls").Activate With Worksheets("Sheet1") Dim NextRow As Range Set NextRow = Sheet1.Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0) NextRow.PasteSpecial (xlValues) Application.CutCopyMode = False Application.ScreenUpdating = True End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
holding worksheet formats when copying/pasting | Excel Discussion (Misc queries) | |||
Copying & Pasting into a newly created worksheet | Excel Programming | |||
Copying and Pasting Worksheet between workbooks | Excel Programming | |||
Copying and Pasting a Worksheet | Setting up and Configuration of Excel | |||
Copying and Pasting in a different worksheet (Urgent) | Excel Programming |