Results 1 to 6 of 6

Copy data into specific columns using array

Threaded View

  1. #1
    Registered User
    Join Date
    09-18-2021
    Location
    Aarhus, Denmark
    MS-Off Ver
    O365
    Posts
    3

    Copy data into specific columns using array

    I'm trying to copy data from one sheet to another, but into columns arranged differently.

    I just an subscript error?

    Help is much appreciated

    Sub filtercopyrange()
    
    Dim x As Long, cls
    Dim DataArr() As String
    Dim iCount As Integer
    Dim rng1 As Range
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim fcol As Integer
    Dim lcol As Integer
    Dim valuee1 As Integer
    Dim lRow2 As Long
    Dim lRow1 As Long
    Dim iCntr As Long
    Dim i As Integer
    Dim ct As Variant
    
    Set sh1 = Sheets("Dataset")
    Set sh2 = Sheets("Forside")
    
    Sheets("Forside").Activate
    
    Application.ScreenUpdating = False
    
    Range("A7:Y5000").Clear
    
    valuee1 = Sheets("Forside").Range("D2").Value
    
    If IsNumeric(valuee1) = False Then
        Exit Sub
    Else
    
        lRow2 = sh1.Cells(Rows.Count, "A").End(xlUp).Row
            
        Sheets("Dataset").Activate
    
        valuee1 = Sheets("Forside").Range("D2").Value
        
        iCount = 6
        For i = 2 To lRow2
        
                ct = Range("L" & i).Value
                
                'MsgBox "ct = " & ct & " valuee1 = " & valuee1
                
                If ct = valuee1 Then
                    iCount = iCount + 1
                        DataArr(i, 0) = Range("A" & i).Value2 'ID
                        DataArr(i, 1) = Range("D" & i).Value2   'Address
                        DataArr(i, 2) = Range("E" & i).Value2   'Number
                        DataArr(i, 3) = Range("F" & i).Value2   'Country
                        DataArr(i, 4) = Range("G" & i).Value2   'City
                        DataArr(i, 5) = Range("H" & i).Value2   'Zip
                        DataArr(i, 6) = Range("I" & i).Value2   'Product
                        DataArr(i, 7) = Range("J" & i).Value2   'Bandwidth
                        DataArr(i, 8) = Range("R" & i).Value2   'NRC
                        DataArr(i, 9) = Range("S" & i).Value2   'MRC
                        MsgBox DataArr
    
                    With Sheets("Forside")
                        Range("A" & iCount).Value2 = DataArr(i, 0)   'ID
                        Range("B" & iCount).Value2 = DataArr(i, 1)   'Address
                        Range("C" & iCount).Value2 = DataArr(i, 2)   'Number
                        Range("F" & iCount).Value2 = DataArr(i, 3)   'Country
                        Range("E" & iCount).Value2 = DataArr(i, 4)   'City
                        Range("D" & iCount).Value2 = DataArr(i, 5)   'Zip
                        Range("G" & iCount).Value2 = DataArr(i, 6)   'Product
                        Range("H" & iCount).Value2 = DataArr(i, 7)   'Bandwidth
                        Range("J" & iCount).Value2 = DataArr(i, 8)   'NRC
                        Range("K" & iCount).Value2 = DataArr(i, 9)   'MRC
                    End With
                Else
                End If
        
        Next
        
        Sheets("Forside").Activate
        
    '   Worksheets("Sheet1").Range("A2").Copy
    '   Worksheets("Sheet2").Range("A2").PasteSpecial Paste:=xlPasteFormulas
    '   lRow1 = Sheets("Forside").Cells(Rows.Count, "A").End(xlUp).Row
        
    '   Range("A7:Y" & lRow1).Sort key1:=Range("L7:L" & lRow1), _
    '   order1:=xlDescending, Header:=xlNo
    
    Application.ScreenUpdating = True
    
       
    End If
    End Sub
    Last edited by KimK78; 09-19-2021 at 03:51 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy non blank data from columns into 2D array
    By kmmadhu in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 03-27-2021, 12:00 PM
  2. [SOLVED] Need help on copy data and paste to the specific columns in another worksheet
    By nelsonlauo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-19-2019, 06:25 AM
  3. [SOLVED] Macro to copy data from three columns to a specific column
    By jackyung in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-10-2017, 05:16 AM
  4. [SOLVED] copy data in specific columns to another sheet
    By steve400243 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-28-2017, 10:05 AM
  5. Replies: 8
    Last Post: 04-04-2013, 08:02 PM
  6. [SOLVED] Adding Record to data table + preserve array formulas in specific columns
    By olivierpbeland in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-21-2013, 02:33 PM
  7. Macro to copy specific data to specific columns from a pivot table
    By Raju Radhakrishnan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-09-2012, 07:24 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1