Hello,
I am a VBA beginner but I do have a computer programmer helping me with this macro (so I believe the problem is complex). I am having an issue with nested "for each" and what I believe is a problem with naming the variables in it. I am not going to paste the WHOLE macro because it's pretty extensive. All of the specific names (i.e. "NameCell", Workbooks and Worksheets) are defined in earlier lines. The problem is that the internal For Each is not recognizing cells that <> 0 as instructed. It skips all of the copy and paste code and moves to the next cell. I use a "For Each" earlier in the macro and it recognizes the cells just fine. Can I use another "For Each" with the same variable "c"? Here is the code: (sorry for the indent issue)
Dim cat As Range
For Each cat In Range(TargetCell, LastCell)
For Each c In Range(cat.Offset(0, 1), cat.Offset(0, 7))
If c.Value <> 0 Then
'This is all copy and paste code, which I don't think is an issue but I'm not sure becuase it hasn't run it yet.
Range(NameCell).Select
Application.CutCopyMode = False
Selection.Copy
Workbooks.Open DestBook
Sheets(DestSheet).Range("A:A").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(SrvGrpCell).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(DestSheet).Range("B:B").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(TargetCell).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(DestSheet).Range("C:C").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(c.Offset(-1, 0)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(DestSheet).Range("D:D").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(c.Value).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(DestSheet).Range("E:E").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Next c
Next cat
Sorry if this is confusing. Any help is appreciated. Please ask questions if this isn't clear (or if I really should paste the whole macro)
Thanks!
I am a VBA beginner but I do have a computer programmer helping me with this macro (so I believe the problem is complex). I am having an issue with nested "for each" and what I believe is a problem with naming the variables in it. I am not going to paste the WHOLE macro because it's pretty extensive. All of the specific names (i.e. "NameCell", Workbooks and Worksheets) are defined in earlier lines. The problem is that the internal For Each is not recognizing cells that <> 0 as instructed. It skips all of the copy and paste code and moves to the next cell. I use a "For Each" earlier in the macro and it recognizes the cells just fine. Can I use another "For Each" with the same variable "c"? Here is the code: (sorry for the indent issue)
Dim cat As Range
For Each cat In Range(TargetCell, LastCell)
For Each c In Range(cat.Offset(0, 1), cat.Offset(0, 7))
If c.Value <> 0 Then
'This is all copy and paste code, which I don't think is an issue but I'm not sure becuase it hasn't run it yet.
Range(NameCell).Select
Application.CutCopyMode = False
Selection.Copy
Workbooks.Open DestBook
Sheets(DestSheet).Range("A:A").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(SrvGrpCell).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(DestSheet).Range("B:B").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(TargetCell).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(DestSheet).Range("C:C").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(c.Offset(-1, 0)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(DestSheet).Range("D:D").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(c.Value).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(DestSheet).Range("E:E").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Next c
Next cat
Sorry if this is confusing. Any help is appreciated. Please ask questions if this isn't clear (or if I really should paste the whole macro)
Thanks!