Hi all -
I have a sheet (titled 'QRR Template') with multiple columns that users will be populating. On a second tab ('Extract for email'), there is a Command Button where if clicked, it copies the user data from the 'QRR Template' tab and transposes the data from row format to column format and pastes on cell B7. If the button is clicked a second time, it again copies data from the 'QRR Template' tab and transposes the data from row format to column format and pastes it at the end of the previously pasted data on row B. Where it pastes on row B is variable because it's dependent on number of rows of data from the 'QRR Template' tab.
I have the below code but it's not correct. Currently, its copying a huge range of cells (majority blank) from 'QRR Template' tab and pasting to the 'Extract for email' tab. I need it to only copy rows with data. If the user populates 5 rows worth of data, I only want it to copy/paste the 5 rows.. If there are 10 rows, copy/paste the 10 rows.. etc. Also, with this code, if the button is pressed multiple times, it's pasting below the existing data on the 'Extract for email' tab. I need it to paste at the end of the data on row B. If there are 10 columns worth of data and the button is pressed, I need it to copy the 10 rows of data from the 'QRR Template' tab, transpose from row format to column format, and paste to the 'Extract for email' tab at the end of the existing data, which would be B11.
I hope that makes sense. I'm a complete noob and am lost on how to make this work. Appreciate any help you all can provide!
Existing code for Command Button on the 'Extract for email' tab:
Private Sub CommandButton1_Click()
Sheets("QRR Template").Range("B6:AK1000").Copy
If ActiveSheet.Range("B7") = "" Then
ActiveSheet.Range("B7").PasteSpecial Transpose:=True
Else:
ActiveSheet.Range("B" & Columns.Count).End(xlUp).Offset(1).PasteSpecial Transpose:=True
End If
End Sub
I have a sheet (titled 'QRR Template') with multiple columns that users will be populating. On a second tab ('Extract for email'), there is a Command Button where if clicked, it copies the user data from the 'QRR Template' tab and transposes the data from row format to column format and pastes on cell B7. If the button is clicked a second time, it again copies data from the 'QRR Template' tab and transposes the data from row format to column format and pastes it at the end of the previously pasted data on row B. Where it pastes on row B is variable because it's dependent on number of rows of data from the 'QRR Template' tab.
I have the below code but it's not correct. Currently, its copying a huge range of cells (majority blank) from 'QRR Template' tab and pasting to the 'Extract for email' tab. I need it to only copy rows with data. If the user populates 5 rows worth of data, I only want it to copy/paste the 5 rows.. If there are 10 rows, copy/paste the 10 rows.. etc. Also, with this code, if the button is pressed multiple times, it's pasting below the existing data on the 'Extract for email' tab. I need it to paste at the end of the data on row B. If there are 10 columns worth of data and the button is pressed, I need it to copy the 10 rows of data from the 'QRR Template' tab, transpose from row format to column format, and paste to the 'Extract for email' tab at the end of the existing data, which would be B11.
I hope that makes sense. I'm a complete noob and am lost on how to make this work. Appreciate any help you all can provide!
Existing code for Command Button on the 'Extract for email' tab:
Private Sub CommandButton1_Click()
Sheets("QRR Template").Range("B6:AK1000").Copy
If ActiveSheet.Range("B7") = "" Then
ActiveSheet.Range("B7").PasteSpecial Transpose:=True
Else:
ActiveSheet.Range("B" & Columns.Count).End(xlUp).Offset(1).PasteSpecial Transpose:=True
End If
End Sub
