how to transpose values without selecting the cell

**vash1628** · 06-02-2012, 03:37 PM

you guys have spoiled me. i need some help with some coding. i'm trying to copy and transpose some info without actually selecting the cells. the following code works but the cells get selected in excel.

wsinputs.Range(Month) = wsinputs.Range("C19:L19").Value
'      Selection.Copy
   '   Pastes totals
       Range(Month).Select
       Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=True

what i'm looking for is something that works like this:
'

Sheets(Month).Range("C54:N54") = wsprod.Range("C21:N21").Value

Where you the user never see the macro running and it does not look like a ghost copying and pasting.

Some background:
In the spread sheet i'm trying to finish the macro that must run, must copy information from a few sheets to a sheet for that month, then it must clear the copied info from those sheets. But the first step would be to copy totals in C19:L19 in the inputs page and copy it to B28:B27 for July (transposing it). The macro determines the cell by another macro that tells it which month to select. for example:

Sub July()
MonthlyBackup ("Jul")
End Sub

Then i named a Cell under each month to help it select the right place. the whole code for that macros is:


Sub MonthlyBackup(ByVal Month As String)
Dim wsinputs As Worksheet, wsprod  As Worksheet, wsutil As Worksheet, wsonline As Worksheet, wscarrier As Worksheet, wspb As Worksheet

Set wsinputs = Worksheets("Inputs")
Set wsprod = Worksheets("Production")
Set wsutil = Worksheets("Utilization")
Set wsonline = Worksheets("Online Times")
Set wscarrier = Worksheets("Carrier gas")
Set wspb = Worksheets("PB's")

'gives reminder message box
 MsgBox ("Warning this button copies the required info to the required places then clears the months data and saves. Please make sure you printed the charts before continuing. ")
 'gives option as to weather you saved before or not
 a = MsgBox("Do You Wish To Continue?", vbYesNo)
    If a = vbNo Then
        MsgBox ("Please do so")
    End If
    If a = vbYes Then
    ' Copies info for Month
   ' runs selection app
      wsinputs.Range(Month) = wsinputs.Range("C19:L19").Value
'      Selection.Copy
   '   Pastes totals
       Range(Month).Select
       Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=True
        Application.CutCopyMode = False
     
    ' the following works just didn't want to have to tyoe in test data after every run
     '  Copies and pastes dates and shifts and results  worked to month
  '      Sheets(Month).Range("A5:B18") = wsinputs.Range("A5:B18").Value
'        Sheets(Month).Range("D5:R18") = wsinputs.Range("D5:R18").Value
      'COPIES THE PRODUCTION COMMENTS
'        Sheets(Month).Range("C54:N54") = wsprod.Range("C21:N21").Value
'        Sheets(Month).Range("A55:N58") = wsprod.Range("A22:N25").Value
'        Sheets(Month).Range("D59:k59") = wsprod.Range("D26:K26").Value
'        Sheets(Month).Range("A60:K61") = wsprod.Range("A27:K28").Value
'        Sheets(Month).Range("A62:N63") = wsprod.Range("A29:N30").Value
'        Sheets(Month).Range("C64:K64") = wsprod.Range("C31:K31").Value
'        Sheets(Month).Range("A65:K66") = wsprod.Range("A32:K33").Value
'        Sheets(Month).Range("A67:N67") = wsprod.Range("A34:N34").Value
'        Sheets(Month).Range("M59:N61") = wsprod.Range("M26:N28").Value
'        Sheets(Month).Range("M64:N66") = wsprod.Range("M31:N33").Value
     '   Copies reactor online comments
'        Sheets(Month).Range("C88:N88") = wsonline.Range("C21:N21").Value
'        Sheets(Month).Range("A89:N92") = wsonline.Range("A22:N25").Value
'        Sheets(Month).Range("D93:k93") = wsonline.Range("D26:K26").Value
 '       Sheets(Month).Range("A94:K95") = wsonline.Range("A27:K28").Value
