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

geno32080

Board Regular
Joined
Jan 23, 2020
Messages
101
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Nov 7, 2011
Messages
3,825
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Try pastespecial. For that you use two lines, one to copy and one to paste.

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

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

geno32080

Board Regular
Joined
Jan 23, 2020
Messages
101
Office Version
  1. 2013
Platform
  1. Windows
Try pastespecial. For that you use two lines, one to copy and one to paste.

VBA Code:
    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

Well-known Member
Joined
Nov 7, 2011
Messages
3,825
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
When you paste data, if you want to clear the clipboard, put the following after the line that does the paste:

VBA Code:
Application.CutCopyMode = False
 
Solution

geno32080

Board Regular
Joined
Jan 23, 2020
Messages
101
Office Version
  1. 2013
Platform
  1. Windows
When you paste data, if you want to clear the clipboard, put the following after the line that does the paste:

VBA Code:
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
 

Forum statistics

Threads
1,176,507
Messages
5,903,450
Members
435,030
Latest member
zechariah

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top