J
Jason Head
Hi, Here is my dillemma (I'm a VBA newbie):
I have created a macro using VBA that takes raw data from an external excel
spreadsheet in the following form
Mark 28
Mark 32
Jim 13
Jim 57
Jim 84
Elliot 15
Jason 48
Jason 48
Jason 90
Jason 85
and transforms it into the following format into a new spreadsheet
Mark 28
Mark 32
Jim 13
Jim 57
Jim 84
Elliot 15
Jason 48
Jason 48
Jason 90
Jason 85
What I want the macro to then do is to go through and differentiate each
range from each other, then sum the values in each range in the cells
directly below each range. I need to use a macro because size and number of
ranges will vary. How do I program a macro so that it will go to the first
empty cell below each range and sum the values for that range?
Any help is appreciated,
Jason
I used the following programming to get the raw data into the format above:
Sub Import_Job_Log()
Application.DisplayAlerts = False
Application.Goto reference:="job_log_loc"
Selection.CurrentRegion.Select
Selection.Clear
Application.Goto reference:="job_log_loc"
mywindow = ActiveWorkbook.Name
myfile = Application.GetOpenFilename("microsoft excel files,*.xls")
Workbooks.Open Filename:=myfile
Selection.CurrentRegion.Select
Selection.Copy
myworkbook = ActiveWorkbook.Name
Windows(mywindow).Activate
ActiveSheet.Paste
Windows(myworkbook).Activate
ActiveWindow.Close
Application.Goto reference:="format"
Selection.EntireRow.Hidden = False
Selection.End(xlUp).Select
Range("E3").Select
Do Until ActiveCell = "stop"
If ActiveCell = ActiveCell.Offset(1, 0) = False Then
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
End If
ActiveCell.Offset(1, 0).Select
Loop
I have created a macro using VBA that takes raw data from an external excel
spreadsheet in the following form
Mark 28
Mark 32
Jim 13
Jim 57
Jim 84
Elliot 15
Jason 48
Jason 48
Jason 90
Jason 85
and transforms it into the following format into a new spreadsheet
Mark 28
Mark 32
Jim 13
Jim 57
Jim 84
Elliot 15
Jason 48
Jason 48
Jason 90
Jason 85
What I want the macro to then do is to go through and differentiate each
range from each other, then sum the values in each range in the cells
directly below each range. I need to use a macro because size and number of
ranges will vary. How do I program a macro so that it will go to the first
empty cell below each range and sum the values for that range?
Any help is appreciated,
Jason
I used the following programming to get the raw data into the format above:
Sub Import_Job_Log()
Application.DisplayAlerts = False
Application.Goto reference:="job_log_loc"
Selection.CurrentRegion.Select
Selection.Clear
Application.Goto reference:="job_log_loc"
mywindow = ActiveWorkbook.Name
myfile = Application.GetOpenFilename("microsoft excel files,*.xls")
Workbooks.Open Filename:=myfile
Selection.CurrentRegion.Select
Selection.Copy
myworkbook = ActiveWorkbook.Name
Windows(mywindow).Activate
ActiveSheet.Paste
Windows(myworkbook).Activate
ActiveWindow.Close
Application.Goto reference:="format"
Selection.EntireRow.Hidden = False
Selection.End(xlUp).Select
Range("E3").Select
Do Until ActiveCell = "stop"
If ActiveCell = ActiveCell.Offset(1, 0) = False Then
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
End If
ActiveCell.Offset(1, 0).Select
Loop