Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 2
Default Using VBA to copy data into another worksheet ???

I have an Excel workbook with 5 worksheets (individuals fill out their appropriate worksheet columns A to AW). There is a Totals worksheet in the workbook that has a command click update button. The update button starts with the 1st individual worksheet and using column A (until column A is empty) copies the information onto the Totals worksheet....it then moves to the next individual worksheet and does the same thing. The problem is that it appears vba will only copy data from column A to Z, what do I need to do in order to pick up the remaining 23 columns that contain data?
  #3   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 2
Default Using VBA to copy data into another worksheet ???

On Monday, May 20, 2013 3:19:16 PM UTC-4, wrote:
I have an Excel workbook with 5 worksheets (individuals fill out their appropriate worksheet columns A to AW). There is a Totals worksheet in the workbook that has a command click update button. The update button starts with the 1st individual worksheet and using column A (until column A is empty) copies the information onto the Totals worksheet....it then moves to the next individual worksheet and does the same thing. The problem is that it appears vba will only copy data from column A to Z, what do I need to do in order to pick up the remaining 23 columns that contain data?


That seems to work if I specify the ending row, which is unknown. My code is:
Private Sub cmdUpdate_Click()
Dim w As String
Dim x As String
Dim y As String
Dim z As String
Dim lCount As Long
Dim lresult As Long
Dim lcellrangenumeric As Long
Dim lColIndex As Long
Dim lSheetIndex As Integer

With Worksheets("Total")

lCount = 3 ' start row value
While Len(.Range("A" & CStr(lCount))) 0
For lColIndex = Asc("A") To Asc("AW") ' column value
.Range(Chr(lColIndex) & CStr(lCount)).Value = ""
Next lColIndex
lCount = lCount + 1 'next row
Wend
End With

For Each ws In Worksheets
With ws
If InStr(ws.Name, ".") 0 Then ' does this worksheet contain someone's name?
' transfer data
lCount = 2 ' start row value
While Len(.Range("A" & CStr(lCount))) 0
lresult = FindnextAvail ' first available row in the Totals sheet
For lColIndex = Asc("A") To Asc("AW") ' column value
Worksheets("Total").Range(Chr(lColIndex) & CStr(lresult)).Value = .Range(Chr(lColIndex) & CStr(lCount)).Value
Next lColIndex
lCount = lCount + 1 'next row
Wend

End If
End With
Next ws

End Sub

Private Function FindnextAvail()
Dim lCount As Long

With Worksheets("Total")
lCount = 3 ' start row value
While Len(.Range("A" & CStr(lCount))) 0
lCount = lCount + 1 'next row
Wend
End With

FindnextAvail = lCount
End Function
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
copy/pasting data into a worksheet and then sorting worksheet Pierrette T[_2_] Excel Programming 0 November 11th 09 04:23 AM
Copy data from one worksheet and paste into another worksheet stockton12 Excel Programming 3 March 30th 08 09:40 PM
Copy data in multiple worksheet tabs into one worksheet Bob Excel Programming 2 February 15th 08 04:01 PM
copy from B worksheet to A worksheet with NO repeated data tikchye_oldLearner57 Excel Discussion (Misc queries) 1 September 29th 06 06:56 PM
copy data in a cell from worksheet A to worksheet B rajesh Excel Discussion (Misc queries) 1 February 21st 06 08:40 AM


All times are GMT +1. The time now is 11:35 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"