Macro to copy data under matching column header

Closed
nsabree6 Posts 3 Registration date Sunday January 13, 2013 Status Member Last seen January 13, 2013 - Jan 13, 2013 at 08:04 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jan 13, 2013 at 09:47 AM
I am trying to write a macro to copy data from multiple spreadsheets and get the data to paste into the proper matching headers. Here is what I have so far. It copies over, but then all of the data disappears except what was copied into the first column.

Sub Macro2()

Dim wks As Worksheet
Dim varValue1 As String
Dim varFind1 As Integer
Dim varCount1 As Integer
Dim varCount2 As Integer
Dim varRow As Integer
Dim varCol As Integer
Dim varColMark As Integer
Dim z As Integer
z = 2


For Each wks In Worksheets
If Left(wks.Name, 2) = "AL" Then
Else
GoTo skip1
End If
varCount1 = wks.Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
varCount2 = wks.Range("4:4").Cells.SpecialCells(xlCellTypeConstants).Count
For varCol = 1 To varCount2
For x = 1 To 27
If Worksheets("data").Cells(1, x).Value = wks.Cells(4, varCol).Value Then
varColMark = x
'Debug.Print varColMark, Worksheets("data").Cells(1, x).Value

End If
'Debug.Print wks.Cells(4, varCol).Value
Next x
For varRow = 4 To varCount1 + 3
If varCol = 1 Then
Worksheets("data").Cells(z, varColMark).Value = wks.Cells(varRow + 4, varCol)
'Debug.Print wks.Cells(varRow + 4, varCol)

z = z + 1
Else
For h = z - varCount1 To z
Worksheets("data").Cells(h, varColMark).Value = wks.Cells(varRow + 4, varCol)
Next h
End If

'Debug.Print wks.Name; " "; z; " "; " "; varRow; " "; varCol; " - "; wks.Cells(varRow + 4, varCol)



Next varRow




Next varCol

skip1:
Next wks

End Sub
Sub FindRightColumn()

Dim varValue1 As String

Dim varFind1 As Integer
'Worksheets("AL-JJ-enrollment-2009").Cells(i + 4, 1)
varValue1 = Worksheets("AL-JJ-enrollment-2009").Cells(4, 16)
'Debug.Print varValue1
For x = 1 To 27
If Worksheets("data").Cells(1, x).Value = varValue1 Then
MsgBox x

End If


Next x



End Sub

1 reply

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jan 13, 2013 at 08:46 AM
could you please post a sample workbook at some public file share site like speedyshare.com and post back link to the actual file back here
0
nsabree6 Posts 3 Registration date Sunday January 13, 2013 Status Member Last seen January 13, 2013
Jan 13, 2013 at 08:52 AM
http://speedy.sh/sMd2C/AL-JJ-DATA-2009-1.xlsm
0
nsabree6 Posts 3 Registration date Sunday January 13, 2013 Status Member Last seen January 13, 2013
Jan 13, 2013 at 09:45 AM
http://speedy.sh/sMd2C/AL-JJ-DATA-2009-1.xlsm
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jan 13, 2013 at 09:47 AM
a bit confuse over your macro. Are you trying to do this

1. loop thru each sheet
1.1. find the last used row on data sheet
1.2 copy each column starting from row 5 till last row into appropriate column in sheet "data" after the last used column as determined in step 1.1
?
0