0

I have a form with multiple drop down cells on a sheet titled "COPD Data Input" the populated cells are B1 to B41, once the sheet has been filled in, I want a button that copies all data from B1 to B41 and transposes it as values only into a sheet called "output" in cells A2 to AO2. I have created a macro that copies and transposes the data, then clears the original form. However I am struggling with pasting the data into the next blank row I.e. 2nd form should be transposed into A3 to AO3 and so on.

Original attempt was pasting over the inserted data each time. With some changes when I attempted to paste the data into the first blank row, each time the pasted data had moved one column (1st result pasted A2 onwards, 2nd B2 onwards then C2 etc.)

Sub Upload()
'
' Upload Macro

    '
    
    '
        Range("A2").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Copy
        Sheets("Log").Select
        Range("A3").Select
        ActiveSheet.Paste
        Sheets("Output").Select
        Sheets("Log").Select
        Range("A4").Select
        Sheets("COPD Data Input").Select
        Range("B1").Select
        Range(Selection, Selection.End(xlDown)).Select
      
        Application.CutCopyMode = False
        Selection.ClearContents
        
    End Sub
Robin
  • 1
  • 2
  • Please read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Also, you need to calculate the first non blank row on your destiny sheet. Here on SO there are about millions of examples of how to do it. Check [this](https://stackoverflow.com/a/38882823/9199828) – Foxfire And Burns And Burns Feb 13 '23 at 10:58

0 Answers0