Copy only certain values from 1 sheet to another

[jex57]

Hi all,
I have written some VBA to get the correct data values that I need from a sheet called DATA
I now want to do the following:

1 - Write the values (table name, comment, column) to a new sheet called TRANSPOSED
2 - Return to the DATA sheet and search for the next table value and then repeat step 1 without overwriting my previous values

I have managed to get the code to get my values, but now dont know how to move the values to the new TRANSPOSED sheet.
Any assistance would be greatly appreciated


Code thus far:

Sub Test()


Dim DataSheetName As String, TransposedSheetName As String
DataSheetName = "Data"
TransposedSheetName = "Transposed"


Dim countrow As Double, countTbl As Double, countPK As Double, countCol As Double, countColComment As Double
countrow = 2
Dim StopDataLoop As Boolean
StopDataLoop = False
Dim TableName As String, TableComment As String, TablePK As String
Dim ColumnName As String, ColumnDataType As String, ColumnComment As String




While StopDataLoop = False
    If Sheets(DataSheetName).Cells(countrow, 1) = "" And Sheets(DataSheetName).Cells(countrow + 1, 1) = "" And _
       Sheets(DataSheetName).Cells(countrow + 2, 1) = "" And Sheets(DataSheetName).Cells(countrow + 3, 1) = "" And _
       Sheets(DataSheetName).Cells(countrow + 4, 1) = "" And Sheets(DataSheetName).Cells(countrow + 5, 1) = "" And _
       Sheets(DataSheetName).Cells(countrow + 6, 1) = "" And Sheets(DataSheetName).Cells(countrow + 7, 1) = "" And _
       Sheets(DataSheetName).Cells(countrow + 8, 1) = "" And Sheets(DataSheetName).Cells(countrow + 9, 1) = "" And _
       Sheets(DataSheetName).Cells(countrow + 10, 1) = "" And Sheets(DataSheetName).Cells(countrow + 11, 1) = "" And _
       Sheets(DataSheetName).Cells(countrow + 12, 1) = "" And Sheets(DataSheetName).Cells(countrow + 13, 1) = "" And _
       Sheets(DataSheetName).Cells(countrow + 14, 1) = "" And Sheets(DataSheetName).Cells(countrow + 15, 1) = "" And _
       Sheets(DataSheetName).Cells(countrow + 16, 1) = "" And Sheets(DataSheetName).Cells(countrow + 17, 1) = "" And _
       Sheets(DataSheetName).Cells(countrow + 18, 1) = "" And Sheets(DataSheetName).Cells(countrow + 19, 1) = "" Then
      StopDataLoop = True
    
    End If

If Sheets(DataSheetName).Cells(countrow, 1) = "Table" Then
       TableName = Sheets(DataSheetName).Cells(countrow + 1, 2)
       TableComment = ""
       countTbl = countrow + 2
          While Sheets(DataSheetName).Cells(countTbl, 1) <> "Primary Key Column"
            TableComment = TableComment & Sheets(DataSheetName).Cells(countTbl, 2)
            countTbl = countTbl + 1
        Wend
      TablePK = ""
       countPK = countTbl + 2
          While Sheets(DataSheetName).Cells(countPK, 1) <> "Column"
             TablePK = TablePK & Sheets(DataSheetName).Cells(countPK, 1) & ", "
            countPK = countPK + 1
         Wend
       TablePK = Mid(TablePK, 1, Len(TablePK) - 2)
      countCol = countPK + 2
           While Sheets(DataSheetName).Cells(countCol, 1) <> ""
             ColumnName = Sheets(DataSheetName).Cells(countCol, 1)
            ColumnDataType = Sheets(DataSheetName).Cells(countCol, 2)
            countColComment = countCol
            
            'ColumnComment = Sheets(DataSheetName).Cells(countCol, 3)
        
        
        
       countCol = countCol + 1
            
            
            
        
              TableNameTransposed = TableName
             TableCommentTransponsed = TableComment
             Column
           
          Wend
        
       countrow = countCol 'Reset the overall counter
    End If












    countrow = countrow + 1
Wend


End Sub

 

[DanteAmor]

You can explain with examples what data you have, what you want to copy and where you want to put it.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.