2

I am trying to copy a whole sheet from one Excel file to a sheet in another. Following is the code I wrote which doesn't work. Please suggest changes.

Sub copyallwos()

Dim wkbSource As Workbook
Dim wkbDest As Workbook
Dim shttocopy As Worksheet
Dim wbname As String

Set wkbSource = Workbooks.Open("C:\Users\AV\Documents\New folder\SCADA Wos.xlsm")

Set wkbDest = Workbooks("C:\Users\AV\Documents\New folder\MASTER.xlsm")

'perform copy
Set shttocopy = wkbSource.Sheets("tt")
shttocopy.Copy
wkbDest.Sheets("SCADAWOs").Select
ActiveSheet.Paste

End Sub
shA.t
  • 15,880
  • 5
  • 49
  • 104
Arun Noel Victor
  • 37
  • 2
  • 5

1 Answers1

5

Try this under 'perform copy

wkbSource.Sheets("tt").Copy After:=wkbDest.Sheets("SCADAWOs")

You can also insert the sheet before your "SCAD..." sheet, just change After:= to Before:=. Also, if you don't necessarily know a sheet name in the destination workbook, you can use After:=Wkbdest.sheets(sheets.count) which will instert it after the last Worksheet.

BruceWayne
  • 22,449
  • 14
  • 60
  • 100
  • 1
    Thankyou. I will try it. I am also getting a 'script out of range' error on this line: Set wkbDest = Workbooks("C:\Users\AV\Documents\New folder\MASTER.xlsm") – Arun Noel Victor Jul 27 '15 at 14:29
  • @ArunNoelVictor - silly question, but that is exactly where the file is, and named correct? and it's an .xlsm extension? – BruceWayne Jul 27 '15 at 14:38
  • Hm - I tested similar code on my comp. and it opened without an issue. Try just moving it to C:\, and see if it can open from there? Or some other folder to test. – BruceWayne Jul 27 '15 at 15:17
  • I had to open the wkbDest for this to work, otherwise I was also getting "out of range" error – Andrejs Gasilovs Jan 25 '22 at 13:03