0

I need to develop a macro that allows me to copy data from a file named 'file.csv' to a file 'data.csv'. The first file could be half empty and therefore at first I need to look for the data. The most important part is at the end when I try to paste it. It returns a strange error "Application-define or object-defined error" where there are the ***.

    Sub dataComposer()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim Filename As String
    Dim begin As Integer
    Dim over As Integer
    Dim wkbAll As Workbook
    Dim wkbTemp As Workbook
    Dim newSheet As Worksheet
    For y1 = 1 To 1 Step 1
    'Open the source file
    Set wkbTemp = Workbooks.Open(Filename:=ThisWorkbook.Path & "\file.csv")
    wkbTemp.Activate
    'Look for the part to copy
    For x1 = 1 To 200000 Step 1
        If IsEmpty(Cells(x1, 1)) = False Then
            begin = x1
            For x2 = x1 To 300000 Step 1
                If IsEmpty(Cells(x2, 1)) = True Then
                    over = x2
                    Exit For
                End If
            Next
            Exit For
        End If
    Next
    'Open the destination file
    Set wkbTemp1 = Workbooks.Open(Filename:=ThisWorkbook.Path & "\data.csv")

   'Copy the data from the source
    wkbTemp.Sheets(1).Cells.Copy
    Range(Cells(begin, 1), Cells(over - 1, 47)).Select
    Selection.Copy

    'Now, paste it into the destination
    Windows("data.csv").Activate
    Range(Cells(being, 1)).Select '***
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    'Save and close
    wkbTemp.Close
    wkbTemp1.Save
    wkbTemp1.Close

    Next

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True


    End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Cisco
  • 61
  • 1
  • 9
  • 1
    CSVs are text files - you shouldn't really need to open them in Excel at all. That said, see [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/4088852). – Comintern Nov 12 '18 at 22:18
  • why are you using csv? Also is the data in the source file in a table format? Data starts in A1 with headers etc.. – alowflyingpig Nov 12 '18 at 22:20
  • 1
    I think you are getting an error because `being` should be `begin` in the line `Range(Cells(being, 1)).Select '***`. To avoid (and discover) other errors due to typos, write `Option Explicit` before your code. – chillin Nov 13 '18 at 00:06

2 Answers2

0

Try something along these lines

Set wkbTemp = ThisWorkbook.Path & "\file.csv"
Set wkbTemp1 = ThisWorkbook.Path & "\data.csv"

'assume data is on "sheet1"
Set wkbTemp_data = wkbTemp.Sheets("Sheet1")
Set wkbTemp_data1 = wkbTemp1.Sheets("Sheet1")

'Get last row in source wkb
LastRow = wkbTemp_data.Range("A" & Rows.Count).End(xlUp).Row    

'Get last col
Last_Col = Cells(1, Columns.Count).End(xlToLeft).Column

'Return Col name from number
LastColName = Split(Cells(, Last_Col).Address, "$")(1)        

'Get data to copy
wkbTemp_data1.Range(A1).Value wkbTemp_data.Range("A1:" & LastColName  & ":" & LastRow).Value
alowflyingpig
  • 730
  • 7
  • 18
0

I found a solution and this is the code. Thank you everybody for helping anyway.

Sub dataComposer()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim Filename As String
Dim begin As Long
Dim over As Long
Dim wkbAll As Workbook
Dim wkbTemp As Workbook
Dim newSheet As Worksheet
Dim g1 As Integer
Dim g2 As Integer
Dim n1 As String
Dim n2 As String
g1 = Worksheets("Sheet1").Range("B1").Value
g2 = Worksheets("Sheet1").Range("D1").Value
n1 = Worksheets("Sheet1").Range("B2").Value
n2 = Worksheets("Sheet1").Range("B3").Value
For y1 = g1 To g2 Step 1
    'Filename = ThisWorkbook.Path & "\data" & y1 & ".csv"
    'Workbooks.Open Filename
    Set wkbTemp = Workbooks.Open(Filename:=ThisWorkbook.Path & "\" & n1 & y1 & ".csv")
    wkbTemp.Activate
    For x1 = 1 To 200000 Step 1
        If IsEmpty(Cells(x1, 1)) = False Then
        'Cell A2 is not blank
            begin = x1
            For x2 = x1 To 300000 Step 1
                If IsEmpty(Cells(x2, 1)) = True Then
                    over = x2
                    Exit For
                End If
            Next
            Exit For
        End If
    Next



    'wkbTemp1.Activate

    Set wkbTemp_data = wkbTemp.Sheets(n1 & y1)


    wkbTemp_data.Cells.Copy
    Range(Cells(begin, 1), Cells(over - 1, 47)).Select
    Selection.Copy
    'Now, copy what you want from x:
    'wkbTemp.Sheets(1).Range(Cells(begin, 1), Cells(over - 1, 47)).Copy

    Set wkbTemp1 = Workbooks.Open(Filename:=ThisWorkbook.Path & "\" & n1 & ".csv")
    Set wkbTemp_data1 = wkbTemp1.Sheets(n1)

    'Now, paste to y worksheet:
    Windows(n1 & ".csv").Activate
    'wkbTemp1.Sheets(1).Activate
    Range(Cells(begin, 1), Cells(over - 1, 47)).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


    'Activate Source Worksheet
    wkbTemp.Close
    wkbTemp1.Save
    wkbTemp1.Close
Next

For y1 = g1 To g2 Step 1
    'Filename = ThisWorkbook.Path & "\data" & y1 & ".csv"
    'Workbooks.Open Filename
    Set wkbTemp2 = Workbooks.Open(Filename:=ThisWorkbook.Path & "\" & n2 & y1 & ".csv")
    wkbTemp2.Activate
    For x1 = 1 To 200000 Step 1
        If IsEmpty(Cells(x1, 1)) = False Then
        'Cell A2 is not blank
            begin = x1
            For x2 = x1 To 300000 Step 1
                If IsEmpty(Cells(x2, 1)) = True Then
                    over = x2
                    Exit For
                End If
            Next
            Exit For
        End If
    Next



    'wkbTemp1.Activate

    Set wkbTemp_data2 = wkbTemp2.Sheets(n2 & y1)


    wkbTemp_data2.Cells.Copy
    Range(Cells(begin, 1), Cells(over - 1, 47)).Select
    Selection.Copy
    'Now, copy what you want from x:
    'wkbTemp.Sheets(1).Range(Cells(begin, 1), Cells(over - 1, 47)).Copy

    Set wkbTemp3 = Workbooks.Open(Filename:=ThisWorkbook.Path & "\" & n2 & ".csv")
    Set wkbTemp_data3 = wkbTemp3.Sheets(n2)

    'Now, paste to y worksheet:
    Windows(n2 & ".csv").Activate
    'wkbTemp1.Sheets(1).Activate
    Range(Cells(begin, 1), Cells(over - 1, 47)).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    'Activate Source Worksheet
    wkbTemp2.Close
    wkbTemp3.Save
    wkbTemp3.Close

Next

Application.ScreenUpdating = True
Application.DisplayAlerts = True


End Sub
Cisco
  • 61
  • 1
  • 9