Selecting range based on another cell last row (VBA)

[feni1388]

Hello....
Please help.....
I don't know what's wrong with this code.

Range("D6:G6").Select
Range("D6:G" & Range("F" & Rows.Count).End(xlUp).Row).Select

At this part, it ended up selecting A6:G39. Even though column F last row is F8.
There's no merge cells in all columns D to G.

Sub CopyData()

Sheets("Search").Select
Range("D6:G6").Select
Range("D6:G" & Range("F" & Rows.Count).End(xlUp).Row).Select


Sheets("NextPO").Select
Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Select
Application.ScreenUpdating = True
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False

Sheets("Search").Select
    
End Sub

先日付確認用-test.xlsm
	A	B	C	D	E	F	G	H
3		Delivery address				Item	Qty	Check
4
5	Search by Phone No　－→					DAB01165
6	Company A			DAB01165	1	GL43	10
7					2	GL44	10
8					3	JT05	5
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
検索

Cell Formulas
Range	Formula
F5	F5	=IF(AND(C5<>"",D6<>""),"検索条件を修正してください",IF(C5<>"",VLOOKUP(SUBSTITUTE(TRIM(C5),"-",""),得意先及び直送先!U:V,2,FALSE),D6))
A6	A6	=IF(D6="","",VLOOKUP(D6,得意先及び直送先!A:K,2,FALSE)&VLOOKUP(D6,得意先及び直送先!A:F,3,FALSE))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F5	Cell Value	contains "検索"	text	NO
A6	Cell Value	contains "様"	text	NO

 

[jolivanes]

In your first post you select the first free cell in Column C before you paste values so I assumed that that is where it should go.
Explain in a concise manner what you want to achieve.

Re: "I want the information to be pasted below the last used cell of Row "G", so the list can be continued."
But paste in Columns C, D, E and F?

 

[jolivanes]

If I understand you right, change this line

shNP.Cells(shNP.Rows.Count, 3).End(xlUp).Offset(1).Resize(lr - 5, 4).Value = shS.Cells(6, 4).Resize(lr - 5, 4).Value

to

shNP.Cells(shNP.Rows.Count, 7).End(xlUp).Offset(1, -4).Resize(lr - 5, 4).Value = shS.Cells(6, 4).Resize(lr - 5, 4).Value

 

[feni1388]

I tried another code and it works as I expected.
So I marked it as solved.
I know that the code maybe too long, but at least it gives me the outcome.

Thank you all for the help. I really appreciate it.

Sub CopyData()

Dim LastRow As Long

Sheets("Search").Select
Range("D6:G31").Select
Selection.Copy


LastRow = Sheets("NextPO").Cells(Rows.Count, "C").End(xlUp).Row
LastRow = LastRow + 1
Sheets("NextPO").Range("A" & LastRow).PasteSpecial
Application.CutCopyMode = False

    
    
End Sub

 

[feni1388]

If I understand you right, change this line

shNP.Cells(shNP.Rows.Count, 3).End(xlUp).Offset(1).Resize(lr - 5, 4).Value = shS.Cells(6, 4).Resize(lr - 5, 4).Value

to

shNP.Cells(shNP.Rows.Count, 7).End(xlUp).Offset(1, -4).Resize(lr - 5, 4).Value = shS.Cells(6, 4).Resize(lr - 5, 4).Value

Sorry, I missed your message.
I changed the code as you suggested but it still copied the last cell of column G in sheet Search.

What I want to achieve is copying Column D to G (sheet Search) with the last row used in either column F or G (because both column F and G should have the last row) to sheet NextPO from column A to D.
Then after the user change the customer code and items, qty in sheet Search, they will run the macro again to copy it to the next empty row (last row is either column C or D in sheet Next PO).

Sorry, the first message with Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Select is misleading and incorrect. I wanted to paste it to column A with either column C or D as the last used row.
I didn't realise it at first that by using this code, the customer code is pasted to column C instead of column A.

 

[jolivanes]

In your first post you start with "Please Help". So you need help. That is what the majority of people want on these sites.
Now there will be other people searching for help with a similar or same problem and end up here. They expect, just like you, to find a solution for their problem.
Is the code in Post #13 the solution? It does not look like to me because you find the next free cell again in Column C which you previously mentioned is not the case.
Could you be so kind and for people that end up here for a solution show us your working code.
Your kind help is appreciated.

 

[feni1388]

In your first post you start with "Please Help". So you need help. That is what the majority of people want on these sites.
Now there will be other people searching for help with a similar or same problem and end up here. They expect, just like you, to find a solution for their problem.
Is the code in Post #13 the solution? It does not look like to me because you find the next free cell again in Column C which you previously mentioned is not the case.
Could you be so kind and for people that end up here for a solution show us your working code.
Your kind help is appreciated.

Sorry, perhaps my explanation wasn't clear. It's better that I show you the result that I want to achieve.
As you can see from the table below.
I need to paste it to column A, but the next free cell has to be counted from column C.
From the code that I posted in Post #13, I had to copy the whole range from D6:G31 (ideally it should copy only until the last row used in column G). Then paste it to "NextPO" sheet column A, but count the last row from column C. I tried it several times and the table below was the result (it's as I expected).

先日付確認用.xlsm
	A	B	C	D	E
1
2	AES01032	1	GL72	1
3		2	GL71	2
4		3	BM43	3
5		4	GM73	5
6		5	GH73	10
7		6	BM70	10
8	AES01032	1	GL72	1
9		2	GL71	2
10		3	BM43	3
11		4	GM73	5
12		5	GH73	10
13		6	BM70	10
14		7	BM71	5
15	ASD01061	1	BM43	10
16	ASD01061	1	BM43	10
17	ASS01041	1	BM43	1
18		2	BM73	1
19		3	GL41	1
20		4	GL42	1
21		5	GL43	1
22		6	GL44	1
23		7	GL45	1
24		8	GH73	1
25		9	GH83	1
26
先日付受注

Cell Formulas
Range	Formula
B2,B8,B15:B17	B2	=IFERROR(IF(C2<>"",1,""),"")
B3:B7,B9:B14,B18:B26	B3	=IF(C3="","",IF(AND(C3<>"",B2=""),1,B2+1))

 

[feni1388]

I posted here the code that I just found on in the internet and modified it a bit to suit my purposes in case someone has the same problem as mine.
With this code, I was able to copy the range that I want even though the rows length changes dynamically (row F and G).

Sub CopyData()


Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range
Dim EndCell As Range
Dim SearchCell As Range

Set sht = Worksheets("Search")
Set StartCell = Range("C6")
Set EndCell = Range("G31")
Set SearchCell = Range("C6:G31")


'Find Last Row
  LastRow = SearchCell.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

'Select Range
  sht.Range("C6:G" & LastRow).Select
   
 Selection.Copy


LastRow1 = Sheets("NextPO").Cells(Rows.Count, "D").End(xlUp).Row
LastRow1 = LastRow1 + 1
Sheets("NextPO").Range("A" & LastRow1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Sheets("NextPO").Select
Range("F2").Select
ActiveCell.FormulaR1C1 = _
        "=IF(RC[-5]="""","""",IF(RC[-5]=RC[-5],COUNTIF(R2C1:RC[-5],RC[-5]),""""))"


If Application.CountA(Range("D:D")) = 2 Then GoTo oneresultskip
           
    'AutoFill Down
     Range("F2").AutoFill Range("F2:F" & Cells(Rows.Count, 4).End(xlUp).Row)
     'Values
           
oneresultskip:
           
            Range("F:F").Value = Range("F:F").Value

Application.CutCopyMode = False
Columns("F:F").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False

End Sub