macro to copy and paste worksheet

[Pete]

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]

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]

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.

[VBA]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[/VBA]

[Pete]

Thanks KH.

Let me test and report back..

[david000]

[vba]
Sub CopySheet1()
Dim i As Long
With Sheet1.UsedRange
Sheet2.Range("A1").Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
End Sub
[/vba]