Marbo
03-11-2016, 04:33 AM
Hi,
New to VBA and new to this forum ... so bear with me
Excel file holding a dashboard based on an Addin to collect data from an external source and print the result (after calculation). This part of the macro works fine.
However, trying to "repeat" the process 17 times (17 different companies) with one macro seems to be a bridge to far for me.
"MyRange" holds all the company names, starting in cell G4 ending in cell G20, on sheet "Parameters".
"MyCells" to use all company names one by one, to be copied in a specific cell (G13) on sheet "DB Comxxx"
The macro executes loop nr 1 fine (this is reflecting cell G4 = first company), but gives error message when he needs to repeat the process on loop nr 2 (this would be reflecting cell G5 = second company)
Part of the code ...
~Sheets("Parameters").Select
Dim MyRange As Range
Dim MyCell As Range
Set MyRange = Worksheets("Parameters").Range("G4:G20")
For Each MyCell In MyRange
MyCell.Select ---> ERROR "run time error 1004 / Select Method of Range class failed
Selection.Copy
Sheets("DB_Comxxx").Select
Range("G13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
....
Next MyCell
'
End Sub
Any suggestions to solve this error ? :dunno
Thx,
Marc
New to VBA and new to this forum ... so bear with me
Excel file holding a dashboard based on an Addin to collect data from an external source and print the result (after calculation). This part of the macro works fine.
However, trying to "repeat" the process 17 times (17 different companies) with one macro seems to be a bridge to far for me.
"MyRange" holds all the company names, starting in cell G4 ending in cell G20, on sheet "Parameters".
"MyCells" to use all company names one by one, to be copied in a specific cell (G13) on sheet "DB Comxxx"
The macro executes loop nr 1 fine (this is reflecting cell G4 = first company), but gives error message when he needs to repeat the process on loop nr 2 (this would be reflecting cell G5 = second company)
Part of the code ...
~Sheets("Parameters").Select
Dim MyRange As Range
Dim MyCell As Range
Set MyRange = Worksheets("Parameters").Range("G4:G20")
For Each MyCell In MyRange
MyCell.Select ---> ERROR "run time error 1004 / Select Method of Range class failed
Selection.Copy
Sheets("DB_Comxxx").Select
Range("G13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
....
Next MyCell
'
End Sub
Any suggestions to solve this error ? :dunno
Thx,
Marc