'        Sheets(Month).Range("A96:N97") = wsonline.Range("A29:N30").Value
 '       Sheets(Month).Range("C98:K98") = wsonline.Range("C31:K31").Value
 '       Sheets(Month).Range("A99:K100") = wsonline.Range("A32:K33").Value
 '       Sheets(Month).Range("A101:N101") = wsonline.Range("A34:N34").Value
'        Sheets(Month).Range("M93:N95") = wsonline.Range("M26:N28").Value
 '       Sheets(Month).Range("M98:N100") = wsonline.Range("M31:N33").Value
 '     ' copies Extruder online data
 '       Sheets(Month).Range("C122:N122") = wsonline.Range("C55:N55").Value
 '       Sheets(Month).Range("A123:N126") = wsonline.Range("A56:N59").Value
 '       Sheets(Month).Range("D127:k127") = wsonline.Range("D60:K60").Value
 '       Sheets(Month).Range("A128:K129") = wsonline.Range("A61:K62").Value
 '       Sheets(Month).Range("A130:N131") = wsonline.Range("A63:N64").Value
 '       Sheets(Month).Range("C132:K132") = wsonline.Range("C65:K65").Value
 '       Sheets(Month).Range("A133:K134") = wsonline.Range("A66:K67").Value
 '       Sheets(Month).Range("A135:N135") = wsonline.Range("A68:N68").Value
 '       Sheets(Month).Range("M127:N129") = wsonline.Range("M60:N62").Value
 '       Sheets(Month).Range("M132:N134") = wsonline.Range("M65:N67").Value
 '     ' copies Carrier gas online daily times
 '       Sheets(Month).Range("C156:N156") = wscarrier.Range("C21:N21").Value
 '       Sheets(Month).Range("A157:N160") = wscarrier.Range("A22:N25").Value
 '       Sheets(Month).Range("D161:K161") = wscarrier.Range("D26:K26").Value
 '       Sheets(Month).Range("A162:K163") = wscarrier.Range("A27:K28").Value
  ''      Sheets(Month).Range("A164:N165") = wscarrier.Range("A29:N30").Value
'        Sheets(Month).Range("C166:K166") = wscarrier.Range("C31:K31").Value
 '       Sheets(Month).Range("A167:K168") = wscarrier.Range("A32:K33").Value
 '       Sheets(Month).Range("A169:N169") = wscarrier.Range("A34:N34").Value
 '       Sheets(Month).Range("M161:N163") = wscarrier.Range("M26:N28").Value
 '       Sheets(Month).Range("M166:N168") = wscarrier.Range("M31:N33").Value
 '     'Copies Pb
 '       Sheets(Month).Range("C190:N190") = wspb.Range("C21:N21").Value
 '       Sheets(Month).Range("A191:N194") = wspb.Range("A22:N25").Value
 '       Sheets(Month).Range("D195:K195") = wspb.Range("D26:K26").Value
 '       Sheets(Month).Range("A196:K197") = wspb.Range("A27:K28").Value
 '       Sheets(Month).Range("A198:N199") = wspb.Range("A29:N30").Value
 '       Sheets(Month).Range("C200:K200") = wspb.Range("C31:K31").Value
 '       Sheets(Month).Range("A201:K202") = wspb.Range("A32:K33").Value
 '       Sheets(Month).Range("A203:N203") = wspb.Range("A34:N34").Value
  '      Sheets(Month).Range("M195:N197") = wspb.Range("M26:N28").Value
 '       Sheets(Month).Range("M200:N202") = wspb.Range("M31:N33").Value
'''      Clears months data
  '      Sheets("Inputs").Select
  '      Application.Run ("clearmonth")
        ' DESELECTS COPIED SELECTION
  '      Range("A21").Select
  '      Application.CutCopyMode = False
  '      ActiveWorkbook.Save
    End If
    
End Sub

i dimmed(') the parts that are working and have modified the code since i first started on it to try incorporate the "variables" or what ever the "dims" are.
i am also attaching the spread sheet. the other months tabs are not there as i will copy and past them from the "Jul" tab then rename them