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?
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?
From a very basic concept ( and Im sure others will have their methods)....
Transferring formatting
Selecting cells. There are a number of methods available, here are some to consider for selecting a single cellSub 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
To select cells on same sheet
-or-ActiveSheet.Cells(5, 4).Select
To select cells on another sheetActiveSheet.Range("D5”).Select
-or-Application.Goto ActiveWorkbook.Sheets("Sheet2").Cells(6, 5)
To Select cells on same sheet by activating the sheetApplication.Goto (ActiveWorkbook.Sheets("Sheet2").Range("E6"))
Methods for selecting multiple cellsSheets("Sheet2").Activate ActiveSheet.Cells(6, 5).Select
Select cells on the same sheet
orActiveSheet.Range(Cells(2, 3), Cells(10, 4)).Select ActiveSheet.Range("C2:D10").Select ActiveSheet.Range("C2", "D10").Select
Or, alternatively, it could be simplified to this:ActiveSheet.Range(ActiveSheet.Cells(2, 3), ActiveSheet.Cells(10, 4)).Select
Selecting cells on a different sheetRange(Cells(2, 3), Cells(10, 4)).Select
Or, you can activate the worksheet, and then use the above to select the range:Application.Goto ActiveWorkbook.Sheets("Sheet3").Range("D3:E11") Application.Goto ActiveWorkbook.Sheets("Sheet3").Range("D3", "E11")
Selecting cells on a different workbookSheets("Sheet3").Activate ActiveSheet.Range(Cells(3, 4), Cells(11, 5)).Select
Or, you can activate the worksheet, and then use the above to select the range:Application.Goto Workbooks("BOOK2.XLS").Sheets("Sheet1").Range("E4:F12") Application.Goto Workbooks("BOOK2.XLS").Sheets("Sheet1").Range("E4", "F12")
To save the sheet as a copy (if using a Command button)Workbooks("BOOK2.XLS").Sheets("Sheet1").Activate ActiveSheet.Range(Cells(4, 5), Cells(12, 6)).Select
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
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link