Copy and Paste on the Same Sheet-CodePudding

I have a workbook that serves as a database with an Input page. I want to make Column A dynamic, which will update header rows on all pages of the worksheet. I have created a macro that copies these header names from Column A on the Input Sheet, and pastes these values as headers on the next sheet. Once these header rows are labeled they are copied on Sheet 2 a second time so they can be pasted as additional header rows to the right of the previously pasted values. The reason is because they are values monitored at Start and Stop times, which will have different data stored at each time. Also, I would like these header rows to have medium weight borders around them. I have drafted the following code, but it only works partially correct by copying the first set as expected, however the second copy part does not work as well. I was hoping to create a template sheet in the document, which would have Date, Start Time, Space, End time. This would mean the copy rows would need to be inserted after Start Time and again after End time in a dynamic manner so this list could grow. Please see my attached code and thank you so much for any help.

Sub CopyData2()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim wb As Workbook
Dim lRow As Long
Dim lCol1 As Long
Dim lCol2 As Long
Dim cRange As Range
Dim iCell As Range
Dim iRange As Range

Set wb = ThisWorkbook

Set ws1 = wb.Sheets(1)

Set ws2 = wb.Sheets(2)

lRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row

lCol1 = ws2.Cells(12, Columns.Count).End(xlToLeft).Column

lCol2 = ws2.Cells(3, Columns.Count).End(xlToLeft).Column

ws1.Range("A13:A" & lRow).Copy

ws2.Range("C3").PasteSpecial xlPasteValues, Transpose:=True

Set cRange = ws2.Range(("C3"), ws2.Range("C3").End(xlToRight))

cRange.Select

cRange.Copy

ws2.Cells(3, lCol2).PasteSpecial xlPasteValues

End Sub

CodePudding user response：

I don't understand what you're trying to do with the Start/Stop times but the try removing cRange.Select to fix the 2nd Copy/Paste

CodePudding user response：

I'd suggest replacing the last 4 lines with those below

With ws2.Range(("C3"), ws2.Range("C3").End(xlToRight))
    ws2.Cells(3, lCol2).Resize(.Rows.Count,.Columns.Count).Value2 = .Value2
End With

in order to avoid both the Select, and redundant use of the clipboard.