VBA Copy / Paste with Column Spaces?

[Glasgowsmile]

Good day,

I have the following code:

  ' Import Date through Index
         Range("A6:E185").Copy
         wkbCrntWorkBook.Sheets("Segmentation").Range("A4").PasteSpecial xlPasteValues
       
         ' Import Group
       
         Range ("G6:I185")
         wkbCrntWorkBook.Sheets("Segmentation").Range("G4").PasteSpecial xlPasteValues

I would like to fix the code so I don't have to go back and forth between worksheets copying a few columns and pasting a few columns. I would like to copy the full range 1x and paste into A4 but the issue is that column F has formulas in it so I need to skip this column when it pastes in the data.

How would I go about doing that?

 

[Glasgowsmile]

Add to the ranges you need to copy.

Sub Segmentation()
    Application.DisplayAlerts = False
    Dim wkbCrntWorkBook As Workbook
    Dim wkbSourceBook As Workbook
 
    Dim sh As Worksheet, ws As Worksheet
    Dim rng1 As Variant, rng2 As Variant
    Dim r1 As Long, c1 As Long
    Dim r2 As Long, C2 As Long
 
    Set wkbCrntWorkBook = ThisWorkbook
    Set sh = wkbCrntWorkBook.Sheets("Segmentation")

    With Application.FileDialog(msoFileDialogOpen)
        .Filters.Clear
        .Filters.Add "Excel 2007-13", "*.xlsx; *.xls; *.xlsm; *.xlsa"
        .AllowMultiSelect = False
        .Show
        Application.ScreenUpdating = False
        If .SelectedItems.Count > 0 Then
            Workbooks.Open .SelectedItems(1)
            Set wkbSourceBook = ActiveWorkbook
            Set ws = wkbSourceBook.Sheets(1)
     
     
            'wkbCrntWorkBook.Sheets("Segmentation").Range("A3:I185, N3:P185, U3:W185, AB3:AD185, AI3:AK185, AP3:AR185, AW3:AY185, BD3:BF185").ClearContents
   
       
            With ws
                rng1 = .Range("A6:E185").Value
                rng2 = .Range("G6:I185").Value
       
                r1 = UBound(rng1, 1): c1 = UBound(rng1, 2)
                r2 = UBound(rng2, 1): C2 = UBound(rng2, 2)
            End With


            With sh
           
                .Range("A4").Resize(r1, c1).Value = rng1
                .Range("G4").Resize(r2, C2).Value = rng2
            End With
   
            ' Import Date through Index
            '            Range("A6:BI185").Copy
            '            wkbCrntWorkBook.Sheets("Segmentation").Range("A4").PasteSpecial xlPasteValues
       
            wkbSourceBook.Close False
       
            Range("A3:A500").NumberFormat = "dd-mmm-yy"
            Range("C3:D500,G3:H500,K3:L500,O3:P500,S3:T500,W3:X500,AA3:AB500,AE3:AF500,AI3:AJ500").NumberFormat = "0.0%"
            Range("A3:AK200").HorizontalAlignment = xlCenter
       
        End If
        Application.CutCopyMode = False
    End With
 
    'myerror:
    '    With Application
    '        .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlCalculationAutomatic
    '    End With
    '    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Thanks! That works but if I need to add onto it as I have several more columns to add (and avoid), would it look like this? I changed the rng2, 1 to a 4 instead because I need to actually skip over 4 columns in the next entry. Not sure if that's the right way to do it.

I need to do this about... 6 more times, so I'd have to do r# for each one and declare the variables at the top As Long too, right?

With ws
                rng1 = .Range("A6:E185").Value
                rng2 = .Range("G6:I185").Value
                rng3 = .Range("K6:M185").Value
        
                r1 = UBound(rng1, 1): c1 = UBound(rng1, 2)
                r2 = UBound(rng2, 1): c2 = UBound(rng2, 2)
                r3 = UBound(rng2, 4): c3 = UBound(rng2, 2)
            End With

            With sh
            
                .Range("A4").Resize(r1, c1).Value = rng1
                .Range("G4").Resize(r2, C2).Value = rng2
                .Range("N4").Resize(r3, c3).Value = rng3
            End With

 

[davesexcel]

r3 = UBound(rng2, 4): c3 = UBound(rng2, 2)

Yes.
rng3 would also be required in this line

 

[Glasgowsmile]

Yes.
rng3 would also be required in this line

Thanks,

This is a more effective way than continuous copy/paste commands back and forth between sheets?

 

[Glasgowsmile]

Alright, I tried this and ran into a Subscript out of range error.

