XL_NOOB_89
New Member
- Joined
- Feb 8, 2022
- Messages
- 4
- Office Version
- 365
- Platform
- Windows
I see a ton of posts on this topic, but none of them are actually what I'm struggling with. The code below works fine, but it only carries over the values of the source cell (.values). I'm looking to also carry over the source formatting from the Input sheet - the source formatting will be dynamic. I don't want to use the .copy/.pastespecial command. Majority of the source cells on the Input sheet are text, but can also be numbers at times.
I'm basically running through a massive list on the Input sheet and copying everything over to the Output sheet with a row in between each line. I've tried multiple variations in the With clause, but nothing seems to work. Any help would be appreciated:
I'm basically running through a massive list on the Input sheet and copying everything over to the Output sheet with a row in between each line. I've tried multiple variations in the With clause, but nothing seems to work. Any help would be appreciated:
VBA Code:
Sub DataRetrieval()
Dim LR As Long, i As Long
Dim wsInput As Worksheet
Dim wsOutput As Worksheet
Dim lCount As Long
Dim CommRow As Long
Dim TheMark As Range
Set wsInput = ThisWorkbook.Worksheets("Input")
Set wsOutput = ThisWorkbook.Worksheets("Email Output")
Set TheMark = wsInput.Range("C3")
wsOutput.Range("C9:C1000").ClearContents
wsOutput.Range("C9:C1000").ClearFormats
CommRow = 9
For lCount = 1 To WorksheetFunction.CountIf(wsInput.Columns(3), "*")
Set TheMark = wsInput.Columns(3).Find(what:="*", after:=TheMark, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
With TheMark
wsOutput.Range("C" & CommRow).Value = TheMark
End With
CommRow = CommRow + 2
Next lCount
End Sub
Last edited by a moderator: