dabug80
asked on
Excel Macro Edit (find last blank row)
Hi,
I have the following macro code below. It currently copies data from the master sheet and transposes it to the data sheet to B2. I would like the macro code edited so it paste/transposes the copied data to the last blank row of the data sheet - after row 1. Is this possible?
Thanks
I have the following macro code below. It currently copies data from the master sheet and transposes it to the data sheet to B2. I would like the macro code edited so it paste/transposes the copied data to the last blank row of the data sheet - after row 1. Is this possible?
Thanks
Sub mac_1pasteallocation()
Sheets("Master").Select
Range("B3:B12").Copy
Sheets("Data").Select
Range("B2").PasteSpecial Paste:=xlPasteAll, Transpose:=True
Application.CutCopyMode = False
Range("A1").Select
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
try:
Sub mac_1pasteallocation()
Sheets("Master").Select
Range("B3:B12").Copy
Sheets("Data").Select
lastRow = ActiveSheet.Cells(ActiveSh eet.Rows.C ount, "B").End(xlUp).Row
Range("B" & lastRow + 1).PasteSpecial Paste:=xlPasteAll, Transpose:=True
Application.CutCopyMode = False
Range("A1").Select
End Sub
Sub mac_1pasteallocation()
Sheets("Master").Select
Range("B3:B12").Copy
Sheets("Data").Select
lastRow = ActiveSheet.Cells(ActiveSh
Range("B" & lastRow + 1).PasteSpecial Paste:=xlPasteAll, Transpose:=True
Application.CutCopyMode = False
Range("A1").Select
End Sub
While copying the data from Master sheet, you don't need to select the Master sheet necessarily. You just need to qualify the range which is being copied with the sheet reference and it will copy the range from that sheet no matter which sheet is currently active.
Therefore just the following line would be sufficient...
Therefore just the following line would be sufficient...
Sheets("Master").Range("B3:B12").Copy
ASKER
Thanks
Open in new window