(Solved) – Transposing and formatting data in VBA, need help making sure my data is being edited on the correct sheet

(solved)-–-transposing-and-formatting-data-in-vba,-need-help-making-sure-my-data-is-being-edited-on-the-correct-sheet

This should be an easy problem to solve, but I am extremely unfamiliar with VBA so I need some help.

Essentially, I want to copy data given to me (finite column range, but dynamic rows), and paste it transposed into a second sheet within excel. From there, I need to convert all the values in columns D,E,F, and H to fractions out of 12. I’m pretty sure I’m 99% of the way there, but the last step (converting to fraction text) is being performed in the wrong worksheet. I’ve tried using the .Activate function as well as setting the correct worksheet, but neither work. There’s got to be an easy fix to this that I’m missing. Note: this is my first time ever coding in VBA so take it easy on me. Code is below:

Option Explicit

Sub FormatData()

Dim ws As Worksheet

' create a new worksheet, and name it "Master"
Set ws = ThisWorkbook.Worksheets.Add(after:=ThisWorkbook.Worksheets(1))
ws.Name = "Master"

' copy the UsedRange and Transpose
Worksheets("Sheet1").UsedRange.Copy
ws.Range("A1").PasteSpecial xlPasteAll, Transpose:=True

Sheets("Master").Activate

Dim i As Long, N As Long, j As Long
N = Cells(Rows.Count, "D").End(xlUp).Row
j = 2
For i = 2 To N
    Cells(i, "D") = WorksheetFunction.Text(Cells(i, "D"), "0 0/12")
    j = j   1
    Next i
j = 2
For i = 2 To N
    Cells(i, "E") = WorksheetFunction.Text(Cells(i, "E"), "0 0/12")
    j = j   1
    Next i
j = 2
For i = 2 To N
    Cells(i, "F") = WorksheetFunction.Text(Cells(i, "F"), "0 0/12")
    j = j   1
    Next i
j = 2
For i = 2 To N
    Cells(i, "H") = WorksheetFunction.Text(Cells(i, "H"), "0 0/12")
    j = j   1
    Next i

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *