VBA to Copy over Non Contiguous Cells to another Sheet

[grf]

Hello Experts

In Sheet INVOICE, I have the Client's Name in A1; The Invoice Slip/No in G1 and Row 10 sees the Headers for the Invoice and they are: A10 Date; B10 Job Description; C10 Hrs/Visits; D10 Rate; E10 Balance; F10 Notes
On Sheet INVDETAILS The Headers on Row A are from A1: Name; B1,Date; C1 Job Description; D1, Hrs/Visits; E1, Rate; F1,Balance; Notes, G1, Slip/No.
I would like to copy the details of each job on the Sheet INVOICE to the Sheet INVDETAILS. But as there could be more than one job included on the INVOICE Sheet, each row of work copied over would be different although the Client's Name on A1 and the Slip/No copied over would remain the same.
Any help pointing me in the right direction would be appreciated.
Regards, Graham

 

[Fluff]

How about

Sub grf()
   Dim Ws As Worksheet
   Dim Rng As Range
   
   Set Ws = Sheets("Invoice")
   Set Rng = Ws.Range("A11", Ws.Range("A" & Rows.Count).End(xlUp)).Resize(, 6)
   With Sheets("InvDetails")
      .Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(Rng.Rows.Count, 6).Value = Rng.Value
      .Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(Rng.Rows.Count).Value = Ws.Range("A1").Value
      .Range("H" & Rows.Count).End(xlUp).Offset(1).Resize(Rng.Rows.Count).Value = Ws.Range("G1").Value
   End With
End Sub

 

[grf]

Fluff, thank you so much for replying so quickly but I must confess that I'm guilty of making the stupid mistake of not supplying sufficient data initially.
For on the INVOICE sheet there contains totals and VAT addition data in cells A32 to A34 and E32 to E34. I'm so sorry, I thought I'd had everything covered.

 

[Fluff]

In that case can you please post a sample of your data, on both sheets using the XL2BB add-in?

 

[grf]

copy.xlsx
	A	B	C	D	E	F	G	H
1	Jim Thompson
2	Cortijo		Joe Bloggs				35	/2020
3	0		Property Maintenance
4			& Pool Technician		NIE X-5070682-X
5	NIE	N/A
6	0
7
8				Jul	Invoice
9
10	Date	Job Description	Hrs/ Visits	Rate	Balance	Notes
11	09/07/20	Weed Spraying	1	30	30
12	10/07/20	Weed Spraying	1	30	30
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32	Total				60.00
33	IVA @ 21% :				12.60
34	Balance			0.00	72.60
INVOICE

 

[grf]

copy.xlsx
	A	B	C	D	E	F	G	H
1	Name	Date	Job Description	Hrs/Visits	Rate	Balance	Notes	Sl/No
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
INVDETAILS

 

[grf]

First time I've tried XL2BB, I hope the above is acceptable
Regards, Graham

 

[Fluff]

Ok, thanks for that, brings up another couple of questions.
Will the word Total always be in A32, or can that change?
You originally said the SI number was in G1, but it appears to be in G2, which is correct?

 

[grf]

Yes Fluff, Total will always be in A32 on the INVOICE SHEET as Sl number will always be in G1 - not G2 as I have mistakenly placed it.

 

[Fluff]

Ok, how about

Sub grf()
   Dim Ws As Worksheet
   Dim Rng As Range
   Dim UsdRws As Long
   
   Set Ws = Sheets("Invoice")
   If Ws.Range("A31") <> "" Then
      UsdRws = 31
   Else
      UsdRws = Ws.Range("A32").End(xlUp).Row
   End If
   Set Rng = Ws.Range("A11:A" & UsdRws).Resize(, 6)
   With Sheets("InvDetails")
      .Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(Rng.Rows.Count, 6).Value = Rng.Value
      .Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(Rng.Rows.Count).Value = Ws.Range("A1").Value
      .Range("H" & Rows.Count).End(xlUp).Offset(1).Resize(Rng.Rows.Count).Value = Ws.Range("G1").Value
   End With
End Sub