+ Reply to Thread
Results 1 to 2 of 2

Data Entry from one excel workbook to several excel work sheets "Else without if" Error

  1. #1
    Registered User
    Join Date
    06-05-2014
    Posts
    8

    Question Data Entry from one excel workbook to several excel work sheets "Else without if" Error

    Dear Friends,
    I need your expertise in the VBA coding for data entry from one excel workbook to several excel work sheets. I tried with some coding to transfer the Data, it worked fine in sheet 1, but when i tried to make it to select and transfer data to different sheets, it is showing error. I tried several time re-writing this code with If function. none of them works, it always showing error. Here i attached the Workbook mydata2.xlsm as a main Data Entry sheet and when i click the command button it have to register in another excel workbook named postings2.xlsx. Every time while i Run the Program i'm getting "Else without if" Error. please let me know what wrong in my coding.

    Dim itemprice As String
    Dim itemname As String
    Dim partname As String
    Dim car As String
    Dim mydata As Workbook

    Worksheets("sheet1").Select
    itemname = Range("B1")
    Worksheets("sheet1").Select
    itemprice = Range("B2")
    Worksheets("sheet1").Select
    partname = Range("B3")
    Worksheets("sheet1").Select
    car = Range("B4")

    If car = "Z01" Then
    Set mydata = Workbooks.Open("C:\Users\RAJ TAR\Desktop\projects\postings2.xlsx")
    Worksheets("Z01").Select
    Worksheets("Z01").Range("a1").Select
    RowCount = Worksheets("Z01").Range("A1").CurrentRegion.Rows.Count
    With Worksheets("Z01").Range("A1")
    .Offset(RowCount, 0) = itemname
    .Offset(RowCount, 1) = itemprice
    .Offset(RowCount, 2) = partname
    .Offset(RowCount, 3) = car

    ElseIf car = "Z02" Then
    Set mydata = Workbooks.Open("C:\Users\RAJ TAR\Desktop\projects\postings2.xlsx")
    Worksheets("Z02").Select
    Worksheets("Z02").Range("a1").Select
    RowCount = Worksheets("Z02").Range("A1").CurrentRegion.Rows.Count
    With Worksheets("Z02").Range("A1")
    .Offset(RowCount, 0) = itemname
    .Offset(RowCount, 1) = itemprice
    .Offset(RowCount, 2) = partname
    .Offset(RowCount, 3) = car

    Else
    Set mydata = Workbooks.Open("C:\Users\RAJ TAR\Desktop\projects\postings2.xlsx")
    Worksheets("Z03").Select
    Worksheets("Z03").Range("a1").Select
    RowCount = Worksheets("Z03").Range("A1").CurrentRegion.Rows.Count
    With Worksheets("Z03").Range("A1")
    .Offset(RowCount, 0) = itemname
    .Offset(RowCount, 1) = itemprice
    .Offset(RowCount, 2) = partname
    .Offset(RowCount, 3) = car
    End If
    End With
    mydata.Save
    Workbooks("postings2.xlsx").Save
    Workbooks("postings2.xlsx").Close
    Range("B1:B4").ClearContents
    End Sub

    Thank you in Advance.

    Regards,
    Raj TAR
    Attached Files Attached Files
    Last edited by rajtar; 01-12-2016 at 10:48 AM. Reason: to improve

  2. #2
    Registered User
    Join Date
    06-05-2014
    Posts
    8

    Re: Data Entry from one excel workbook to several excel work sheets "Else without if" Erro

    Hi Friends,
    I have found what was the Problem, just i didn't ended the With function, just by adding End With for every loop, it works perfectly fine.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Copy data from multiple sheets in workbook to different work book but on one worksheet.
    By dwhite30518 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-23-2014, 11:31 PM
  2. Replies: 0
    Last Post: 07-02-2014, 04:13 PM
  3. [SOLVED] auto save from outlook to excel workbook and multiple work sheets depending on date
    By namluke in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-22-2013, 06:18 AM
  4. Need A data entry form to save the data in several work sheets
    By nilesh_urkude in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-01-2013, 12:25 AM
  5. Replies: 0
    Last Post: 06-27-2010, 04:10 PM
  6. Replies: 3
    Last Post: 11-27-2009, 02:12 AM
  7. data entry in excel by linking to other word doc. or excel sheets
    By wangxuqin in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-14-2006, 07:40 AM

Tags for this Thread

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