Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Problem copying/pasting with variable in worksheet name

  Asked By: Magenta    Date: Mar 04    Category: MS Office    Views: 1135
  

i imagine this is a quick fix and i would really appreciate the help.
here is my code

Worksheets("Report - Acct Detail").Range("A1:Q8").Copy
Worksheets(Data(i)).Range("a1").PasteSpecial

the problem lies in the data(i) i believe which is an integer array. I
cannot figure out how to actually paste what i'm copying when using
this variable. any help would be much appreciated!

Share: 

 

6 Answers Found

 
Answer #1    Answered By: Felicia Hill     Answered On: Mar 04

Aren't you missing some parameters ?

see the link below - beware of wordwrap

www.google.com/search,GGLG:2005-38,G\
GLG:en&q=excel+vba+%22PasteSpecial+Method%22

 
Answer #2    Answered By: Corinne Rogers     Answered On: Mar 04

The parameter to the Worksheets call will either be an integer, being the sheet
number, or a string, being the sheet name.

Data(i) is an array  or a function called Data with a index or parameter called
i. What is Data (and if an array, what does it contain)? What is "i", and how
is it given a value?

 
Answer #3    Answered By: Agatha Miller     Answered On: Mar 04

data(i) is in a loop (sorry, i left a bunch of the code out), where
it loops through the array  and adds a sheet and renames it with the
value in the array. it then copies information from one worksheet,
and adds it to the worksheet  just added (named by the department in
the data array). there are 31 departments (these are accounted for
already), and the array has already been filled with the integer
number of the departments (e.g. 310). so the code looks more like
this:

for i = 1 to departments
Worksheets("Report - Acct Detail").Range("A1:Q8").Copy
Worksheets(Data(i)).Range("a1").PasteSpecial
next

also, the array is made up of all integers.

does that help?

 
Answer #4    Answered By: Sonya Flores     Answered On: Mar 04

Replace
Worksheets(Data(i)).Range("a1").PasteSpecial

With
Worksheets(CStr(Data(i))).Range("a1").PasteSpecial

A string is expected instead of an integer. Hope this helps.

 
Answer #5    Answered By: Eric Foster     Answered On: Mar 04

I think i'll try Hany's
suggestion because i need the department number to be "looked up" at
another stage of the code, so it would help  to just have the numeric
number. if that fails though, i will just make the change and see
where i need to change the lookup.

 
Answer #6    Answered By: Oliver Evans     Answered On: Mar 04

A number passed to Worksheets will be treated as an index into the collection,
not a reference to the name of the sheet.

I'd think that about the only way you'd be able to find sheets with numeric
names would be to loop through the sheets and check their names against what
you're looking for.

Numbers are not very good names for sheets. Name your sheets something like
"Department 310" - much more readable as well as easier to use.

You can still keep your integer  array of department numbers. Just append it to
"Department " every time you want to name or use a sheet. E.g.:

Worksheets("Department " & Data(i)).Range("a1").PasteSpecial

 
Didn't find what you were looking for? Find more on Problem copying/pasting with variable in worksheet name Or get search suggestion and latest updates.