If you have trouble, you'll want to post the current version of your code.
Dale Fye wrote:
>
> Dave,
>
> I could swear I tried that, and that it wouldn't allow me to do the paste
> without the sheet being activated. I'll give it another try, though
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >I still would consider dropping the activate's completely.
> >
> > Dale Fye wrote:
> >>
> >> Thanks, Dave.
> >>
> >> I thought about that over dinner. I currently activate that sheet
> >> outside
> >> my loop, and needed to move that line inside the loop.
> >>
> >> "Dave Peterson" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > You can only select a range on the activesheet.
> >> >
> >> > So you could do:
> >> >
> >> > Set rng = sWbk.Worksheets(intWorksheet).Range(strRange)
> >> > swbk.activate
> >> > worksheets(inworksheet).select
> >> > rng.Select
> >> >
> >> > But you're going to have to do the same thing at the other end (before
> >> > you
> >> > paste), too.
> >> >
> >> > But better would be to drop the .select's and .activate's:
> >> >
> >> > ...
> >> > Set rng = sWbk.Worksheets(intWorksheet).Range(strRange)
> >> >
> >> > rng.copy
> >> > aWbk.Sheets(intWorksheet).Range("A" & (intRowPointer - 9)) _
> >> > .PasteSpecial Paste:=xlPasteValues, _
> >> > Operation:=xlNone, _
> >> > SkipBlanks:=False, _
> >> > Transpose:=False
> >> >
> >> >
> >> >
> >> > Dale Fye wrote:
> >> >>
> >> >> On a monthly basis, I recieve about 30 workbooks and have to
> >> >> consolidate
> >> >> them into a single workbook to forward to my prime contractor. This
> >> >> involves copying subsections of each of these workbooks into the same
> >> >> location (sheet/rows) in my consolidated workbook. I've got code
> >> >> working
> >> >> that loops through all of the workbooks in the same folder as my
> >> >> consolidated workbook, and then search those workbooks (3 sheets) for
> >> >> non-zero values in a particular row/column combination. This function
> >> >> FindNonZero( ) works great, returns a True/False value to indicate
> >> >> whether
> >> >> it found the non-zero value, and also gives me the worksheet and row
> >> >> pointer
> >> >> for the non-zero values.
> >> >>
> >> >> The portion of my code below works great if there is only a single
> >> >> non-zero
> >> >> value in the source workbook, but if the code finds a second non-zero
> >> >> value
> >> >> I get a Runtime Error '1004' Application-defined or object-defined
> >> >> error
> >> >> when the "Rng.Select" statement is executed, and my code stops
> >> >> running.
> >> >> I
> >> >> don't know whether it is because the source workbook (sWbk) already
> >> >> has a
> >> >> range selected (I have not figured out how to un-select a range
> >> >> programmatically) and Excel won't allow that, or whether there is
> >> >> something
> >> >> else wrong with my methodology. Would appreciate any assistance or
> >> >> other
> >> >> recommendations.
> >> >>
> >> >> Thanks, Dale
> >> >>
> >> >> While FindNonZero(sWbk, intWorksheet, intRowPointer) = True
> >> >> Debug.Print sWbk.Name, intWorksheet, intRowPointer,
> >> >> sWbk.Sheets(intWorksheet).Cells(intRowPointer, 12)
> >> >> strRange = "A" & (intRowPointer - 9) & ":K" &
> >> >> (intRowPointer -
> >> >> 1)
> >> >> Set rng = sWbk.Worksheets(intWorksheet).Range(strRange)
> >> >> rng.Select
> >> >> Selection.Copy
> >> >> aWbk.Sheets(intWorksheet).Activate
> >> >> aWbk.Sheets(intWorksheet).Range("A" & (intRowPointer -
> >> >> 9)).Select
> >> >> Selection.PasteSpecial Paste:=xlPasteValues, _
> >> >> Operation:=xlNone, _
> >> >> SkipBlanks:=False, _
> >> >> Transpose:=False
> >> >> Wend
> >> >
> >> > --
> >> >
> >> > Dave Peterson
> >
> > --
> >
> > Dave Peterson
--
Dave Peterson
|
Similar Threads
