UpToDate Data

tarun@1964
NewLounger
Posts: 12
Joined: 10 Jun 2022, 09:22
Location: KOLKATA , WEST BENGAL, INDIA

UpToDate Data

Post by tarun@1964 »

Hi, respected "ADMINISTRATORS",'MODERATOR' and FORUM members , i have A problem to transfer data from a spreadsheet to another spreadsheet
by using VBA as stated below for 'Module2'.

Code: Select all

Sub UpToDate_Data()

Dim ws As Worksheet
Dim wt As Worksheet
Dim s As Long
Dim t As Long
Dim r As Long

Application.ScreenUpdating = False

Set ws = Worksheets("ItemList")
Set wt = Worksheets("AvilItem")

' Find first empty row in columns A:R on sheet AVILITEM
t = wt.Range("A:R").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

' Copy Item Code and Item Name
For s = 1 To 25
wt.Cells(t, 2 * s + 2).Value = ws.Range("b" & s + 2).Value
wt.Cells(t, 2 * s + 3).Value = ws.Range("c" & s + 2).Value

Next s

Application.ScreenUpdating = True
End Sub
But it is not working .i think there must be fatal mistake i have made.here i am attaching copy of work book. Hope i will confirmly get YOUR assistance to solve my problem.
YOURS Sincerely
Tarun@1964
You do not have the required permissions to view the files attached to this post.

User avatar
Leif
Administrator
Posts: 7193
Joined: 15 Jan 2010, 22:52
Location: Middle of England

Re: UpToDate Data

Post by Leif »

I have edited the above post - it is helpful to other members if you can enclose any code within 'code' tags.
In the post entry screen, select your code and then click on the button with </> on it. (Note that this preserves any indentation and formatting you may have included.)
Leif

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

Re: UpToDate Data

Post by HansV »

Please provide more details about what you're trying to accomplish. The AVILITEM sheet does not appear to be suitable for the code.
Best wishes,
Hans

tarun@1964
NewLounger
Posts: 12
Joined: 10 Jun 2022, 09:22
Location: KOLKATA , WEST BENGAL, INDIA

Re: UpToDate Data

Post by tarun@1964 »

Respected HansV ,
Actually i want to transfer data of B2 and C2 towards Down as long from 'ITEM LIST' Sheet to A2 and B2 towards down as long to 'AVILITEM' Sheet without any repeataion, means if found ITEM CODE and ITEM NAME in 'ITEM LIST' Sheet and 'AVILITEM' Sheet are same then not necessory to transfer data despite of clicking button.

Yours Sincerely
Tarun@1964

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

Re: UpToDate Data

Post by HansV »

For example:

Code: Select all

Sub UpToDate_Data()
    Dim ws As Worksheet
    Dim wt As Worksheet
    Dim t As Long
    Dim m As Long

    Application.ScreenUpdating = False

    Set ws = Worksheets("Item List")
    ' Find last used row in columns B:C on ITEM LIST
    m = ws.Range("A:B").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set wt = Worksheets("AvilItem")
    ' Find first empty row in columns A:R on sheet AVILITEM
    t = wt.Range("A:R").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

    ' Copy Item Code and Item Name
    ws.Range("B2:C" & m).Copy Destination:=wt.Range("A" & t)
    ' Remove duplicates
    wt.UsedRange.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes

    Application.ScreenUpdating = True
End Sub
Best wishes,
Hans

tarun@1964
NewLounger
Posts: 12
Joined: 10 Jun 2022, 09:22
Location: KOLKATA , WEST BENGAL, INDIA

Re: UpToDate Data

Post by tarun@1964 »

Respected HansVogelaar Sir,
Thank YOU for YOUR help. i have followed YOUR Code, but unfortunately it is not working properly. displaying
Run - time error '9':Subscript out of range. debug "Set wt = Worksheets("AvilItem")"
what to do now.

YOURS Sincerely
Tarun@1964
You do not have the required permissions to view the files attached to this post.

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: UpToDate Data

Post by StuartR »

That error suggests that you don't have a worksheet called AvilItem
Please check the spelling, and whether there is a trailing space or some other difference

Ah, I just looked at your workbook and the worksheet name is "AVIL ITEM" not "AvilItem" Note the space in the middle as well as the upper case
StuartR


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

Re: UpToDate Data

Post by HansV »

The workbook that you attached to the first post in this topic has AVILITEM; the one in your later reply has AVIL ITEM, as StuartR pointed out.

S1507.png

So you should change Worksheets("AvilItem") to Worksheets("Avil Item")
(Upper case/low case does not matter in sheet names)
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

tarun@1964
NewLounger
Posts: 12
Joined: 10 Jun 2022, 09:22
Location: KOLKATA , WEST BENGAL, INDIA

Re: UpToDate Data

Post by tarun@1964 »

Oh! excellent it is working properly , Respected Mr. Hans Vogelaar(Administrator) Sir, and Mr. StuartR(Administrator) Sir,i share my gratitude with both of YOU. actually i am also over look the matter which YOU have identified to find out my mistake, a lot of thanks to both of YOU. i am indebted to YOU and every FORUM MEMBERS of EILEENSLOUNGE in many ways.
again many thanks to YOU.

YOURS Sincerely
Tarun@1964