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]

Your variable names are all over the place.

 

[johnnyL]

Maybe something like:

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]

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]

: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]

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]

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]

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]

What are you copying? Data, formulas?

 

[geno32080]

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