SouthernGent0327
New Member
- Joined
- Jan 30, 2021
- Messages
- 14
- Office Version
- 365
- Platform
- Windows
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.
VBA Code:
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