Results 1 to 15 of 15
  1. #1
    Lounger
    Join Date
    May 2012
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need help with VBA code

    Hi, can someone please help....

    I would like to create code that take data from one place and pastes it into a cell then sheet 1 is created in a workbook, then move to next row copy and paste data and sheet 2 is created, it will loop through the list and if there is a number there then sheet 2 is created again and added to the new workbook, then before moving to a new column it closes the new workbook and the process is started again.

    Here is the code I have so far to help with the looping

    Sub Pull()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lc As Long, lr As Long, MyCol As Long, MyRow As Long
    Dim MyAddress As String

    Set ws1 = Sheets("DealerNameRun")
    Set ws2 = Sheets("SalesCalc")

    lc = ws1.Cells(1, Columns.Count).End(xlToLeft).Column

    For MyCol = 1 To lc
    MyAddress = "F68"
    lr = ws1.Cells(Rows.Count, MyCol).End(xlUp).Row
    For MyRow = 1 To lr

    'To put only the value of the cell to D27
    ws2.Range(MyAddress).Value = ws1.Cells(MyRow, MyCol).Value

    'If you want to copy the whole cell's contents to D27
    'ws1.Cells(MyRow, MyCol).Copy Destination:=ws2.Range("D27")


    MyAddress = "F136"

    Next MyRow


    Next MyCol

    End Sub

  2. #2
    Lounger
    Join Date
    Apr 2012
    Location
    Abergavenny, Wales, UK
    Posts
    25
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Hi
    Your explanation is not very clear.
    Can you provide a sample file with a better explanation of what you are trying to do?
    Regards
    Roger Govier
    Microsoft Excel MVP

  3. #3
    Lounger
    Join Date
    May 2012
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    So far it would look at a sheet called DealerNameRun pull the value in from A1 and place on SalesCal sheet F68 then it should copy the sheet called DP and SM then break all links and open into a new workbook, then move to A2 and place that value to SalesCal F136 and copy sheet SC break all links and place in workbook, then move to the next row A3 and place that value to SalesCal F136 and copy sheet SC break all links and place in workbook, this continues until there is no more data in column A. Close and save workbook.

    Then it would move to the next column (B) in DealerNameRun pull the value in from B1 and place on SalesCal F68 then it should copy the sheet called DP and SM then break all links and open into a new workbook, then move to B2 and place that value to SalesCal F136 and copy sheet SC break all links and place in workbook, then move to the next row A3 and place that value to SalesCal F136 and copy sheet SC break all links and place in workbook, this continues until there is no more data in column C. Close and save workbook.
    and continue until there is no more data in the columns

    Below is the code I have so far however, it is not pulling the sheet DP and SM according to the data in row 1


    Sub A_Startup()

    'Dim rs As Recordset 'this holds our spinner that we created
    Dim Active As String
    Dim i As Integer
    Dim strDir As String
    Dim strLoc As String
    Dim RowCount As Long ' to count records so that you can run batches of 50 or 100 etc..
    Dim strSpinner As String
    Dim strIndustry As String
    Dim strSections As String
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lc As Long, lr As Long, MyCol As Long, MyRow As Long
    Dim MyAddress As String



    Application.ScreenUpdating = True 'this allows the screen not to be updated and speeds up the action of the code, you can see your code as it is working
    Application.DisplayAlerts = False 'turn displays/warning off


    Set ws1 = Sheets("DealerNameRun")
    Set ws2 = Sheets("SalesCalc")

    lc = ws1.Cells(1, Columns.Count).End(xlToLeft).Column

    For MyCol = 1 To lc
    MyAddress = "F68"

    Set wbMaster = ActiveWorkbook 'set the active workbook as the master product advisor workbook

    If ActiveWorkbook.Path = "C:\Users\Mizpah\Documents\Shekira\" Then
    strLoc = "C:\Users\Mizpah\Documents\Shekira\Report\"
    Else
    strLoc = "C:\Users\Mizpah\Documents\Shekira\Report\"
    End If

    Set wbReport = Workbooks.Add

    wbReport.Colors = wbMaster.Colors ' copies the colors

    wbMaster.Worksheets("DP").Copy After:=wbReport.Worksheets(wbReport.Worksheets.Cou nt) 'copies the pages
    Call brLinks
    Cells.Copy 'COPY ALL THE CELLS IN A WORKBOOK
    Cells.PasteSpecial xlPasteValues 'so it breaks the links

    wbMaster.Worksheets("SM").Copy After:=wbReport.Worksheets(wbReport.Worksheets.Cou nt) 'copies the pages
    Call brLinks
    Cells.Copy 'COPY ALL THE CELLS IN A WORKBOOK
    Cells.PasteSpecial xlPasteValues 'so it breaks the links

    lr = ws1.Cells(Rows.Count, MyCol).End(xlUp).Row
    For MyRow = 1 To lr

    'To put only the value of the cell to D27
    ws2.Range(MyAddress).Value = ws1.Cells(MyRow, MyCol).Value


    MyAddress = "F136"


    wbMaster.Worksheets("SC").Copy After:=wbReport.Worksheets(wbReport.Worksheets.Cou nt) 'copies the pages
    Call brLinks
    Cells.Copy 'COPY ALL THE CELLS IN A WORKBOOK
    Cells.PasteSpecial xlPasteValues 'so it breaks the links

    Next MyRow


    For i = 1 To Application.SheetsInNewWorkbook ' deletes the first default sheets at the front of the workbook
    wbReport.Worksheets("Sheet" & i).Delete
    Next i


    ' ===== this section saves as Excel in the correct directory which is created by CreateDir subroutine ==========

    strDir = strLoc & "Reports\"
    Call CreateDir(strDir) ' this Function is located in ModF_Functions and is a standard function to create directories when they don't already exist
    wbReport.SaveAs strDir & "Dealer Reports" & "_" & Dealer & ".xls"


    Cells.Copy 'COPY ALL THE CELLS IN A WORKBOOK
    Cells.PasteSpecial xlPasteValues 'so it breaks the links

    '==== this is the print-to-pdf section of code ====

    'wbReport.PrintOut , , , , "Adobe PDF"


    wbReport.Close True 'close active workbook


    RowCount = RowCount + 1

    Next MyCol

    Application.ScreenUpdating = True 'this allows the page to be updated
    Application.DisplayAlerts = True 'turn displays on

    End Sub

  4. #4
    Lounger
    Join Date
    Apr 2012
    Location
    Abergavenny, Wales, UK
    Posts
    25
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Hi

    Upload your file
    Regards
    Roger Govier
    Microsoft Excel MVP

  5. #5
    Lounger
    Join Date
    May 2012
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am not sure how to upload the file, I found information on a website called 4shared.com and this is the link it gave me to share.

    <iframe marginWidth="0" marginHeight="0" src="http://www.4shared.com/folder/Zp-6fNDT/_online.html?dirembed=true" frameBorder="0" allowtransparency="true" width="840" height="600"></iframe>

  6. #6
    Lounger
    Join Date
    May 2012
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi

    To upload a file here, when you click the Reply option, look underneath the reply panel where you type your reply and click the button labelled [Go Advanced].
    It will give you an 'attachement' option button which will allow you to [Browse] your computer to click on the file to upload, and an [upload] button to attach it to your reply.
    You can [Close this Window] after you attach your file, then, after typing your reply, click the [Submit reply] button.

    I think that's how it works.

    zeddy

  8. #8
    Lounger
    Join Date
    May 2012
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here is my file,
    Attached Files Attached Files

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi

    Copy my attached file into any folder of your choice, e.g.
    C:\Users\Richard\

    Make sure you have a folder called Report within your selected location, e.g.
    C:\Users\Richard\Report

    Now open my attached file from your saved location, and then run the VBA routine A_Startup

    The system will process the first 4 Stores for you, creating the Excel file and PDF file for each of these stores.

    To process all 140 stores, simply delete the empty columns I inserted at col E and col F.

    I looked at your VBA code and made a few changes.
    My code gives you a progress message in the bottom statusbar, so I turned OFF the screen updating.
    You can amend the code to turn screen updating back on if required.

    I noticed that you didn't have a sheet named [SM] in the example file you sent, so I added a dummy sheet with that name.
    You will need to adjust that as required.

    Also, to make it easier to understand, I added a range name of SalesConsultantCode for cell [F136] on your [SalesCalc] sheet.

    Your code referred to..
    "Dealer Principal" & "_" & ActiveSheet.Range("AJ3").Value
    ..but I couldn't find anything that this related to.

    If you have any questions, please ask.

    zeddy
    Attached Files Attached Files

  10. #10
    Lounger
    Join Date
    May 2012
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have done show however there is a line of code that is producing an complie error: can`t find object or library

    zLastCol = [a1].CurrentRegion.Columns.Count

    it is highlighting the a1

  11. #11
    Lounger
    Join Date
    May 2012
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am noticing there are variable that are not declared and the program keeps stoping at those saying can`t find object or library

  12. #12
    Lounger
    Join Date
    May 2012
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am having troble opening your file is this a 2010 it then asks me to open with Excel 2010 and when I do I am not seeing where you declared the variables so I am getting a error can`t find object or library

  13. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi

    The file I attached is Excel2010 .xlsm format.
    I started with your Example.xlsm file, renamed it, and modified the VBA code.
    When I look at the Tools > References , in the VBA window, it shows 4 references are checked.
    This includes Microsoft Excel 14.0 Object Library.
    Are you using Excel 2010??? Or are you using compatibility mode???

    Also, I turned off Option Explicit at the top of the module (by placing a comment ' character in front) as I prefer to program as I go (rather than have to declare all variables first).

    I tested my attached file with Excel 2010 and it works fine on my system.

    zeddy

  14. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi

    I loaded my file [rz-rudevincy-Example1.xlsm] into Excel2007 and it also works as expected.

    zeddy

  15. #15
    Lounger
    Join Date
    May 2012
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you so much it did work as I was going through the code I noticed you did not declare the variables... did not knoe about turning off Option Explicit , at that time my computer was just acting weird....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •