VBA to Copy over Non Contiguous Cells to another Sheet

grf

Board Regular
Joined
Oct 30, 2004
Messages
70
Office Version
  1. 365
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

Board Regular
Joined
Oct 30, 2004
Messages
70
Office Version
  1. 365
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
In that case can you please post a sample of your data, on both sheets using the XL2BB add-in?
 

grf

Board Regular
Joined
Oct 30, 2004
Messages
70
Office Version
  1. 365

ADVERTISEMENT

copy.xlsx
ABCDEFGH
1Jim Thompson
2Cortijo Joe Bloggs35/2020
30 Property Maintenance
4 & Pool TechnicianNIE X-5070682-X
5NIEN/A
60
7
8JulInvoice
9
10DateJob DescriptionHrs/ VisitsRateBalanceNotes
1109/07/20Weed Spraying13030
1210/07/20Weed Spraying13030
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32Total60.00
33IVA @ 21% :12.60
34Balance0.0072.60
INVOICE
 

grf

Board Regular
Joined
Oct 30, 2004
Messages
70
Office Version
  1. 365
copy.xlsx
ABCDEFGH
1NameDateJob DescriptionHrs/VisitsRateBalanceNotesSl/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

Board Regular
Joined
Oct 30, 2004
Messages
70
Office Version
  1. 365

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Oct 30, 2004
Messages
70
Office Version
  1. 365
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
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
 

Forum statistics

Threads
1,141,074
Messages
5,704,147
Members
421,328
Latest member
mippy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top