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
 

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.

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
3,812
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Your variable names are all over the place.
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
3,812
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Maybe something like:

VBA Code:
Private Sub CommandButton1_Click()
'
    Dim sourceworkbook  As Workbook
'
    Set sourceworkbook = Workbooks.Open("D:\folder\import\Jax.csv")
'
    sourceworkbook.Sheets("Jax").Range("A3:S81").Copy ThisWorkbook.Worksheets("Sheet2").Range("A3:S81")
'
    sourceworkbook.Close
    Set sourceworkbook = Nothing
End Sub

Hard to say from the original code you posted what your intentions are. :(
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
3,812
Office Version
  1. 2016
  2. 2013
  3. 2007
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?

The ranges must be the same size.

A3:S81 is not the same size as B9:S81
 

geno32080

Board Regular
Joined
Jan 23, 2020
Messages
101
Office Version
  1. 2013
Platform
  1. Windows
:S89The ranges must be the same size.

A3:S81

The ranges must be the same size.

A3:S81 is not the same size as B9:S81
This is going to be more complicated than I originally thought. The source is a file downloaded from a web portal. In the past I run the report, Highlighted the page and pasted to the sheet, What's happened now is it went from one sheet to 8. Alot of copying an pasting, and only one screen to operate this procedure.
So when the report is run, I save it to a folder, I name the sheets by the city the report was generated from. It has headers that take up A1 : AA2, I only need the info that is on A3:S81( the report could be any number of rows, I set the variable to 81, because it never goes any higher than that) the destination sheet starts at B9: and goes to S81. So with that in mind I tried changing the source to B9:T9 and the destination to the same B9:T9 still get the the same run time error, subscript out of range.

sourceworkbook.Sheets("Jacksonville").Range("B9:T9").Copy ThisWorkbook.Worksheets("Sheet2").Range("B9:T9")
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
3,812
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Subscript out of range means that it is not seeing something.

Many times it comes down to the sheet names. Make sure both of those sheet names exist and are typed in correctly.
 

geno32080

Board Regular
Joined
Jan 23, 2020
Messages
101
Office Version
  1. 2013
Platform
  1. Windows
Subscript out of range means that it is not seeing something.

Many times it comes down to the sheet names. Make sure both of those sheet names exist and are typed in correctly

Subscript out of range means that it is not seeing something.

Many times it comes down to the sheet names. Make sure both of those sheet names exist and are typed in correctly.
Thank you, found the issue, works great. That's going to save a great deal of time..
 

geno32080

Board Regular
Joined
Jan 23, 2020
Messages
101
Office Version
  1. 2013
Platform
  1. Windows
The only other thing that is going wrong, is the worksheets that i have created have a background color and borders around the cells, When the data is pasted to the sheet it removes the background color and borders. Have you seen any code to stop that?
I tried this, that didn't work.
Range("B9:S98").Interior.ColorIndex = xlNone
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
3,812
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
What are you copying? Data, formulas?
 

geno32080

Board Regular
Joined
Jan 23, 2020
Messages
101
Office Version
  1. 2013
Platform
  1. Windows
The only other thing that is going wrong, is the worksheets that i have created have a background color and borders around the cells, When the data is pasted to the sheet it removes the background color and borders. Have you seen any code to stop that?
I tried this, that didn't work.
Range("B9:S98").Interior.ColorIndex = xlNone
Just data. dates, names client id's etc. ''m searching for a code to add in that would paste the values only.
here is the final code that is working, exept I need it t paste values only.
Set sourceworkbook = Workbooks.Open("D:\JEN FOLDER\IMPORT\Jacksonville.csv")
sourceworkbook.Sheets("Jacksonville").Range("A3:S98").Copy ThisWorkbook.Worksheets("jax").Range("B9:T98")
sourceworkbook.Close
 

Forum statistics

Threads
1,175,859
Messages
5,899,906
Members
434,805
Latest member
Nihon

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