Posts by ExcelChick

ExcelChick · Nov 14th 2013

Re: Paste from one worksheet to a range of cells on another worksheet

I expect that when it hits a cell with text in it (I guess thats the error in this case) that it will fill up 6 cells. What should I put instead of

On Error Resume Next?

When I comment out 'On Error Resume Next' it highlights this and says Runtime error 1004 application defined or object defined error
c = c.Offset(-5).Resize(6)

ExcelChick · Nov 14th 2013

Re: Paste from one worksheet to a range of cells on another worksheet

hm when I do this it's almost like it just skips the

Code

[COLOR=#333333]c.Offset(-5).Resize(6) [/COLOR]

Then it doesn't fill anything... Should I be using "" instead of vbNullstring? Am I using strVariable = vbNullString properly?

Code

Dim c As Range
    Dim d As Range




Sheets("Sup_Data").Select
    Dim strVariable As String
    
    strVariable = vbNullString




    Set d = Range("$G$2", "$G$3614")




    For Each c In d
    On Error Resume Next
    If c = vbNullString Then c = c.Offset(-5).Resize(6)
     Next c

Display More

ExcelChick · Nov 14th 2013

Re: Paste from one worksheet to a range of cells on another worksheet

This is awesome - thanks so much! I got this to work:

Code

Worksheets("CAN").Range("$F$5").Copy
Worksheets("Sup_Data").Range("C" & Rows.Count).End(xlUp).Offset(1).Resize(CMTRow).PasteSpecial (xlPasteValues)

Now I am loving this resize thing! I'm trying to Fill blank cells upwards 6 rows and am trying to use this to no avail - is this possible?:

So if there's text in G7 it would fill up to blank cells G2:G6. The below works great for fill down (without the resize)...

Code

Dim c As Range
Dim d As Range




Sheets("Sup_Data").Select
    Dim strVariable As String
    
    strVariable = vbNullString




    Set d = Range("G:G")




    For Each c In d
    On Error Resume Next
    If c = vbNullString Then c = c.Resize(6).End(xlUp).Offset(1, 0)




     Next c
'
    
End Sub

Display More

ExcelChick · Nov 13th 2013

Re: Paste from one worksheet to a range of cells on another worksheet

What do you mean fully qualified?

Is there a way to get it to paste the values after the last row of data? Something like this with the resize?

Code

Worksheets("CAN").Range("$F$1215").Copy
    Worksheets("Sup_Data").Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues) .Resize (CSGRows)

This isn't working

ExcelChick · Nov 13th 2013

Re: Paste from one worksheet to a range of cells on another worksheet

ah I got it this works

Code

Worksheets("CAN").Range("$F$5").Copy
    Worksheets("Sup_Data").Cells(intRow_02, intCol_03).Resize(CMTRow).PasteSpecial (xlPasteValues)

Thanks for your help!

ExcelChick · Nov 13th 2013

Re: Paste from one worksheet to a range of cells on another worksheet

This is awesome - but how would I just get the value? from F5?

ExcelChick · Nov 13th 2013

I am trying to paste one cell into a certain range. I'm using the introw/intcol defined as integers because it will be easier to add a row count when I go to the next 14 worksheets and need to paste the same cell. I am getting Runtime error 1004 Application defined or object defined error when I get to the worksheets("sup_data") line. Can anyone help me paste the value from the other worksheet?

Code

With Worksheets("CAN")
    .Range("$F$5").Copy
    Worksheets("Sup_Data").Range(Cells(intRow_02, intCol_03), Cells((intRow_02 + CMTRow), intCol_03)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With

Thanks!

ExcelChick · Nov 12th 2013

Hello,

I am relatively new to VBA and have cobbled together some code that basically stacks the data from certain cells from one worksheet and pastes them into another. it then filters on the wanted data, selects the visible cells, and pastes into a new worksheet. The code is running really slowly and I'll need to have it copy and paste from 14 other tabs as well (the data is all in the same ranges defined in the code). I do not know how to have it loop through my 14 other worksheets or make it so that the execution time is suitable for even one tab of data. (see 'Copy and paste Supervised data into Sup_Data Tab section) I think this section is causing the hang up because it's going through cell by cell and there's a ton?
The 14 other worksheets are:
USA
ASG
Gallia
IGEM
LAT
NOR
SPAI
UKI
ANZ
ASE
China
IND
JPN
Skorea

