PDA

View Full Version : Solved: copy, cut, paste: on a hidden sheet, possible?



mperrah
09-04-2007, 04:14 PM
I have a macro that copies cuts and pastes data to and from a sheet I would like to have hidden to avoid deletion.
When I hide the sheet and run the sub, it fails at select sheet (when hidden)

any ideas?

I could make a collection array to store the data temporarily (not sure how)
also there are cell formulas that I am not clear how to accomplish in VBA.

Thanks in advance
Mark

mdmackillop
09-04-2007, 04:28 PM
You can't select a hidden sheet, but it is not necessary to do so.
Range("A1").CurrentRegion.Copy Sheets(3).Range("A1")
Range("A1").CurrentRegion.Cut Sheets(3).Range("A20")

mperrah
09-04-2007, 04:53 PM
md:
I have data on 2 non hidden sheets "QCDetail" and "WQC"
on "library" I have a vlookup and match formula that looks at "QCDetail"
I have a macro that adds to the "QCDetail" sheet first
then deletes the data on WQC,
then pastes the new dta back to WQC and lastly sorts it.

The idea is the "WQC" needs to have the most curent list and I can't have the library page visible to avoid users messing up the formula.

If I put the vlookup on the WQC sheet and they add or remove a row the world stops spinning till I fix their copy and send it back.

I found a way to get the formulas off the main page but would be even better if the "library" sheet could be hidden and still allow the macros to run.

The place it halts is :
sheets("library").select

Does your post imply I can select data and range and copy and past omiting the first select sheet?

I found that specifying a range it is good to set the active sheet first by selecting... I'm still learning

mperrah
09-04-2007, 04:56 PM
Here is one of the codes I'm using
came from recorder...

