caudillo
10-04-2022, 07:25 AM
Hello,
I am trying to build a code in Excel VBA to copy specific ranges from one sheet and paste them to specific area in another sheet. Every time I copy the specific ranges from the first sheet they have to be pasted in the next empty column of the specific area. The code is as folows:
Sub copyandpaste()
Dim sws As Worksheet
Dim dws As Worksheet
Set sws = Sheets("Sheet1")
Set dws = Sheets("Sheet2")
Sheets("Sheet1").Select
Range("C10:C11").Select
Selection.Copy
dws.Select
Range("B6:B7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet1").Select
Range("H20:H40").Select
Selection.Copy
dws.Select
Range("B9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
dws.Activate
End Sub
Every time I try to execute the code the data are pasted in the same B column. Is there a way to make it find the next empty column (C, D, E and so on) and paste the data there?
Any help would be really appreciated.
Thanks
I am trying to build a code in Excel VBA to copy specific ranges from one sheet and paste them to specific area in another sheet. Every time I copy the specific ranges from the first sheet they have to be pasted in the next empty column of the specific area. The code is as folows:
Sub copyandpaste()
Dim sws As Worksheet
Dim dws As Worksheet
Set sws = Sheets("Sheet1")
Set dws = Sheets("Sheet2")
Sheets("Sheet1").Select
Range("C10:C11").Select
Selection.Copy
dws.Select
Range("B6:B7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet1").Select
Range("H20:H40").Select
Selection.Copy
dws.Select
Range("B9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
dws.Activate
End Sub
Every time I try to execute the code the data are pasted in the same B column. Is there a way to make it find the next empty column (C, D, E and so on) and paste the data there?
Any help would be really appreciated.
Thanks