3 questions regarding VBA Excel

[Aziz]

Hello,

1. How do I transfer a formatting from 1 sheet to another?
2. How do I select specific cells?
3. How do I save the sheet as a copy?

[Aussiebear]

From a very basic concept ( and Im sure others will have their methods)....

Transferring formatting

Sub ShtTransfer()
'worksheet to copy from
Dim sht1 As Worksheet
'worksheet to paste to
Dim sht2 As Worksheet
‘determine the sheet to copy from
Set sht1 = ThisWorkbook.Worksheets("Name of the Worksheet to copy from")
'Copy everything in the worksheet
sht1.Cells.Copy
'create new blank worksheet
Set sht2 = ThisWorkbook.Worksheets.Add
'first paste values
sht2.Cells.PasteSpecial xlPasteValues
' then paste formats
sht2.Cells.PasteSpecial xlPasteFormats
'give a name to your new worksheet
sht2.Name="Something"
End Sub

Selecting cells. There are a number of methods available, here are some to consider for selecting a single cell

To select cells on same sheet

ActiveSheet.Cells(5, 4).Select
-or-

ActiveSheet.Range("D5”).Select

To select cells on another sheet

Application.Goto ActiveWorkbook.Sheets("Sheet2").Cells(6, 5)
-or-

Application.Goto (ActiveWorkbook.Sheets("Sheet2").Range("E6"))

To Select cells on same sheet by activating the sheet

Sheets("Sheet2").Activate
ActiveSheet.Cells(6, 5).Select

Methods for selecting multiple cells

Select cells on the same sheet

ActiveSheet.Range(Cells(2, 3), Cells(10, 4)).Select
ActiveSheet.Range("C2:D10").Select
ActiveSheet.Range("C2", "D10").Select
or

ActiveSheet.Range(ActiveSheet.Cells(2, 3), ActiveSheet.Cells(10, 4)).Select
Or, alternatively, it could be simplified to this:

Range(Cells(2, 3), Cells(10, 4)).Select

Selecting cells on a different sheet

Application.Goto ActiveWorkbook.Sheets("Sheet3").Range("D3:E11")
Application.Goto ActiveWorkbook.Sheets("Sheet3").Range("D3", "E11")
Or, you can activate the worksheet, and then use the above to select the range:

Sheets("Sheet3").Activate
ActiveSheet.Range(Cells(3, 4), Cells(11, 5)).Select

Selecting cells on a different workbook

Application.Goto Workbooks("BOOK2.XLS").Sheets("Sheet1").Range("E4:F12")
Application.Goto Workbooks("BOOK2.XLS").Sheets("Sheet1").Range("E4", "F12")
Or, you can activate the worksheet, and then use the above to select the range:

Workbooks("BOOK2.XLS").Sheets("Sheet1").Activate
ActiveSheet.Range(Cells(4, 5), Cells(12, 6)).Select

To save the sheet as a copy (if using a Command button)


Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
ActiveSheet.Copy
With ActiveWorkbook
.SaveAs Filename:=ThisWorkbook.Path & "" & Format(Now, "mm-dd-yy") & ".xlsx", FileFormat:=51
.Close SaveChanges:=False
End With
Application.ScreenUpdating = True
End Sub