B.brazilian
New Member
- Joined
- Apr 22, 2011
- Messages
- 9
Hi guys,
I need some major help with this code. What I am trying to do is fairly simple, but it has been years since I programmed anything on VB and I am struggling big time here.
The idea is getting the data starting from a range (i need to skip all the headers and etc, so actually the real data starts at the column 14) and scan it down till it finds the last entry, and then copying the whole data to a different worksheet in a different workbook.
This is how i am trying to do it:
1 - i am running a loop to find how many "actual" data cells i have
2 - selecting and copying the data from the 1st worksheet
3 - clearing any data in the target worksheet before pasting the source data.
Here is the code:
PLEASE I NEED HELP WITH IT, I'M TO THE POINT WHERE EVERY STEP THAT I TAKE FORWARD IT FEELS LIKE I'M JUMPING BACK.
Thank a lot,
BR
I need some major help with this code. What I am trying to do is fairly simple, but it has been years since I programmed anything on VB and I am struggling big time here.
The idea is getting the data starting from a range (i need to skip all the headers and etc, so actually the real data starts at the column 14) and scan it down till it finds the last entry, and then copying the whole data to a different worksheet in a different workbook.
This is how i am trying to do it:
1 - i am running a loop to find how many "actual" data cells i have
2 - selecting and copying the data from the 1st worksheet
3 - clearing any data in the target worksheet before pasting the source data.
Here is the code:
Code:
Public strSheetName As String
Private Sub cmdCopynPaste_Click()
'SheetName = the name of the sheet where the data is you want to copy
Let strSheetName = "WorkBookTestRots.xls"
Dim WkSh As Worksheet
'Activate the sheet
For Each WkSh In Worksheets
If WkSh.Name = strSheetName Then
WkSh.Activate
End If
Next
'Variables
'Replace with the variables you will need for each data point (farm, strain, number of eggs)
' Dim Rows As Integer
Dim FARM As String
Dim STRAIN As String
Dim Flock As Integer
Dim AGE As Integer
Dim EGGS As Integer
Dim ROTS As Integer
STRAIN = ActiveSheet.Range("A13")
Flock = ActiveSheet.Range("B14")
FARM = ActiveSheet.Range("C13")
AGE = ActiveSheet.Range("D14")
EGGS = ActiveSheet.Range("E14")
ROTS = ActiveSheet.Range("AX14")
' Copy data to another worksheet
'On Error GoTo ErrHandler
Dim wbRotData As Excel.Workbook
Dim wsRotData As Excel.Worksheet
Dim FlockID As Integer
Dim Flock_Age As Integer
Dim Eggs_Set As Long
Dim Egg_Rot As Integer
' If you are copying a date, set the format (my date column was AX)
ThisWorkbook.Sheets(1).Range("AX6").NumberFormat = "mmddyyyy"
'Always clear the sheet where you’re moving the data
Workbooks("wbRotData").Activate
Worksheets("wsRotData").Activate
Worksheets("wsRotData").Range("A:AZ").ClearContents
'Set date format in sheet where I'm copying dates
wsRotData.Range("E1").NumberFormat = "mmddyyyy"
'Declare variables to hold the information you want to copy. You need one variable for each column.
'Where I have 20, you will replace that with a counter variable to store the number of rows of data in the worksheet
'for example, Dim intDataRowsCounter, Dim intMort(intDataRowsCounter)
Dim xDate As Date
Dim intStrain(20) As Integer
Dim intFLOCK(20) As Integer
Dim intFarm(20) As String
Dim intAGE(20) As Integer
Dim intEGGS(20) As Integer
Dim intROTS(20) As Integer
'Declare variable to store the rows where the data starts (not column headers or other info at the top)
Dim iStartCol As Integer
iStartCol = 14
Dim iStartRow As Integer
'Declare a variable for each column of data and assign the column number
Dim iStartStrainRow As Integer
iStartStrainRow = 1
Dim iStartFlockRow As Integer
iStartFlockRow = 2
Dim iStartFarmRow As String
iStartFarmRow = 3
Dim iStartAgeRow As Integer
iStartAgeRow = 4
Dim iStartEggsRow As Integer
iStartEggsRow = 5
Dim iStartRotsRow As Integer
iStartRotsRow = 50
Dim iStartStrainCol As Integer
iStartStrainCol = 2
Dim iStartFlockCol As Integer
iStartFlockCol = 2
Dim iStartFarmCol As Integer
iStartFarmCol = 2
Dim iStartAgeCol As Integer
iStartAgeCol = 2
Dim iStartEggsCol As Integer
iStartEggsCol = 2
Dim iStartRotsCol As Integer
iStartRotsCol = 2
'Declare row variable to write export rows and increment by 1 after each row is written
Dim iStartExportRow As Integer
iStartExportRow = 1
' Declare variables to store column numbers in the worksheet where you’re moving the data
Dim iStartExportStrainCol As Integer
iStartExportStrainCol = 1
Dim iStartExportFlockCol As Integer
iStartExportFlockCol = 1
Dim iStartExportFarmCol As Integer
iStartExportFarmCol = 1
Dim iStartExportAgeCol As Integer
iStartExportAgeCol = 1
Dim iStartExportEggsCol As Integer
iStartExportEggsCol = 1
Dim iStartExportRotsCol As Integer
iStartExportRotsCol = 1
Dim iCt As Integer
'*********Count Number of Rows with Data in Column A
Dim cell As Range
Dim count As Integer
count = 0
'Select Column A
Workbooks("WorkBookTestRots.xls").Activate
Worksheets("Sheet1").Activate
Columns("A:A").Select
'Replace any spaces so they don’t get counted as data rows
Selection.Replace What:=" ", Replacement:=""
'Select rows that contain data and count them:
Selection.SpecialCells(xlCellTypeConstants).Select
For Each cell In Selection
count = count + 1
Next cell
MsgBox count & " Entries found"
'Since I had 13 columns of “header” data in my worksheet, I want to start on Row 14. I also started on Column 1.
'Set the start position here:
iStartRow = 1
iStartCol = 14
'Reset all counters
iCt = 0
nI = 0
nC = 15
'Subtract any column headers or other fields that you do not want to include in the export
'intTotalHouses = count - 13
'Set the starting row for the export sheet
iStartExportRow = 1
'''Start Loop here
'nC = Number of rows that contain data. If you have 12 rows of data, nC, the value of nC should be 12. iCt will increment by 1 until = nC
For iCt = 0 To nC
' Workbooks("wbRotData").Active
' Worksheets("wsRotData").Activate
Workbooks("wbRotData").Worksheets("wsRotData").Activate
' End If
'*******************************Read Values************************************************
'Assign the data in the flock column to the variable strFlock.
'Uses Row (iStartHeaderRow) and Column (iStartFlockCol) to find data location.
'iStartHeaderRow will be incremented by 1 each time you pass
'through the loop, but iStartFlockCol is always the same. The same applies for strStrain, strFlock, etc.
strStrain = ActiveSheet.Cells(iStartStrainRow, iStartStrainCol)
strFlock = ActiveSheet.Cells(iStartFlockRow, iStartFlockCol)
strFarm = ActiveSheet.Cells(iStartFarmRow, iStartFarmCol)
strAge = ActiveSheet.Cells(iStartAgeRow, iStartAgeCol)
strEggs = ActiveSheet.Cells(iStartEggsRow, iStartEggsCol)
strRots = ActiveSheet.Cells(iStartRotsRow, iStartRotsCol)
'**************Copy Farm, Strain, Flock, Farm, Age, Eggs, and Rots to the export worksheet***************************
'wsExportToCsv is the export worksheet name.
'Set the starting row and column (should probably be 1 for row and column)
'You will increment the iStartExportRow after each pass through the loop,
'but again, the column positions will not change (e.g. Flock Column is always 1, Strain is always 2, etc)
wsRotData.Range(Cells(iStartExportRow, (iStartExportStrainCol)).Address, wsRotData.Cells(iStartExportRow, (iStartExportStrainCol))).Value = strStrain
' wsRotData.Range(Cells(iStartExportRow, ((iStartExportStrainCol)))).Value = strStrain
wsRotData.Range(Cells(iStartExportRow, iStartExportFlockCol).Address, wsRotData.Cells(iStartExportRow, (iStartExportFlockCol))).Value = strFlock
wsRotData.Range(Cells(iStartExportRow, iStartExportFarmCol).Address, wsRotData.Cells(iStartExportRow, (iStartExportFarmCol))).Value = strFarm
wsRotData.Range(Cells(iStartExportRow, iStartExportAgeCol).Address, wsRotData.Cells(iStartExportRow, (iStartExportAgeCol))).Value = strAge
wsRotData.Range(Cells(iStartExportRow, iStartExportEggsCol).Address, wsRotData.Cells(iStartExportRow, (iStartExportEggsCol))).Value = strEggs
wsRotData.Range(Cells(iStartExportRow, iStartExportRotsCol).Address, wsRotData.Cells(iStartExportRow, (iStartExportRotsCol))).Value = strRots
'After writting all the values to the export worksheet, I incremented the export row by 1 so that on the next pass,
'it writes to the next line
iStartExportRow = iStartExportRow + 1
'Increment the row by 1 on the source data sheet, so it reads the next row of data
iStartRow = iStartRow + 1
'Increment the row counter by 1—and start the loop over. When you reach the value of nC (the number of rows that contain data, you will exit the loop)
Next iCt
End Sub
PLEASE I NEED HELP WITH IT, I'M TO THE POINT WHERE EVERY STEP THAT I TAKE FORWARD IT FEELS LIKE I'M JUMPING BACK.
Thank a lot,
BR