Need help with VBA code

Topic

New Reply

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

Reply | Quote

Replies

Hi
Your explanation is not very clear.
Can you provide a sample file with a better explanation of what you are trying to do?

Reply | Quote

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:UsersMizpahDocumentsShekira” Then
strLoc = “C:UsersMizpahDocumentsShekiraReport”
Else
strLoc = “C:UsersMizpahDocumentsShekiraReport”
End If

Set wbReport = Workbooks.Add

wbReport.Colors = wbMaster.Colors ‘ copies the colors

wbMaster.Worksheets(“DP”).Copy After:=wbReport.Worksheets(wbReport.Worksheets.Count) ‘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.Count) ‘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.Count) ‘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

Reply | Quote

Hi

Upload your file

Reply | Quote

I think this is the link:

http://www.4shared.com/file/jZ6eJJTJ/Example.html

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Here is my file,

Reply | Quote

Hi

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

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

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

Reply | Quote

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

Reply | Quote

I am noticing there are variable that are not declared and the program keeps stoping at those saying can`t find object or library

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Hi

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

zeddy

Reply | Quote

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….

Reply | Quote