Code

Public Sub Supervised_Data_Build()
    Application.Calculation = xlCalculationManual
    ActiveSheet.DisplayPageBreaks = False
    Application.ScreenUpdating = False
 
'Unfilter
    Sheets("Sup_Data").Select
    If ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData
    End If
    
'Clear Existing Entries
    Sheets("Sup_Data").Select
    Range("A2:Q1048576").Select
    Selection.ClearContents
    
    Sheets("Sup_Data(2)").Select
    Range("A2:Q1048576").Select
    Selection.ClearContents
    




'Copy and paste Supervised data into Sup_Data Tab
    Dim r As Range, n As Long, i As Long




    Sheets("CAN").Select
    For Each r In Range("a21", "a3633")
    n = n + 1
    r.Range("a1:g1,I1,l1,o1,w1,z1,ac1,af1").Copy Sheets("Sup_Data").Cells((n + 1), 4)

Display More

For the 'Inserts CSG Name section I have a certain number of cells/rows I need to paste into from the "CAN" worksheet (and the 14 others eventually). I need those to be stacked on top of eachother but here I've just called out where they go rather than being able to say paste it in 587 rows or 604 rows subsequently

Code

'Inserts CSG Name
'--CMT +587rows
    With Sheets("CAN").Select
    [$F$5].Copy Sheets("Sup_Data").Range("$C$2", "$C$589")
'--FS  + 604 rows
    [$F$610].Copy Sheets("Sup_Data").Range("$C$590", "$C$1194")
'--H&PS + 604 rows
    [$F$1215].Copy Sheets("Sup_Data").Range("$C$1195", "$C$1799")
'--PRD + 604 rows
    [$F$1820].Copy Sheets("Sup_Data").Range("$C$1800", "$C$2404")
'--RES + 604 rows
    [$F$1820].Copy Sheets("Sup_Data").Range("$C$2405", "$C$3009")
'--Other + 604 rows
    [$F$3030].Copy Sheets("Sup_Data").Range("$C$3010", "$C$3614")
'Inserts SG_CD
    [$B$1].Copy Sheets("Sup_Data").Cells((n + 1), 1)
'Inserts GU
    [$B$2].Copy Sheets("Sup_Data").Cells((n + 1), 2)
    End With

Display More

I have tried to optimize this section with the vbNullString to fill a certain text upwards that will be needed in the final stacked data (to create a pivot) - but I'm not sure if there's a better way.[/COLOR][/B]

Code

'Fills/copies the WF/WG up to blank cells
    Dim c As Range
    Dim d As Range
    Dim strVariable As String
    strVariable = vbNullString




    Set d = Sheets("Sup_Data").Range("$G$2", "$G$3614")




    For Each c In d
    On Error Resume Next
    If c = vbNullString Then c = c.Offset(1, 0)
     Next c




    d(1.1).Select




    Next

Display More

[COLOR=#ff8c00][B]Here is the remainder of my code:

Code

'Filter for wanted data
   
    Worksheets("Sup_Data").Range("$A$1:$Q$1048576").AutoFilter Field:=9, Criteria1:=Array( _
    "Contract Hrs", "Supervised FTE", "Total Labor Costs", "Unloaded Chg Payroll"), _
    Operator:=xlFilterValues
    Worksheets("Sup_Data").Range("$A$1:$Q$8964").AutoFilter Field:=5, Criteria1:="<>"
    
    
    
    Dim LastRow As Integer
    LastRow = [A1048576].End(xlUp).Row




'Copy Visable Values
    Sheets("Sup_Data").Select
    Range("A2:Q" & LastRow).Select
    Selection.SpecialCells(xlCellTypeVisible).Copy




'Paste Values to Sup_Data(2) worksheet
    Worksheets("Sup_Data(2)").Select
    [A2].Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    
    Application.ScreenUpdating = True




'Save Sup_data(2)to new workbook
    'Sheets("Sup_Data(2)").Select
    'Sheets("Sup_Data(2)").Copy
    'Application.DisplayAlerts = False
    'ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & "Supervised Stacked Data" & ".xlsx", _
    'FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    'Application.DisplayAlerts = True
    'ActiveWorkbook.Close Saved = False

Display More