Getting a range for a worksheet before copy

[rob737]

Hi Forum

Hope all is well. I recently came to the forum for some assistance with a Macro. I received a couple of responses however I was unable to make them work. As such I thought I would try again.

I have a macro that copies sheets from other workbooks into an equivalent sheet master book.

Sample

Sheets("DB EOL").Select

Workbooks.Open ("C:\Resilience Reports\Development\Supportability\DB EOL.xlsx")

Windows("DB EOL.xlsx").Activate

Range("A1:T1551").Select

Application.CutCopyMode = False

Selection.Copy

Windows("Supportability Resilience Report July 2017 V1.0.xlsx").Activate

ActiveSheet.Paste
​

In this example DB EOL is the target sheet and DB EOL is the source workbook.

I have already created a workbook DB EOL in Supportability Resilience Report July 2017 V1.0.xlsx.

The problem I have is I am using a fixed Range Range("A1:T1551").Select, and the size of this worksheet changes every month.

What I would like to do is open DB EOL and get the range of the worksheet without hard coding the line in the macro.

Hope someone can help me out here, and as always many thanks for any help with this.

Regards
Rob

 

[Norie]

Rob

Which sheet in the workbook being opened are you copying from?

 

[rob737]

Rob

Which sheet in the workbook being opened are you copying from?

Hi

the source workbook is called DB EOL - the one I am copying from

the source worksheet is called DB EOL

the target workbook is called Supportability Resilience Report July 2017 V1.0.xlsx

the target worksheet is called DB EOL

My macro has already created DB EOL in Supportability Resilience Report July 2017 V1.0.xlsx

Thanks for your help with this

Regards
Rob

 

[rob737]

Hi

the source workbook is called DB EOL - the one I am copying from

the source worksheet is called DB EOL

the target workbook is called Supportability Resilience Report July 2017 V1.0.xlsx

the target worksheet is called DB EOL

My macro has already created DB EOL in Supportability Resilience Report July 2017 V1.0.xlsx

Thanks for your help with this

Regards
Rob

Also the macro is executing from the workbook Supportability Resilience Report July 2017 V1.0.xlsx

Thanks

 

[TotallyConfused]

Hello Rob
Unless I am misunderstanding your question, what you are looking for is some way to create a range that will automatically vary in size as new data is entered. If this is correct, I think the following macro may be of some help to you. I was having the same problem with a range that changed size. This code will scan your sheet and create the new range according to the number of rows and columns of data.

You can substitute your own name instead of mine = ‘DataEntryItemsRn’.

Public Sub CreateRangeName()             'Create and names ranges ALSO places names in NAME MANAGER
    Dim DataEntryItemsRn As Range        'Name of range created
    Application.ScreenUpdating = False   'Turns OFF screen updating
 ActiveWorkbook.Worksheets("DataEntryItemsDB").Activate
 Cells(2, 1).Select         'Move cursor to cell A2 and make it the ActiveCell (Top left corner of range)
                            'The following line of code creates the range and names it 'DataEntryItemsRn'
 Set DataEntryItemsRn = Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column))
                            'Following line of code puts range name in NAME MANAGER
 ActiveWorkbook.Names.Add Name:="DataEntryItemsRn", RefersTo:="=" & DataEntryItemsRn.Address
 Application.ScreenUpdating = True  'Turns ON screen updating
End Sub

 

[rob737]

Hello Rob
Unless I am misunderstanding your question, what you are looking for is some way to create a range that will automatically vary in size as new data is entered. If this is correct, I think the following macro may be of some help to you. I was having the same problem with a range that changed size. This code will scan your sheet and create the new range according to the number of rows and columns of data.

You can substitute your own name instead of mine = ‘DataEntryItemsRn’.

Public Sub CreateRangeName()             'Create and names ranges ALSO places names in NAME MANAGER
    Dim DataEntryItemsRn As Range        'Name of range created
    Application.ScreenUpdating = False   'Turns OFF screen updating
 ActiveWorkbook.Worksheets("DataEntryItemsDB").Activate
 Cells(2, 1).Select         'Move cursor to cell A2 and make it the ActiveCell (Top left corner of range)
                            'The following line of code creates the range and names it 'DataEntryItemsRn'
 Set DataEntryItemsRn = Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column))
                            'Following line of code puts range name in NAME MANAGER
 ActiveWorkbook.Names.Add Name:="DataEntryItemsRn", RefersTo:="=" & DataEntryItemsRn.Address
 Application.ScreenUpdating = True  'Turns ON screen updating
End Sub

Many thanks for the suggestion I will give it a go tonight

Many thanks
Regards
Rob