I have values in a work report that need to essentially get copy & pasted into tblStaging & tblTarget, and allow for the same thereafter. So, e.g., a team “Ravens” and city “Baltimore” from report “Report Type 1” has values in a table on the work report that need to be C&P’d into a table that will then be used as a staging table to paste the now-formatted values into a target table. This has to be done for multiple teams, locations, and report types, so clearing and reusing the stage tables is necessary. The end result should be a target table that houses all the data that is now pivoted from the work report.

Attached are images of the desired stepthrough and results.Steps 1-6, Step 6.5,Step 7,Step 8

Sub Example_1()

Dim sourceWorkbook As Workbook
Dim sourceWorksheet As Worksheet
Dim targetWorkbook As Workbook
Dim targetWorksheet As Worksheet
Dim k As Long

Set targetWorkbook = Workbooks("Learn VBA")
Set targetWorksheet = targetWorkbook.Sheets("Target Worksheet")
Set sourceWorksheet = targetWorkbook.Sheets("Source Worksheet")

targetWorkbook.Sheets("Source Worksheet").Activate
Range("B6").Select
Range(Selection, Selection.End(xlToRight)).Copy
Range("V6").PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False

Range("B7").Select
Range(Selection, Selection.End(xlToRight)).Copy
'Im wanting to select the cell after (below) the final cell in col v so I
'can append the table with the cells copied from B7 and to the right.
'Then I need this done for every row and to the right for B8:B10
Range("V6").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Offset(1, 0).Range("A1").PasteSpecial xlPasteValues, Transpose:=True
'I think something along the lines of:
For i = 7 To 10
Range("B" & i).Select
Range(Selection, Selection.End(xlToRight)).Copy
'Range("V", count rows + 1) pastevalues transpose:=True

'Range("B8", Selection.End(xlToRight)).Copy
'Range("V6", Selection.End(xlDown)).Offset(1, 0).PasteSpecial xlPasteValues, Transpose:=True
'Range("B9", Selection.End(xlToRight)).Copy
'Range("V6", Selection.End(xlDown)).Offset(1, 0).PasteSpecial xlPasteValues, Transpose:=True
'Range("B10", Selection.End(xlToRight)).Copy
'Range("V6", Selection.End(xlDown)).Offset(1, 0).PasteSpecial xlPasteValues, Transpose:=True

'Here, I want to copy the value in cell B1 and paste it to the entire col R, after the first
'cells are pasted values and transposed - see desired outcome
Range("B1").Copy
Range("R6", Selection.End(xlDown)).PasteSpecial xlPasteValues
Range("B2").Copy
Range("S6", Selection.End(xlDown)).PasteSpecial xlPasteValues
Range("B3").Copy
Range("Q6", Selection.End(xlDown)).PasteSpecial xlPasteValues

'How can I count the number of non-empty cells in Range("B6:M6"), then select that count
'of cells from the col "X6", and paste it for each instance that a row was copied in the
'tblStaging[Staging Table 4 (Period)]? - see desired outcome

'There is a formula that should continue down the column U:
'=XLOOKUP([@Period],tblPeriods[Period],tblPeriods[Date],"")

'Then, I want to take the now complete table tblStaging ("Q6:V40") copy and paste values
'to the target table tblTarget on the Target Worksheet tab

Range("Q6:V40").Copy
targetWorkbook.Sheets("Target Worksheet").Activate
Range("A2").PasteSpecial xlPasteValues

'Finally, I want to clear the tables "Q6:V40" and "B6:M10" so new data can be used
'I want to be able to do this for every new instance of Items 1, 2, and 3
'e.g., now, Item 1 = Cincinnati, Item 2 = Bengals, Item 3 = Report Type 2
'and new values that will be pasted into the rows x columns table ("B6:M10")

End Sub