Hi all!
i have a For loop with in a macro i use which copies specific cell from one sheet into a row on another, but one of the sections can have multiple inputs (Cells F11:F20) basically the loop runs through each section copying over these cells but will change for each F Cell. However all 10 cells may not be used so i dont want the same row being copied over multiple times, is there a way that i can add an IF statement into the loop to say that if F & X is blank to either move to the next X value, or end the loop completely? the loop which i have is as follows;
For X = 11 To 20
LR = Sheets("Back End Table").Range("A" & Rows.Count).End(xlUp).Row + 1
Else
'ID
Range("C5").Select
Selection.Copy
Sheets("Back End Table").Select
Range("A" & LR).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Add Account").Select
'SUFFIX
Range("D5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Back End Table").Select
Range("B" & LR).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Add Account").Select
'FIRST NAME
Range("B7:C7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Back End Table").Select
Range("C" & LR).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Add Account").Select
'SURNAME
Range("B8:C8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Back End Table").Select
Range("D" & LR).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Add Account").Select
'EMPLOYEE ID
Range("F7").Select
Application.CutCopyMode = False
election.Copy
Sheets("Back End Table").Select
Range("E" & LR).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Add Account").Select
'NETWORK LOGIN
Range("F8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Back End Table").Select
Range("F" & LR).Select
election.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Add Account").Select
'LOCATION
Range("B9:C9").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Back End Table").Select
Range("G" & LR).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Add Account").Select
'ADMIN GROUP
Range("B11:C11").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Back End Table").Select
Range("H" & LR).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Add Account").Select
'BUSINESS GROUP
Range("F" & X).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Back End Table").Select
Range("I" & LR).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Add Account").Select
'WORK CLASS
Range("B12:C12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Back End Table").Select
Range("J" & LR).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Add Account").Select
'DATA CLASS
Range("B13:C13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Back End Table").Select
Range("K" & LR).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Add Account").Select
'CASEWORK
Range("B15:C15").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Back End Table").Select
Range("L" & LR).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Add Account").Select
'ARL
Range("B16:C16").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Back End Table").Select
Range("M" & LR).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Add Account").Select
'LETTER SIG
Range("B18:C18").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Back End Table").Select
Range("N" & LR).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Add Account").Select
'DMS
Range("B20:C20").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Back End Table").Select
Range("O" & LR).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Add Account").Select
Next X
The section labelled "Business group" (i have higlighted in blue) contains the cells that can have multiple values.
if any more information is needed please let me know!
any help will be greatly appreciated!
Regards
Kieran
i have a For loop with in a macro i use which copies specific cell from one sheet into a row on another, but one of the sections can have multiple inputs (Cells F11:F20) basically the loop runs through each section copying over these cells but will change for each F Cell. However all 10 cells may not be used so i dont want the same row being copied over multiple times, is there a way that i can add an IF statement into the loop to say that if F & X is blank to either move to the next X value, or end the loop completely? the loop which i have is as follows;
For X = 11 To 20
LR = Sheets("Back End Table").Range("A" & Rows.Count).End(xlUp).Row + 1
Else
'ID
Range("C5").Select
Selection.Copy
Sheets("Back End Table").Select
Range("A" & LR).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Add Account").Select
'SUFFIX
Range("D5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Back End Table").Select
Range("B" & LR).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Add Account").Select
'FIRST NAME
Range("B7:C7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Back End Table").Select
Range("C" & LR).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Add Account").Select
'SURNAME
Range("B8:C8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Back End Table").Select
Range("D" & LR).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Add Account").Select
'EMPLOYEE ID
Range("F7").Select
Application.CutCopyMode = False
election.Copy
Sheets("Back End Table").Select
Range("E" & LR).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Add Account").Select
'NETWORK LOGIN
Range("F8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Back End Table").Select
Range("F" & LR).Select
election.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Add Account").Select
'LOCATION
Range("B9:C9").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Back End Table").Select
Range("G" & LR).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Add Account").Select
'ADMIN GROUP
Range("B11:C11").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Back End Table").Select
Range("H" & LR).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Add Account").Select
'BUSINESS GROUP
Range("F" & X).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Back End Table").Select
Range("I" & LR).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Add Account").Select
'WORK CLASS
Range("B12:C12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Back End Table").Select
Range("J" & LR).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Add Account").Select
'DATA CLASS
Range("B13:C13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Back End Table").Select
Range("K" & LR).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Add Account").Select
'CASEWORK
Range("B15:C15").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Back End Table").Select
Range("L" & LR).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Add Account").Select
'ARL
Range("B16:C16").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Back End Table").Select
Range("M" & LR).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Add Account").Select
'LETTER SIG
Range("B18:C18").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Back End Table").Select
Range("N" & LR).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Add Account").Select
'DMS
Range("B20:C20").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Back End Table").Select
Range("O" & LR).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Add Account").Select
Next X
The section labelled "Business group" (i have higlighted in blue) contains the cells that can have multiple values.
if any more information is needed please let me know!
any help will be greatly appreciated!
Regards
Kieran