Transposing from Column to Row

[LostinVA]

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

 

[Fluff]

Your explanation coupled with your code make no sense to me.
How would you determine how many rows & columns need to be copied from the template?

 

[LostinVA]

Sorry, I thought it might have been confusing. That's the thing, it's variable how many rows of data from the 'QRR Template' tab will need to be copied/pasted when the Command Button is pressed. The user might enter 5 rows of data on the 'QRR Template' or they might enter 10 rows of data. So if they enter 5 rows and the button is pressed, only those 5 rows of data should be copied and pasted/transposed onto the 'Extract for email' tab. If 10 rows of data are entered on the 'QRR Template', those 10 rows would be copied and pasted/transposed.

The way the code is now, it's copying a huge range of cells 'B6:AK1000', instead of determining how many rows/columns of data exist and only copying/pasting that. I do know the first row of data is B6:BA6. The number of rows is variable and dependant on what the user populates.

Hope that makes sense.

 

[LostinVA]

Sorry, I thought it might have been confusing. That's the thing, it's variable how many rows of data from the 'QRR Template' tab will need to be copied/pasted when the Command Button is pressed. The user might enter 5 rows of data on the 'QRR Template' or they might enter 10 rows of data. So if they enter 5 rows and the button is pressed, only those 5 rows of data should be copied and pasted/transposed onto the 'Extract for email' tab. If 10 rows of data are entered on the 'QRR Template', those 10 rows would be copied and pasted/transposed.

The way the code is now, it's copying a huge range of cells 'B6:AK1000', instead of determining how many rows/columns of data exist and only copying/pasting that. I do know the first row of data is B6:BA6. The number of rows is variable and dependant on what the user populates.

Hope that makes sense.

My mistake, the first row of data is B6:AK6

 

[Fluff]

Will row 6 always have data for each column to be copied?
Also will col B always have data for each row to be copied?

 

[LostinVA]

Yep, row 6 and column B should always have data to be copied. The Command Button won't be pressed unless there's data.

It could be just B6:AK6 to be copied or it could be B6:AK15 depending on what the user inputs.

 

[Fluff]

How about

Private Sub CommandButton1_Click()
Dim lr As Long, lc As Long
With Sheets("sheet2")
   lc = .Cells(6, Columns.Count).End(xlToLeft).Column
   lr = .Range("B" & Rows.Count).End(xlUp).Row
   .Range("B6", .Cells(lr, lc)).Copy
End With
If Range("B7") = "" Then
   Range("B7").PasteSpecial Transpose:=True
Else
   Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Transpose:=True
End If
End Sub

 

[LostinVA]

Nice!!!! It's almost there.. Looks to be working perfectly except for where it's pasting. Right now, the first time the button is pressed it's pasting as intended (B7). If the button is pressed again though, it's pasting underneath the previously pasted data. I'd like it to paste to the next open column to the right. So the first time the button is pressed, it's transposing the data perfectly and pasting starting in B7. The next time it's pressed, I'd like it to paste the data in the next open column to the right.. so if only one row of data was copied/transposed/pasted, the next button press would paste in C7. If 5 rows were copied/transposed/pasted, the next button press would paste in G7.

Appreciate all of your time helping me with this.. and your patience!

 

[Fluff]

In that case

Private Sub CommandButton1_Click()
Dim lr As Long, lc As Long
With Sheets("sheet2")
   lc = .Cells(6, Columns.Count).End(xlToLeft).Column
   lr = .Range("B" & Rows.Count).End(xlUp).Row
   .Range("B6", .Cells(lr, lc)).Copy
End With
If Range("B7") = "" Then
   Range("B7").PasteSpecial Transpose:=True
Else
   Cells(7, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial Transpose:=True
End If
End Sub

 

[LostinVA]

You are amazing! Thank you thank you!