Copy Range of Cells from closed sheet to open sheet in workbook

[geno32080]

I can get it to open the sheet, but then
It halts at the sourceworkbook.Sheets("Jax").Copy ("A3:S81")
I need it copy jax sheet cells A3:S81, close it and paste to Sheet2 cells B9:S81

Any suggestions?


Private Sub CommandButton1_Click()
Dim sourceworkbook As Workbook
Dim currentworkbook As Workbook

Set currentworkbook = ThisWorkbook

Set sourcework = Workbooks.Open("D:\folder\import\Jax.csv")

sourceworkbook.Sheets("Jax").Copy ("A3:S81")
sourceworkbook.Close

Set sourceworkbook = Nothing
Set currentworkbook = Nothing

ThisWorkbook.Activate
Worksheets("Sheet2").Activate
Worksheets("Sheet2").Range("B9:S81").Select

End Sub

 

[johnnyL]

Try pastespecial. For that you use two lines, one to copy and one to paste.

    sourceworkbook.Sheets("Jacksonville").Range("A3:S98").Copy
    ThisWorkbook.Worksheets("jax").Range("B9").PasteSpecial Paste:=xlPasteValues

 

[geno32080]

Try pastespecial. For that you use two lines, one to copy and one to paste.

    sourceworkbook.Sheets("Jacksonville").Range("A3:S98").Copy
    ThisWorkbook.Worksheets("jax").Range("B9").PasteSpecial Paste:=xlPasteValues

The code brings up a box stating "there is a large amount of information on the clipboard" and so on, is that a excel setting maybe? I got the same message from the code mumps sent.

 

[johnnyL]

When you paste data, if you want to clear the clipboard, put the following after the line that does the paste:

Application.CutCopyMode = False

 

[geno32080]

When you paste data, if you want to clear the clipboard, put the following after the line that does the paste:

Application.CutCopyMode = False

yep, that'll do it.. Your the man...Thank you.

Set sourceworkbook = Workbooks.Open("D:\JEN FOLDER\IMPORT\Jacksonville.csv")
sourceworkbook.Sheets("Jacksonville").Range("A3:S98").Copy
ThisWorkbook.Worksheets("jax").Range("B9").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
sourceworkbook.Close

 

[johnnyL]

Glad to help!