Copying a variable-size range to a different worksheet

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:
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Try using
Code:
Dim LastCell as Long
LastCell = Range("N1:N65536").End(xlUp).Row
Range(Cells(1, 14), Cells(LastCell, 14)).Select
Here is a way to select your range through the end of your list in column N.

Hope it helps
 
Upvote 0
I noticed that you said that you were working in column 14, but later you said you were working in column 1 row 14.
If that is the case then switch the 1 and the 14 in the cells reference.
 
Upvote 0
Try using
Code:
Dim LastCell as Long
LastCell = Range("N1:N65536").End(xlUp).Row
Range(Cells(1, 14), Cells(LastCell, 14)).Select
Here is a way to select your range through the end of your list in column N.

Hope it helps
Hi MPW,

Would the following modification to your code be acceptable?
Code:
Dim LastCell As Range
Set LastCell = Range("N" & Rows.Count).End(xlUp)
Range(Cells(1, "N"), LastCell).Select
 
Upvote 0
I noticed that you said that you were working in column 14, but later you said you were working in column 1 row 14.
If that is the case then switch the 1 and the 14 in the cells reference.

Sorry MPW,

If i said column 1 and row 14 i meant to say row 1 and columns 14.

I know my code is a mess, but i've been on the trial and error with this code. and i am lost close to the to give up.

i tried to debug and NOTHING that i wanted it to do is working as it should. thats why im in need of a major help
 
Upvote 0
Actually if you wanted to write it as a range it could be done like this.
Code:
Dim LastCell as Long
Dim VarRange as Range

LastCell = Range("N1:N65536").End(xlUp).Row
VarRange = "N1:N" & LastCell & ""

Range(VarRange).Select
The way that LastCell is being used is to just trap the Row ref as a number.
 
Upvote 0
MPW do you have any better way to activate the sheet where the data is located? bc when im trying to run it, it is not doing it, it is supposed to shift back and forth between sheets btu i am far from getting it to run like that.

Is this right?
Code:
For Each WkSh In Worksheets
       If WkSh.Name = strSheetName Then
          WkSh.Activate
       End If
    Next
 
Upvote 0
You can just select it by it's name.
Code:
Sheets("Sheet1").Select
Also if you are using multiple Sheets you should use the sheet name when selecting and/or pasting ranges.
For example:
Code:
Sheets("Sheet1").Range("A1").Select
If you refer to you ranges this way you do not need to refocus sheets to copy data.
Code:
Sheets("Sheet1").Range("A2") = Sheets("Sheet1").Range("A1")
 
Upvote 0

Forum statistics

Threads
1,224,559
Messages
6,179,515
Members
452,921
Latest member
BBQKING

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top