sime2
10-13-2019, 09:32 PM
Hi,
I have been spinning in circles trying to get any working code for my problem, but nothing has worked so far.
I want to copy certain rows from one sheet e.g. B6 to B63 (including blank rows), from column B to the last column that has values, and paste transpose it to another sheet.
I have a code that copies data till the last row and column, but I really want to specify the last row. In addition, End(xlDown) stops when there is a blank row. My dataset that I want to copy has blank rows in between.
Thanks
Sub test2()
Application.ScreenUpdating = False
Dim sh2 As Worksheet, sh3 As Worksheet, sh4 As Worksheet
Set sh2 = Sheets(2)
Set sh3 = Sheets(3)
Set sh4 = Sheets(4)
With sh3
sh3.Range("B6", Range("B6").End(xlDown).End(xlToRight)).Copy
sh4.Range("C2").PasteSpecial xlPasteValues, Transpose:=True
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
I have been spinning in circles trying to get any working code for my problem, but nothing has worked so far.
I want to copy certain rows from one sheet e.g. B6 to B63 (including blank rows), from column B to the last column that has values, and paste transpose it to another sheet.
I have a code that copies data till the last row and column, but I really want to specify the last row. In addition, End(xlDown) stops when there is a blank row. My dataset that I want to copy has blank rows in between.
Thanks
Sub test2()
Application.ScreenUpdating = False
Dim sh2 As Worksheet, sh3 As Worksheet, sh4 As Worksheet
Set sh2 = Sheets(2)
Set sh3 = Sheets(3)
Set sh4 = Sheets(4)
With sh3
sh3.Range("B6", Range("B6").End(xlDown).End(xlToRight)).Copy
sh4.Range("C2").PasteSpecial xlPasteValues, Transpose:=True
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub