How to copy paste values to another sheet to next available dynamic blank rows using VBA

[pankajgrover]

Hi I have data which i have to copy AH3 to AP25 to paste values to another sheet name saturday. But i want to paste these values to next available automatic dynamic empty rows in Saturday sheet because above i have already previous Saturday data. In below example in Saturday sheet , data should be paste in 6th or 7th row onwards. how to do that.. Thanks

Shares Trading.xlsm
	AH	AI	AJ	AK	AL	AM	AN		AP
3	TEST	Gold	56324.00	S	14/01/2023	1	56324.00		3650
4	TEST	Silver	69427.00	S	14/01/2023	1	69427.00		5480
Vishal

Cell Formulas
Range	Formula
AI3:AI4	AI3	=A3
AJ3:AJ4	AJ3	=D3
AK3:AK4	AK3	=F3
AP3:AP4	AP3	=IF(OR(AK3="",AN3=""),"",(H3-O3)*B3*D3+IF(AK3="S",-K3+AO3-Q3-J3+AR3,-AO3+R3-Q3-J3+AR3))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AL3:AN30	Expression	=$AK3="S"	text	NO
AL3:AN30	Expression	=$AK3="B"	text	NO
AK3:AK30	Cell Value	="B"	text	NO
AK3:AK30	Cell Value	="S"	text	NO

Saturday Sheet:

Shares Trading.xlsm
	A	B	C	D	E	F	G	H
3	test	Gold	55017	S	23/12/2022	1	54574.00	-44300
4	test	Gold	55017	S	23/12/2022	1	54574.00	-22150
5								-66450
6
7
Saturday

Cell Formulas
Range	Formula
H5	H5	=SUM(H3:H4)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D3:D4,D8:D31,N3:N5	Cell Value	="B"	text	NO
D3:D4,D8:D31,N3:N5	Cell Value	="S"	text	NO

 

[Joe4]

AH to AP is a total of 9 columns. A to H is 8 columns.
I noticed that column AO is missing from your original image.
Is it hidden? Do you not want to copy this column over?

And it looks like column H on your "Saturday" sheet sometimes has a total in it.
So is it safe to assume that when trying to locate the next available row, we should be looking at this column H, and not some other column, like A?

 

[pankajgrover]

AH to AP is a total of 9 columns. A to H is 8 columns.
I noticed that column AO is missing from your original image.
Is it hidden? Do you not want to copy this column over?

And it looks like column H on your "Saturday" sheet sometimes has a total in it.
So is it safe to assume that when trying to locate the next available row, we should be looking at this column H, and not some other column, like A?

Hi Morning, yes AO is hidden column, i don't want to copy this and Column H hv total at last and you should consider after that next available row. If possible there shoud be blank row after the total row before the new data paste here.

 

[Joe4]

Try this:

Sub MyCopy2()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim lr As Long
    
'   Set sheet to copy from
    Set ws1 = Sheets("Vishal")
'   Set sheet to copy to
    Set ws2 = Sheets("Saturday")
    
'   Find last row on with data in column H on Saturday sheet
    lr = ws2.Cells(ws2.Rows.Count, "H").End(xlUp).Row
    
'   Copy from AH3:AN3 to new range
    ws1.Range("AH3:AN25").Copy
    ws2.Range("A" & lr + 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
'   Copy from AP3:AP25 to column H of new range
    ws1.Range("AP3:AP25").Copy
    ws2.Range("H" & lr + 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Application.CutCopyMode = False
        
End Sub