visual basic problem

B4L4KS

Board Regular
Joined
Mar 7, 2011
Messages
69
hello,

i have another problem with another worksteet, this involves customers being entered into a database and then moving down a line,

this is the code that i have

Sub adddetails()
'
' adddetails Macro
'
sourcerows = Array(8, 10, 12, 14, 16)
targetareas = Array("C", "D", "E", "F", "G")
targetrow = Sheets("Customers").Range("C" & Rows.Count).End(xlUp).Offset(1).Row
Sheets("Join Loyalty Scheme").Select
For i = LBound(sourcerows) To UBound(sourcerows)
Range("D" & sourcerows(i)).Resize(1, 1).Copy _
Destination:=Sheets("Customers").Range(targetareas(j) & targetrow)
Next
Application.CutCopyMode = False
End Sub

it doent work

anyhelp

kristian

note all the cells are correct just the visual basic is incorrect
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,830
Office Version
  1. 365
Platform
  1. Windows
Try adding the worksheet reference.

Something like this, assuming the source worksheet is 'Join Loyalty Sheet'.:)
Rich (BB code):
Worksheets("Join Loyalty Sheet").Range("D" & sourcerows(I)).Resize(1, 1).Copy _
Destination:=Sheets("Customers").Range(targetareas(j) & targetrow)
 

B4L4KS

Board Regular
Joined
Mar 7, 2011
Messages
69
hi it still doesnt work here is the code and where the data is going and coming from.

code
Sub adddetails()
'
' adddetails Macro
'

sourcerows = Array(8, 10, 12, 14, 16)
targetareas = Array("C", "D", "E", "F", "G")
targetrow = Sheets("Customers").Range("C" & Rows.Count).End(xlUp).Offset(1).Row
Worksheets("Join Loyalty Sheet").Range("D" & sourcerows(i)).Resize(1, 1).Copy _
Destination:=Sheets("Customers").Range(targetareas(i) & targetrow)

Next

Application.CutCopyMode = False
End Sub

[RANGE=cls:xl2bb-100][XR][XH=cs:3]Excel Workbook[/XH][/XR][XR][XH][/XH][XH]C[/XH][XH]D[/XH][/XR][XR][XH]8[/XH][XD=h:l|bc:ffff00]Name*[/XD][XD=h:l|bc:ffff00]a[/XD][/XR][XR][XH]9[/XH][XD=bc:0066cc][/XD][XD=bc:0066cc][/XD][/XR][XR][XH]10[/XH][XD=h:l|bc:ffff00]1st Line of Address*[/XD][XD=h:l|bc:ffff00]b[/XD][/XR][XR][XH]11[/XH][XD=bc:0066cc][/XD][XD=bc:0066cc][/XD][/XR][XR][XH]12[/XH][XD=h:l|bc:ffff00]Town *[/XD][XD=h:l|bc:ffff00]c[/XD][/XR][XR][XH]13[/XH][XD=bc:0066cc][/XD][XD=bc:0066cc][/XD][/XR][XR][XH]14[/XH][XD=h:l|bc:ffff00]County*[/XD][XD=h:l|bc:ffff00]d[/XD][/XR][XR][XH]15[/XH][XD=bc:0066cc][/XD][XD=bc:0066cc][/XD][/XR][XR][XH]16[/XH][XD=h:l|bc:ffff00]Postcode*[/XD][XD=h:l|bc:ffff00]e[/XD][/XR][XR][XH]17[/XH][XD=bc:0066cc][/XD][XD=bc:0066cc][/XD][/XR][XR][XH]18[/XH][XD=h:l|bc:ffff00]Contact Number[/XD][XD=bc:ffff00][/XD][/XR][XR][XH]19[/XH][XD=bc:0066cc][/XD][XD=bc:0066cc][/XD][/XR][XR][XH]20[/XH][XD=h:l|bc:ffff00]Email[/XD][XD=bc:ffff00][/XD][/XR][XR][XH=cs:3][RANGE][XR][XD]Join Loyalty Scheme[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

[RANGE=cls:xl2bb-100][XR][XH=cs:7]Excel Workbook[/XH][/XR][XR][XH][/XH][XH]C[/XH][XH]D[/XH][XH]E[/XH][XH]F[/XH][XH]G[/XH][XH]H[/XH][/XR][XR][XH]7[/XH][XD=h:c|fw:b|bc:ffff00]Customer[/XD][XD=h:c|fw:b|bc:ffff00]1st Line of Address[/XD][XD=h:c|fw:b|bc:ffff00]Town[/XD][XD=h:c|fw:b|bc:ffff00]County[/XD][XD=h:c|fw:b|bc:ffff00]Poscode[/XD][XD=h:c|fw:b|bc:ffff00]No. of Orders Made[/XD][/XR][XR][XH]8[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]9[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]10[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]11[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]12[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]13[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]14[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]15[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]16[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]17[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]18[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]19[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]20[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]21[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]22[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]23[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]24[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]25[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]26[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]27[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]28[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]29[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]30[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]31[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]32[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]33[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]34[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]35[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]36[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]37[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]38[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]39[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]40[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]41[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]42[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]43[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]44[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]45[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH]46[/XH][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][XD=bc:ffff00][/XD][/XR][XR][XH=cs:7][RANGE][XR][XD]Customers[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

many thanks

kristian
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,830
Office Version
  1. 365
Platform
  1. Windows
This worked for me.
Code:
Sub adddetails()
 
    sourcerows = Array(8, 10, 12, 14, 16)
    
    targetareas = Array("C", "D", "E", "F", "G")
    
    targetrow = Sheets("Customers").Range("C" & Rows.Count).End(xlUp).Offset(1).Row
    
    For I = LBound(sourcerows) To UBound(sourcerows)
        Worksheets("Join Loyalty Sheet").Range("D" & sourcerows(I)).Copy Sheets("Customers").Range(targetareas(I) & targetrow)
    Next I
 
End Sub
You could do it like this too, which will either make things clearer or do the opposite and overcomplicate.:)
Code:
Option Explicit
 
Sub adddetails()
Dim wsSrc As Worksheet
Dim wsDst As Worksheet
Dim rngSrc As Range
Dim rngDst As Range
Dim targetrow As Long
Dim sourcerows
Dim targetareas
Dim I As Long
 
    sourcerows = Array(8, 10, 12, 14, 16)
 
    targetareas = Array("C", "D", "E", "F", "G")
 
    Set wsSrc = Worksheets("Join Loyalty Sheet")

    Set wsDst = Sheets("Customers")
 
    targetrow = wsDst.Range("C" & Rows.Count).End(xlUp).Offset(1).Row
 
    For I = LBound(sourcerows) To UBound(sourcerows)
        Set rngSrc = wsSrc.Range("D" & sourcerows(I))
        Set rngDst = wsDst.Range(targetareas(I) & targetrow)
        rngSrc.Copy rngDst
    Next I
 
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,817
Messages
5,525,070
Members
409,617
Latest member
Lenaf

This Week's Hot Topics

Top