PDA

View Full Version : macro to copy and paste worksheet



Pete
12-03-2008, 03:02 AM
Hi Experts

Need to amend the current VBA code so that it can be changed - in a way that the user can paste worksheet "Sheet1" into any other worksheet(s) in the workbook other then worksheet "Task_Table1"

Also need to the macro to function even if worksheet "Sheet1" is hidden.

Sub Copy_Paste()
Application.ScreenUpdating = False
Sheets("Sheet1").Select
ActiveWindow.SmallScroll Down:=-9
Cells.Select
Selection.Copy
Sheets("Latam Santander").Select
ActiveWindow.SmallScroll Down:=-18
Cells.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("D13").Select

Application.ScreenUpdating = True
End Sub

MaximS
12-03-2008, 01:20 PM
Can you please explain what exactly you want to copy (exact range) and where to paste?

How do you want to chose destination workbook/worksheet?

Kenneth Hobs
12-03-2008, 01:50 PM
Insert this into a Module and change the Sub Copy1 as needed.

I submitted my speedup routine as a kb article months back but no response yet. They just make it easy to set/unset these things with just two calls.

Public glb_origCalculationMode As Integer

Sub SpeedOn(Optional StatusBarMsg As String = "Running macro...")
glb_origCalculationMode = Application.Calculation
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
.Cursor = xlWait
.StatusBar = StatusBarMsg
.EnableCancelKey = xlErrorHandler
End With
End Sub

Sub SpeedOff()
With Application
.Calculation = glb_origCalculationMode
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
.CalculateBeforeSave = True
.Cursor = xlDefault
.StatusBar = False
.EnableCancelKey = xlInterrupt
End With
End Sub

Sub Copy1()
CopyFromTo "Sheet1", "Sheet2"
End Sub

Sub CopyFromTo(fromSheet As String, toSheet As String)
SpeedOn
Worksheets(fromSheet).Cells.Copy
Worksheets(toSheet).Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
'Range("D13").Select
SpeedOff
End Sub

Pete
12-04-2008, 12:42 AM
Thanks KH.

Let me test and report back..

david000
12-05-2008, 08:49 PM
Sub CopySheet1()
Dim i As Long
With Sheet1.UsedRange
Sheet2.Range("A1").Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
End Sub