Compile Error

  • Thread starter Thread starter Gerard Goodland
  • Start date Start date
G

Gerard Goodland

Hi all,

The first two lines if this I got from another response on here. I am
trying to to step down through a list after each time I go through the
copy and pastes and save the file. I get an error " Compile Error
Invalid or unqualified Reference" and the For myRow = 1 To
..Range("A65536").End(xlUp).Row is highlighted in yellow.I am starting to
get really lost with this part. Any help would be appreciated.
Thanks



Dim myRow As Long
For myRow = 1 To .Range("A65536").End(xlUp).Row

Sheets("Contacts").Select
Range("B7").Select
Selection.Copy
Sheets("Inventory").Select
Range("D4").Select
ActiveSheet.Paste
Sheets("Contacts").Select
Application.CutCopyMode = False
Range("C7").Select
Selection.Copy
Sheets("Inventory").Select
Range("D5").Select
ActiveSheet.Paste
Sheets("Contacts").Select
Application.CutCopyMode = False
Range("D7").Select
Selection.Copy
Sheets("Inventory").Select
Range("D6").Select
ActiveSheet.Paste
Range("D7").Select
Sheets("Contacts").Select
Application.CutCopyMode = False
Range("E7").Select
Selection.Copy
Sheets("Inventory").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Contacts").Select
Range("A7").Select
Selection.Copy
Sheets("Inventory").Select
Range("X4").Select
ActiveSheet.Paste
Sheets("Contacts").Select
Range("F7:G7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Inventory").Select
Range("W6").Select
ActiveSheet.Paste
Sheets("Contacts").Select
Application.CutCopyMode = False
ActiveWindow.SmallScroll ToRight:=3
Sheets("Inventory").Select
Range("X4").Select


Sheets("Inventory").Select
Sheets("Inventory").Copy
ActiveWorkbook.SaveAs _
Filename:=ActiveWorkbook.Worksheets(1).Range("x4").Value
ActiveWorkbook.Close SaveChanges:=True

Next myRow

End With


End Sub
 
You have a period before the word Range in the For statement. If
the range will always be on the active sheet, you may omit the
period.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Thanks that solved the compile error. My macro is not moving down to the
next row in my sheet. It just keeps doing the same one over and over.
What am I missing to get it to move to the next row down in the
"Contacts" sheet so I can do the next copy and paste etc...?

Thanks
 
What is the relationship between the lop[ counter, myRow, and the cells,
such as B 7, D7?

For instance if first iteration you want to copy from row 7, second row 8,
then that is fine, but how does the X4 and W6 come into it?
 
Hi, I am not sure I understand the question . I am copying from A7 , B7
C7 etc to various cells on the " Inventory " sheet into specific cells .
After the copy , paste and file save , I then need to move down to row 8
so I can copy from A8, B8, C8 etc ...

Thanks
 
Gerard,

Most of that is quite clear in the code. It is the 'various cells' that is
the problem, code needs a specific definition, it does not understand
various. In your code, you seem to copy from a column (say row 7) to a row
(A, B), etc, but suddenly W6 and X4 pop-up. That is not clear to me what the
rule is.

--
HTH

-------

Bob Phillips
Gerard Goodland said:
Hi, I am not sure I understand the question . I am copying from A7 , B7
C7 etc to various cells on the " Inventory " sheet into specific cells .
After the copy , paste and file save , I then need to move down to row 8
so I can copy from A8, B8, C8 etc ...

Thanks

Bob said:
What is the relationship between the lop[ counter, myRow, and the cells,
such as B 7, D7?

For instance if first iteration you want to copy from row 7, second row 8,
then that is fine, but how does the X4 and W6 come into it?
Click to expand...
Click to expand...
 
Hi Bob,

W6 and X4 are locations on the destination sheet where some of the info
from the "Contacts" is being pasted.
 
I know that, but how can I determine that it is W6 and X4 for row 7 when all
the others have been D4, D5, D6, etc.? And then what is it for row 8, then
9, etc.? In other words what is the rule that can be coded to?
 
Hi Bob, I hope I understand;
The copy and paste goes like this
From To
"Contacts" "Inventory"
A7------------------X4
B7-------------------D4
C7-------------------D5
D7--------------------D6
E7--------------------D7
F7----------------------W6
G7----------------------X6

After this the sheet " Inventory is copied to anew book and saved with
the filename using the value in X4 and closed. I then will need to go
back to "Contacts"and do a copy and paste as follows

From To
"Contacts" "Inventory"
A8------------------X4
B8-------------------D4
C8-------------------D5
D8--------------------D6
E8--------------------D7
F8----------------------W6
G8----------------------X6

and so on until there are no more contacts left on the "Contacts" sheet.
( aprox 300)

Thanks Bob for the running conversation.
 
Back
Top