Feeds:
Posts
Comments

Archive for the ‘Excel’ Category

Sub CopyData2NewSheet()

Dim strOriginalSheetName As String
Dim strCopySheetName As String
Dim LastRow As Long, LastCol As Integer, Col As Integer, Row As Long, I As Long
Dim strCellData As String

strOriginalSheetName = “Sheet_Name”
strCopySheetName = strOriginalSheetName & “_Copy”

ThisWorkbook.Sheets(strOriginalSheetName).Activate

Sheets.Add After:=ActiveSheet
ActiveWorkbook.ActiveSheet.Name = strCopySheetName

ThisWorkbook.Sheets(strOriginalSheetName).Activate

LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
LastCol = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column

For Row = 1 To LastRow

For Col = 1 To LastCol

ThisWorkbook.Sheets(strOriginalSheetName).Activate

If Len(Cells(Row, Col)) = 2 Then

strCellData = Cells(Row, Col)
ThisWorkbook.Sheets(strCopySheetName).Activate

Cells(Row + Row, Col) = Left(strCellData, 1)
Cells(Row + Row + 1, Col) = Right(strCellData, 1)

Else

strCellData = Cells(Row, Col)
ThisWorkbook.Sheets(strCopySheetName).Activate

Cells(Row + Row, Col) = strCellData

End If

Next Col

ThisWorkbook.Sheets(strOriginalSheetName).Activate

Next Row

ThisWorkbook.Sheets(strCopySheetName).Activate

‘Insert Black Rows Between Every 2 Rows

For I = 2 To 1000 Step 3

Range(“A” & I).Select
ActiveCell.EntireRow.Insert

Next I

ThisWorkbook.Sheets(strCopySheetName).Activate

MsgBox “Data Copied Sucessfully…”

End Sub

Advertisements

Read Full Post »