CODEWITHSUNDEEP
excelvba
Dexx
Dexx

Reputation: 1

excel VBA copying to blank cell

Im trying to find help with my last part on macro and cannot find working macro set. My macro have to copy data from A1-C1 (all 3 cells have to be copied in 1 copy data in a row) to another list starting on first blank cells starting from column A5-C5 and available range is from A5-C5 to A300-C300, second range is from D5-F5 to D300-F300 etc.. up to CG5-CJ5 to CG300-CJ300. Can someone please help me with creating macro for it? Thank you so much

PS: Im using this( not working bcz this is not for variable cell which i need and dont know how to apply certain data into it)

          ActiveSheet.Range("$A$1:$C$300").AutoFilter Field:=1, Operator:= _
    xlFilterValues, Criteria2:=Array(2, "4/1/2022")
Range("A2:C300").Select
Selection.Copy
Sheets("Sheet1").Select
Range("A5").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
ActiveSheet.Range("$A$1:$C$300").AutoFilter Field:=1
ActiveSheet.Range("$A$1:$C$300").AutoFilter Field:=1, Operator:= _
    xlFilterValues, Criteria2:=Array(2, "4/2/2022")
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("D5").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
ActiveSheet.Range("$A$1:$C$300").AutoFilter Field:=1
ActiveSheet.Range("$A$1:$C$300").AutoFilter Field:=1, Operator:= _
    xlFilterValues, Criteria2:=Array(2, "4/3/2022")
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("G5").Select
ActiveSheet.Paste

Upvotes: 0

Views: 79

Answers (1)

tnavidi
tnavidi

Reputation: 1393

You should try something yourself first, the below code may be useful addition to your macro:

dim rownum as integer
rownum = range("A300").End(xlUp).Row + 1
If rownum < 5 Then rownum = 5
cells(rownum, 1).value = range("A1").value
cells(rownum, 2).value = range("B1").value
cells(rownum, 3).value = range("C1").value

for the columns, you may want to add something like:

dim rownum as integer
dim colnum as integer
colnum = range("CK5").End(xlLeft).Column
rownum = cells(301,colnum).end(xlUp).Row + 1
if rownum < 5 then rownum = 5
cells(rownum, colnum).value = range("A1").value
cells(rownum, colnum + 1).value = range("B1").value
cells(rownum, colnum + 2).value = range("C1").value

Upvotes: 1

Related Questions