Did I miss something?

 Dim sh As Worksheet, ws As Worksheet
    Dim rng1 As Variant, rng2 As Variant, rng3 As Variant
    Dim r1 As Long, c1 As Long
    Dim r2 As Long, c2 As Long
    Dim r3 As Long, c3 As Long
    
        With ws
                rng1 = .Range("A6:E185").Value
                rng2 = .Range("G6:I185").Value
                rng3 = .Range("K6:M185").Value
        
                r1 = UBound(rng1, 1): c1 = UBound(rng1, 2)
                r2 = UBound(rng2, 1): c2 = UBound(rng2, 2)
                r3 = UBound(rng3, 4): c3 = UBound(rng3, 2)
            End With

            With sh
                .Range("A4").Resize(r1, c1).Value = rng1
                .Range("G4").Resize(r2, c2).Value = rng2
                .Range("N4").Resize(r3, c3).Value = rng3
            End With

 

[Glasgowsmile]

Alright, I tried this and ran into a Subscript out of range error.

Did I miss something?

 Dim sh As Worksheet, ws As Worksheet
    Dim rng1 As Variant, rng2 As Variant, rng3 As Variant
    Dim r1 As Long, c1 As Long
    Dim r2 As Long, c2 As Long
    Dim r3 As Long, c3 As Long
   
        With ws
                rng1 = .Range("A6:E185").Value
                rng2 = .Range("G6:I185").Value
                rng3 = .Range("K6:M185").Value
       
                r1 = UBound(rng1, 1): c1 = UBound(rng1, 2)
                r2 = UBound(rng2, 1): c2 = UBound(rng2, 2)
                r3 = UBound(rng3, 4): c3 = UBound(rng3, 2)
            End With

            With sh
                .Range("A4").Resize(r1, c1).Value = rng1
                .Range("G4").Resize(r2, c2).Value = rng2
                .Range("N4").Resize(r3, c3).Value = rng3
            End With

Disregard this - I learned that the rng3, 4 was the issue - I didn't actually need the 4 - the 1 worked fine so it's back to running smoothly lol.

 

[davesexcel]

Yes, 1 counts the rows and 2 counts the columns in the range array

 

[davesexcel]

This is a more effective way than continuous copy/paste commands back and forth between sheets?

This way is not copy/paste. it is just making 1 range=another range

 

[Glasgowsmile]

Yes, 1 counts the rows and 2 counts the columns in the range array

I think I did more work than I needed to?

There is no way to just do A6:AK185 and skip certain columns along the way? With this code, I realized what I've highlight doesn't have a column I need to avoid, so I'm not sure the UBound is actually doing anything here?

What I mean is the first rng1, I need all the data from A6:E185, without skipping any columns and it fits perfectly into A4 without issue. Does that make sense?

 With ws
                rng1 = .Range("A6:E185").Value
                rng2 = .Range("G6:I185").Value
                rng3 = .Range("K6:M185").Value
                rng4 = .Range("O6:Q185").Value
                rng5 = .Range("S6:U185").Value
                rng6 = .Range("W6:Y185").Value
                rng7 = .Range("AA6:AC185").Value
                rng8 = .Range("AE6:AG185").Value
                rng9 = .Range("AI6:AK185").Value
        
                r1 = UBound(rng1, 1): c1 = UBound(rng1, 2)
                r2 = UBound(rng2, 1): c2 = UBound(rng2, 2)
                r3 = UBound(rng3, 1): c3 = UBound(rng3, 2)
                r4 = UBound(rng4, 1): c4 = UBound(rng4, 2)
                r5 = UBound(rng5, 1): c5 = UBound(rng5, 2)
                r6 = UBound(rng6, 1): c6 = UBound(rng6, 2)
                r7 = UBound(rng7, 1): c7 = UBound(rng7, 2)
                r8 = UBound(rng8, 1): c8 = UBound(rng8, 2)
                r9 = UBound(rng9, 1): c9 = UBound(rng9, 2)
                
            End With

            With sh
                .Range("A4").Resize(r1, c1).Value = rng1
                .Range("G4").Resize(r2, c2).Value = rng2
                .Range("N4").Resize(r3, c3).Value = rng3
                .Range("U4").Resize(r4, c4).Value = rng4
                .Range("AB4").Resize(r5, c5).Value = rng5
                .Range("AI4").Resize(r6, c6).Value = rng6
                .Range("AP4").Resize(r7, c7).Value = rng7
                .Range("AW4").Resize(r8, c8).Value = rng8
                .Range("BD4").Resize(r9, c9).Value = rng9
            End With