Range Object to set format ?

[Muhammad Raza]

Dear All

I am new to VBA and am trying to modify a VBA code which is below. The following code perfectly copies the values form one worksheet into another. But I also want it to copy formats.

Any ideas?? - Many Thanks

Sub Practice_1()


Dim datasource As Worksheet
Set datasource = ThisWorkbook.Worksheets("ABC2")
Dim tunnel As Range
Set tunnel = datasource.UsedRange
Dim WB As Workbook
Set WB = Workbooks.Add()
WB.Sheets(1).Range(tunnel.Address).Value = tunnel.Value


End Sub

 

[Andrew Poulsom]

Welcome to MrExcel.

Did you try?

tunnel.Copy WB.Sheets(1).Range(tunnel.Address)

 

[Muhammad Raza]

Not Working..

WB.Sheets(1).Range(tunnel.Address) will only define the range. Right ??

 

[Andrew Poulsom]

What do you mean by not working exactly?

 

[Muhammad Raza]

I mean i entered your line of code at the end and it didnt work.

If you wanted me to do something else - I told you I am new to VBA

 

[Andrew Poulsom]

This will copy the UsedRange on worksheet ABC2 to the first sheet in a new workbook?

Sub Practice_2()
    Dim datasource As Worksheet
    Set datasource = ThisWorkbook.Worksheets("ABC2")
    Dim tunnel As Range
    Set tunnel = datasource.UsedRange
    Dim WB As Workbook
    Set WB = Workbooks.Add
    tunnel.Copy WB.Sheets(1).Range(tunnel.Address)
End Sub

Isn't that what you want?

 

[Muhammad Raza]

Thanks Andrew.. Working perfectly..

But I just realized if I use UsedRange - it will never hide the rows and columns that are hidden in worksheet ABC2 - This means that I will need to copy paste entire sheet to get the hidden property settings.. Am I correct ?

 

[Andrew Poulsom]

Why don't you copy the worksheet rather than it's contents?

Sub CopySheet()
    ThisWorkbook.Worksheets("ABC2").Copy
End Sub

The new workbook will be the ActiveWorkbook.