vba code correction

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

vba code correction

Post by zyxw1234 »

Code: Select all

Sub CopyColumn()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set wb1 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\1.xls")
Set wb2 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\Alert..csv")
Set ws1 = wb1.Worksheets.Item(1)
Set ws2 = wb2.Worksheets.Item(1)
With ws1
.Range(.Cells(1, 9), .Cells(.Rows.Count, 9).End(xlUp)).Copy .Cells(1, 2)'(i have issue with this line wb1 has 1st row as headers so ignore the first row and copy from row I2 and paste it to wb2 in column 2 from 1st row B1 )
End With
 Wb2.Save
 Let Application.DisplayAlerts = False
 Wb2.Close
 Let Application.DisplayAlerts = True
 Wb1.Close
End Sub


plz convert ALERT.xlsx to csv


https://www.ozgrid.com/forum/index.php? ... ost1234453" onclick="window.open(this.href);return false;
You do not have the required permissions to view the files attached to this post.

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

Re: vba code correction

Post by HansV »

The line

Code: Select all

    .Range(.Cells(1, 9), .Cells(.Rows.Count, 9).End(xlUp)).Copy .Cells(1, 2)
copies some cells from ws1 to ws1. I think you want to copy them to ws2. And if you want to start copying from row 2:

Code: Select all

    .Range(.Cells(2, 9), .Cells(.Rows.Count, 9).End(xlUp)).Copy ws2.Cells(1, 2)
Best wishes,
Hans

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

Re: vba code correction

Post by zyxw1234 »

.Range(.Cells(2, 9), .Cells(.Rows.Count, 9).End(xlUp)).Copy ws2.Cells(1, 2)
by putting the same i m not getting correct result
plz run the vba code on the sample file sir & before runing the code plz convert alert.xlsx to alert.csv

.Range(.Cells(2, 9), .Cells(.Rows.Count, 9).End(xlUp)).Copy ws2.Cells(1, 2)
this lines in unable to paste the data to ws2 column 2
Plz relook into it HansV Sir

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

Re: vba code correction

Post by HansV »

Since the first column of ws2 is blank, it disappears when you save wb2.
This should avoid it:

Code: Select all

Sub CopyColumn()
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim m As Long
    Application.ScreenUpdating = False
    Set wb1 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\1.xls")
    Set wb2 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\Alert..csv")
    Set ws1 = wb1.Worksheets.Item(1)
    Set ws2 = wb2.Worksheets.Item(1)
    With ws1
        m = .Range("I" & .Rows.Count).End(xlUp).Row
        .Range("I2:I" & m).Copy ws2.Range("B1")
    End With
    ws2.Range("A1:A" & m - 1).Value = " "
    wb2.Close SaveChanges:=True
    wb1.Close SaveChanges:=False
    Application.ScreenUpdating = True
End Sub
Best wishes,
Hans

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

Re: vba code correction

Post by zyxw1234 »

Ths code is not giving the desired result
plz run the macro and see the result HansV Sir
i need the result in column B of ws2 not in column A of ws2

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

Re: vba code correction

Post by HansV »

Is this better?

Code: Select all

Sub CopyColumn()
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim m As Long
    Application.ScreenUpdating = False
    Set wb1 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\1.xls")
    Set wb2 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\Alert..csv")
    Set ws1 = wb1.Worksheets.Item(1)
    Set ws2 = wb2.Worksheets.Item(1)
    With ws1
        m = .Range("I" & .Rows.Count).End(xlUp).Row
        .Range("I2:I" & m).Copy ws2.Range("B1")
    End With
    ws2.Range("A1:A" & m - 1).Value = Chr(160)
    wb2.Close SaveChanges:=True
    wb1.Close SaveChanges:=False
    Application.ScreenUpdating = True
End Sub
Best wishes,
Hans

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

Re: vba code correction

Post by zyxw1234 »

No Doubt HansV Sir
Probelm Solved
Thnx for ur Great Help in solving this problem Sir