L
L. Howard
Using three inputboxes this code works okay. Takes a column from sheet 1 and makes shorter columns on sheet 2
I would like to do two things.
1. Combine all three entries to a single inputbox, say comma delimited.
2. When referring to the source and destination columns use a letter. (Code below uses a letter for Source and a "number - 1" for Destination column.)
Then split the inputboxes entries to the variables.
Where an inputbox entry would look like 10,D,F would be:
10 rows at a time from column D on source sheet column, post to destination sheet starting at column F and to the right. If last column is not a full 10 rows that is ok.
Thanks.
Howard
Sub ColumnToColumns()
Dim wks1 As Worksheet, wks2 As Worksheet
Dim iColumn As Integer
Dim lLast As Long
Dim i As Long, j As Long ', y As Long
Dim DestCol As String
Dim nRows As String
Dim nnCols As String
Dim nCols As String
nRows = InputBox("Colum No. of Rows.", "Enter value")
If nRows = vbNullString Then Exit Sub
nCols = InputBox("Colum A to Column ?", "Enter Column Letter")
If nCols = vbNullString Then Exit Sub
DestCol = Application.InputBox("Destination Colum 1 = A, 26 = Z ?", "Enter Destination Column number", , , , , , 1)
If DestCol = vbNullString Then Exit Sub
nnCols = nRows - 1
Application.ScreenUpdating = False
Set wks1 = Worksheets("Sheet2") '/ Rename to your workbook
Set wks2 = Worksheets("Sheet3") '/ Rename to your workbook
lLast = wks1.Cells(wks1.Rows.Count, 1).End(xlUp).Row
j = wks2.Cells(1, wks2.Columns.Count).End(xlToLeft).Column + (DestCol - 1)
For iColumn = 1 To nRows
lLast = Application.Max(lLast, wks1.Cells(wks1.Rows.Count, _
iColumn).End(xlUp).Row)
Next iColumn
For i = 1 To lLast Step nRows
wks1.Range(nCols & i & ":" & nCols & i + nnCols).Copy wks2.Cells(1, j)
j = wks2.Cells(1, wks2.Columns.Count).End(xlToLeft).Column + 1
' To cut data to sheet 2
'wks1.Range(nCols & i & ":" & nCols & i + nnCols).Cut wks2.Cells(1, j)
'j = wks2.Cells(1, wks2.Columns.Count).End(xlToLeft).Column + 1
Next
wks2.Activate
Columns("A:Z").HorizontalAlignment = xlCenter
Application.Columns("A:Z").AutoFit
wks1.Activate
Set wks1 = Nothing
Set wks2 = Nothing
Application.ScreenUpdating = True
End Sub
I would like to do two things.
1. Combine all three entries to a single inputbox, say comma delimited.
2. When referring to the source and destination columns use a letter. (Code below uses a letter for Source and a "number - 1" for Destination column.)
Then split the inputboxes entries to the variables.
Where an inputbox entry would look like 10,D,F would be:
10 rows at a time from column D on source sheet column, post to destination sheet starting at column F and to the right. If last column is not a full 10 rows that is ok.
Thanks.
Howard
Sub ColumnToColumns()
Dim wks1 As Worksheet, wks2 As Worksheet
Dim iColumn As Integer
Dim lLast As Long
Dim i As Long, j As Long ', y As Long
Dim DestCol As String
Dim nRows As String
Dim nnCols As String
Dim nCols As String
nRows = InputBox("Colum No. of Rows.", "Enter value")
If nRows = vbNullString Then Exit Sub
nCols = InputBox("Colum A to Column ?", "Enter Column Letter")
If nCols = vbNullString Then Exit Sub
DestCol = Application.InputBox("Destination Colum 1 = A, 26 = Z ?", "Enter Destination Column number", , , , , , 1)
If DestCol = vbNullString Then Exit Sub
nnCols = nRows - 1
Application.ScreenUpdating = False
Set wks1 = Worksheets("Sheet2") '/ Rename to your workbook
Set wks2 = Worksheets("Sheet3") '/ Rename to your workbook
lLast = wks1.Cells(wks1.Rows.Count, 1).End(xlUp).Row
j = wks2.Cells(1, wks2.Columns.Count).End(xlToLeft).Column + (DestCol - 1)
For iColumn = 1 To nRows
lLast = Application.Max(lLast, wks1.Cells(wks1.Rows.Count, _
iColumn).End(xlUp).Row)
Next iColumn
For i = 1 To lLast Step nRows
wks1.Range(nCols & i & ":" & nCols & i + nnCols).Copy wks2.Cells(1, j)
j = wks2.Cells(1, wks2.Columns.Count).End(xlToLeft).Column + 1
' To cut data to sheet 2
'wks1.Range(nCols & i & ":" & nCols & i + nnCols).Cut wks2.Cells(1, j)
'j = wks2.Cells(1, wks2.Columns.Count).End(xlToLeft).Column + 1
Next
wks2.Activate
Columns("A:Z").HorizontalAlignment = xlCenter
Application.Columns("A:Z").AutoFit
wks1.Activate
Set wks1 = Nothing
Set wks2 = Nothing
Application.ScreenUpdating = True
End Sub