Copy paste (macro correction)

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Copy paste (macro correction)

Post by zyxw1234 »

Hi Experts,

Code: Select all

Option Explicit

Sub CopyData()
    
    Dim pathSource As String, pathDest As String
    Dim wbSource As Workbook, wbDest As Workbook
    Dim wsSource As Worksheet, wsDest As Worksheet
    Dim rowSourceLast As Long, colSourceLast As Long, rowDestLast As Long
    
    
    pathSource = "C:UsersWolfieeeStyleDesktop1.xls"
    pathDest = "C:UsersWolfieeeStyleDesktopBook1.xlsx"

    
    Set wbSource = Workbooks.Open(pathSource, ReadOnly:=True)
    Set wsSource = wbSource.Worksheets(1)
    
    
    Set wbDest = Workbooks.Open(pathDest)
    Set wsDest = wbDest.Worksheets(1)
    
    
    rowSourceLast = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row
    colSourceLast = wsSource.Cells(1, wsSource.Columns.Count).End(xlToLeft).Column
    
    
    If wsDest.Range("A1").Value = "" Then
        rowDestLast = 0
    Else
        rowDestLast = wsDest.Cells(wsDest.Rows.Count, 1).End(xlUp).Row
    End If
    wsDest.Range(wsDest.Cells(rowDestLast + 1, 1), wsDest.Cells(rowDestLast + rowSourceLast - 1, colSourceLast)).Value = wsSource.Range(wsSource.Cells(2, 1), wsSource.Cells(rowSourceLast, colSourceLast)).Value
    
    
    wbDest.Save
    wbSource.Close
    wbDest.Close
    
End Sub

This macro works perfect when file has data But there is a chance that the file has a blank sheet (No data in it to copy & paste) So in that case I am getting error
Error Details -run time error 1004
Application defined error


If sheet doesn't have data to copy & paste then do nothing simply close all the file & do Nothing

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copy paste (macro correction)

Post by HansV »

Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Copy paste (macro correction)

Post by zyxw1234 »

Yes that problem is Solved
But for this i dont know what should i do?
Any Suggestions?

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Copy paste (macro correction)

Post by zyxw1234 »

Plz see i added some lines in the macro & now there is only small Error & Its easy to solve

Code: Select all

Option Explicit

Sub CopyData()
   
    Dim pathSource As String, pathDest As String
    Dim wbSource As Workbook, wbDest As Workbook
    Dim wsSource As Worksheet, wsDest As Worksheet
    Dim rowSourceLast As Long, colSourceLast As Long, rowDestLast As Long
    
    
    pathSource = "C:UsersWolfieeeStyleDesktop1.xls"
    pathDest = "C:UsersWolfieeeStyleDesktopBook1.xlsx"

    
    Set wbSource = Workbooks.Open(pathSource, ReadOnly:=True)
    Set wsSource = wbSource.Worksheets(1)
    
    
    Set wbDest = Workbooks.Open(pathDest)
    Set wsDest = wbDest.Worksheets(1)
    
    
    rowSourceLast = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row
    colSourceLast = wsSource.Cells(1, wsSource.Columns.Count).End(xlToLeft).Column
    
    Dim Cnt As Long
    If wsSource.Cells(1, 1) = "" Then
         wbSource.Close SaveChanges:=False
         wbDest.Close SaveChanges:=False
        Exit Sub
    Else
    If wsDest.Range("A1").Value = "" Then
        rowDestLast = 0
    Else
        rowDestLast = wsDest.Cells(wsDest.Rows.Count, 1).End(xlUp).Row
    End If
    wsDest.Range(wsDest.Cells(rowDestLast + 1, 1), wsDest.Cells(rowDestLast + rowSourceLast - 1, colSourceLast)).Value = wsSource.Range(wsSource.Cells(2, 1), wsSource.Cells(rowSourceLast, colSourceLast)).Value
    
    
    wbDest.Save
    wbSource.Close
    wbDest.Close
    
End Sub


Error Name- Compile Error Block If without End If

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copy paste (macro correction)

Post by HansV »

Each If ... Then line should have a corresponding End If.
Your code has two If ... Then lines, but only one End If.
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Copy paste (macro correction)

Post by zyxw1234 »

Code: Select all

Option Explicit

Sub CopyData()
    
    Dim pathSource As String, pathDest As String
    Dim wbSource As Workbook, wbDest As Workbook
    Dim wsSource As Worksheet, wsDest As Worksheet
    Dim rowSourceLast As Long, colSourceLast As Long, rowDestLast As Long
    
    
    pathSource = "C:UsersWolfieeeStyleDesktop1.xls"
    pathDest = "C:UsersWolfieeeStyleDesktopBook1.xlsx"

    
    Set wbSource = Workbooks.Open(pathSource, ReadOnly:=True)
    Set wsSource = wbSource.Worksheets(1)
    
    
    Set wbDest = Workbooks.Open(pathDest)
    Set wsDest = wbDest.Worksheets(1)
    
    
    rowSourceLast = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row
    colSourceLast = wsSource.Cells(1, wsSource.Columns.Count).End(xlToLeft).Column
    
    Dim Cnt As Long
    If wsSource.Cells(1, 1) = "" Then
         wbSource.Close SaveChanges:=False
         wbDest.Close SaveChanges:=False
        Exit Sub
    End If
    
    If wsDest.Range("A1").Value = "" Then
        rowDestLast = 0
    Else
        rowDestLast = wsDest.Cells(wsDest.Rows.Count, 1).End(xlUp).Row
    End If
    wsDest.Range(wsDest.Cells(rowDestLast + 1, 1), wsDest.Cells(rowDestLast + rowSourceLast - 1, colSourceLast)).Value = wsSource.Range(wsSource.Cells(2, 1), wsSource.Cells(rowSourceLast, colSourceLast)).Value
    
    
    wbDest.Save
    wbSource.Close
    wbDest.Close
    
End Sub

I added & edited the macro
Plz see its perfect & any suggestion or changes in the macro is required as per u then plz let me know HansV Sir

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Copy paste (macro correction)

Post by zyxw1234 »

This doubt is different, i am trying to solve this problem viewtopic.php?f=30&t=35051
Dim Lr1 As Long, Lr2 As Long: Let Lr1 = 7: Lr2 = 6(what this line does, & what it is?)

Code: Select all

Sub Macro()
Dim Wb1 As Workbook, Wb2 As Workbook
Set Wb1 = Workbooks.Open("C:UsersWolfieeeStyleDesktop1.xls")                                       
Set Wb1 = Workbooks.Open("C:UsersWolfieeeStyleDesktopBasketOrder.xlsx")            
Dim Ws1 As Worksheet, Ws2 As Worksheet
Set Ws1 = Wb1.Worksheets(1)
Set Ws2 = Wb2.Worksheets(1)
Dim Lr1 As Long, Lr2 As Long: Let Lr1 = 7: Lr2 = 6
Dim rngSrch As Range: Set rngSrch = Ws2.Range("C1:C" & Lr2 & "")
Dim rngDta As Range: Set rngDta = Ws1.Range("B2:B" & Lr1 & "")
Dim Cnt As Long 
    For Cnt = Lr2 To 1 Step -1 
    Dim MtchedCel As Variant  
     Set MtchedCel = rngSrch.Find(What:=rngDta.Item(Cnt), After:=rngSrch.Item(1), LookIn:=xlValues, Lookat:=xlWhole, Searchdirection:=xlNext, MatchCase:=True)
I completed the macro till here

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copy paste (macro correction)

Post by HansV »

Dim Lr1 As Long, Lr2 As Long

is a a so-called declaration. It tells Excel that you are going to use two variables named Lr1 and Lr2, and that they are both of type Long.
A Long is a whole number between -2147483648 and +2147483647.

Let Lr1 = 7

assigns the value 7 to Lr1, and

Lr2 = 6

assigns the value 6 to Lr2.
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Copy paste (macro correction)

Post by zyxw1234 »

But i will use the below
Let lr1 = ws1.Range("B" & ws1.Rows.Count).End(xlUp).Row

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copy paste (macro correction)

Post by HansV »

OK.
Best wishes,
Hans