Sheets("WQC").Select
Range("B2:C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Sheets("library").Select
Range("AT2:AU2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("WQC").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

mperrah
09-04-2007, 04:58 PM
So could I use:

with worksheets("library")
.Range("AT2:AU2").Select ' or no selecting data on the sheet either?

RonMcK3
09-04-2007, 07:10 PM
mperrah,

rbrhodes showed me the following techniqure several days ago; in this code snippet my userform is getting a table from a hidden file:


'//just tired...
Application.ScreenUpdating = False
Sheets("Data").Visible = True
Sheets("Data").Activate
LastRow = GetLastRow
'//fill array with data
ST = Sheets("Data").Range("A2:E" & LastRow).Resize(LastRow - 1, 5)
Sheets("Data").Visible = xlVeryHidden
Sheets(1).Activate
Application.ScreenUpdating = True
'//End tired...

Nothing happens on the screen: the app turns off screen updating, unhides the WS, gets the data it needs, hides the sheet, reactivates it as hidden, then, lets the screen resume doing updating.

You need to replace the 'LastRow ... ' and the 'ST= ... ' lines with your own code.

Cheers!

mperrah
09-04-2007, 08:00 PM
Thanks Ron,
That helps another file I was working to speed up copy and paste
I'm in a time cruch for a release, so I will have to leave it unhidden for now,
But I will try this and post back.
Thanks again.
Mark

mdmackillop
09-05-2007, 12:01 AM
Try my code with sheet 3 hidden. What does that tell you? Do you see the word "Select" there?

mperrah
09-05-2007, 01:31 AM
It errors on the paste special

Sub sortTechs()
' works with 2003
' sortTechs Macro
'
Sheets("WQC").Select
Range("B2:C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
' Sheets("library").Select
' Range("AT2:AU2").Select
' Range(Selection, Selection.End(xlDown)).Select
' Selection.Copy
Range("A1").CurrentRegion.Copy Sheets("library").Range("AT2:AU" & Rows.Count).End(xlUp)
Sheets("WQC").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub

rory
09-05-2007, 03:09 AM
But that has nothing to do with the question as it's not pasting on the hidden sheet! Your problem is that the application is not in cutcopymode, so you need to copy the data again before issuing the PasteSpecial.

mdmackillop
09-05-2007, 05:20 AM
Hi Mark,
I reiterate. Get rid of select. It will not work on a hidden sheet!

mperrah
09-05-2007, 10:11 PM
I'm sorry,
I'm pretty good with editing code and making tweaks,
but if the code is not close to what I'm trying I rely on the Recorder,
and I'm finding I have way better luck searching through this forum.

My recorder always activates the sheet, then selects it,
then selects the range, then you can start with the data.

The data being copied are lookup formulas and I need to paste the values.

I'm not sure the coding.
The range I need always starts at the same spot,
but flows down a different number of rows each time. B2:C 'tolastrow'

What I found was to:
stop screen updating, unhide, preform task,
then rehide, enable screen updating.

I attached a version before the unhide impliment

mdmackillop
09-06-2007, 12:07 AM
This is your code
Sheets("WQC").Select
Range("B2:C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents It won't work withot unhiding.
Simple substitution will give you
Range(Sheets("WQC").Range("B2:C2"), _
Sheets("WQC").Range("B2:C2").End(xlDown)).ClearContents
which will.

mperrah
09-06-2007, 11:53 AM
This isn't working but I think we're getting close.

I'm clearing wqc!b2:c2 down to end
copying (hidden)library!AT2:AU2 down to end (values only)
paste to wqc!B1

something like:
wqc!B2:C2.value = the valuesonly of library!AT2:AU2 (library is hidden)
then sort wqc by the values a to z in C

Sub sortTechs()
'
' sortTechs Macro

'only Sheets("library") is hidden not WQC...

Range(Sheets("WQC").Range("B2:C2"), _
Sheets("WQC").Range("B2:C2").End(xlDown)).ClearContents

Range(Sheets("WQC").Range("B2:C2"), _
Sheets("WQC").Range("B2:C2").End(xlDown).value = _
Range(Sheets("library").Range("AT2:AU2"), _
Sheets("library").Range("AT2:AU2").End(xlDown),(xlValues) ' somehow I need the values transfered

Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

mperrah
09-06-2007, 12:21 PM
What about trying this?
How do I make the contents of (library!AT2:AU lastemptycell)
an array in memory
then sort still in memory
Clear wqc first then
output array to (WQC!B2:C & till array done)

If this could work if sheets("Library") is hidden or not?

Sub getSort( )
Dim arr() As Integer
Dim str As String
Dim arrBound as range

Set arrBound = LBound to UBound (chtecharray) ' chtecharray is a named range for AT2:AU70 - (trying to make dynamic with last row)
redim arr(arrBound)
str = ""

For i = LBound To UBound
str = str & arr(i) & vbCrLf
Next i

Call Sort(arr)

str = ""
For i = LBound To UBound
str = str & arr(i) & vbCrLf
Next i

sheets("WQC").Range("B2").value = arr(i)
End Sub


Sub Sort(arr( ) As Integer)

Dim Temp As Double
Dim i As Long
Dim j As Long

For j = 2 To UBound(arr)
Temp = arr(j)
For i = j - 1 To 1 Step -1
If (arr(i) <= Temp) Then GoTo 10
arr(i + 1) = arr(i)
Next i
i = 0
10 arr(i + 1) = Temp
Next j

End Sub
I'm in very deep water here.
But guessing what goes were, this sounds like it might be close?

Please be gentle.

Mark

mperrah
09-06-2007, 09:10 PM
' these are named ranges on the "QCDetail" sheet
'=OFFSET(QCDetail!$A$1,0,0,COUNTA(QCDetail!$B:$B),3) namelist
' =OFFSET(QCDetail!$A$1,0,0,COUNTA(QCDetail!$B:$B),1) namecount

' these are on a hidden sheet "library"
in column
' =IF(MAX(NameCount)<ROW(1:1),"",VLOOKUP(ROW(1:1),NameList,3)) tech id
' =IF(MAX(NameCount)<ROW(1:1),"",VLOOKUP(ROW(1:1),NameList,2)) tech name

how could I perform the above calculations using an array

Sub UseArrayForCalc()
dim lstrow as range
set lstrow = sheets("QCDetail").OFFSET(QCDetail!$A$1,0,0,COUNTA(QCDetail!$B:$B),1)
With Sheets("QCDetail").Range("AO1:AO & lstrow")
.FormulaR1C1 = "=IF(MAX(NameCount)<ROW(1:1),"",VLOOKUP(ROW(1:1),NameList,2))"
.Copy
End With
With sheets("WQC").Range("B2")
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
End Sub


Am I getting closer? :doh:
Mark

mperrah
09-06-2007, 09:58 PM
Sub UseArrayForCalc()
Dim lstrow As Range
Dim wksht As Worksheet
Dim myCell As Range
Dim tchID As Range
Dim tchNM As Range

Set wksht = Worksheets("QCDetail")
Set tchID = wksht.Range("AO1:AO & uniquetechcount") 'errors here
' uniquetechcount is a named range on the same sheet
Set tchNM = wksht.Range("AP1:AP & uniquetechcount")


With tchID
.Formula = "=IF(MAX(NameCount)<ROW(1:1),"",VLOOKUP(ROW(1:1),NameList,2))"
.FillDown
.Copy
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With

With tchNM
.Formula = "=IF(MAX(NameCount)<ROW(1:1),"",VLOOKUP(ROW(1:1),NameList,3))"
.FillDown
.Copy
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With

End Sub
any closer ?

geekgirlau
09-06-2007, 11:07 PM
Sub sortTechs()
' sortTechs Macro
'only Sheets("library") is hidden not WQC...

' clear the WQC range
Range(Sheets("WQC").Range("B2:C2"), _
Sheets("WQC").Range("B2:C2").End(xlDown)).ClearContents

' copy range from Library
Range(Sheets("Library").Range("AT2:AU2"), _
Sheets("Library").Range("AT2:AU2").End(xlDown)).Copy _
Sheets("WQC").Range("B2")

' replace formula with values
Range(Sheets("WQC").Range("B2:C2"), _
Sheets("WQC").Range("B2:C2").End(xlDown)).Value = _
Range(Sheets("WQC").Range("B2:C2"), _
Sheets("WQC").Range("B2:C2").End(xlDown)).Value

Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

mperrah
09-07-2007, 09:43 AM
That works,
one thing. I loose the borders when using the cellvalue = value step.

I recorded a macro to copy formats then copy values,

was wondering how to put it in the code you got to work?

Sub a1bordertest()
'
' a1bordertest Macro
'
'
Sheets("WQC").Select
Range("D2:E8").Select
Selection.Copy
Sheets("Sheet1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("B3").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

' I tried this as a work around
' mid way through your code...
' sort wqc tech list
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Dim BorderArea As Worksheet
Dim myRng As Range
Dim myCell As Range

Set BorderArea = Worksheets("WQC")
With BorderArea
Set myRng = .Range("B2:C2", .Cells(.Rows.Count, "B").End(xlUp))
End With

With myRng.Borders
.LineStyle = xlContinuous
End With
End Sub

geekgirlau
09-09-2007, 08:46 PM
Sub SortTechs()
' sortTechs Macro
'only Sheets("library") is hidden not WQC...

Dim myRng As Range


' clear the WQC range
With Sheets("WQC")
Range(.Range("B2:C2"), .Range("B2:C2").End(xlDown)).ClearContents

' copy range from Library
Range(Sheets("Library").Range("AT2:AU2"), _
Sheets("Library").Range("AT2:AU2").End(xlDown)).Copy _
Sheets("WQC").Range("B2")

' replace formula with values
Set myRng = .Range("B2:C2", .Cells(.Rows.Count, "B").End(xlUp))
End With

With myRng
.Value = myRng.Value

.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

.Borders.LineStyle = xlContinuous
End With
End Sub

mperrah
09-10-2007, 11:51 PM
GeekGirlAU

I wish you lived close enough to take me under your wing.
Your help always gets right to the heart of what I need.
Thank you so much.

Mark

geekgirlau
09-11-2007, 07:01 PM
Always a pleasure to help when I can :curtsey: