Paste value in variable column

[msquared99]

I currently have a VB program that will copy and paste value data. What I want to be able to do is copy data and paste value it in a column that I choose.

I'll have several pieces of data to paste value. Example: I'll choose column E and data will paste value into rows 1 thru 9, then some data will paste value into rows 11 thru 20 and so on.

I am assuming some sort of loop will be used for the column selection?

Thanks for anyone's help and time.

[Guido Geurs]

Is it 1 value in different cells?
How do you enter the value of the columns? value? string? in a textbox with separators or in different textboxes?
You see we have multiple questions and solutions!

Please is it possible to attach in Bytes your code with some data (not all if it's to much).
If it's confidential data, enter some fictive values.
So we can see in which direction we must search for a solution.

[msquared99]

Guido,

Thanks for the response.

I have the spreadsheet but cannot copy it into the forum. If you like I can email it to?

I want to be able to take data from the "Weekly Summary" and paste value it into different rows of the "Data" spreadsheet. I need to be able to select the column as it changes each day.

Anyway, here is the code:

Sub Create_File()

ActiveWorkbook. Save
ActiveWorkbook. Save
ActiveSheet.Sha pes("Button 1").Select
Selection.OnAct ion = "Create_Fil e"
Range("L14").Se lect

Sheets("Weekly Summary").Selec t
Sheets("Weekly Summary").Cells .Select
Selection.Copy
Selection.Paste Special Paste:=xlValues , Operation:=xlNo ne, SkipBlanks:= _
False, Transpose:=Fals e
Sheets("Weekly Summary").Range ("A1").Selec t
Application.Cut CopyMode = False

Sheets("MTD Summary").Selec t
Sheets("MTD Summary").Cells .Select
Selection.Copy
Selection.Paste Special Paste:=xlValues , Operation:=xlNo ne, SkipBlanks:= _
False, Transpose:=Fals e
Sheets("MTD Summary").Range ("A1").Selec t
Application.Cut CopyMode = False

Sheets("Data"). Select
Sheets("Data"). Cells.Select
Selection.Copy
Selection.Paste Special Paste:=xlValues , Operation:=xlNo ne, SkipBlanks:= _
False, Transpose:=Fals e
Sheets("Data"). Range("A1").Sel ect
Application.Cut CopyMode = False

Sheets("Macro") .Select
ActiveWindow.Se lectedSheets.De lete

End Sub

Sub Create_File()

ActiveWorkbook. Save
ActiveWorkbook. Save
ActiveSheet.Sha pes("Button 1").Select
Selection.OnAct ion = "Create_Fil e"
Range("L14").Se lect

Sheets("Weekly Summary").Selec t
Sheets("Weekly Summary").Cells .Select
Selection.Copy
Selection.Paste Special Paste:=xlValues , Operation:=xlNo ne, SkipBlanks:= _
False, Transpose:=Fals e
Sheets("Weekly Summary").Range ("A1").Selec t
Application.Cut CopyMode = False

Sheets("MTD Summary").Selec t
Sheets("MTD Summary").Cells .Select
Selection.Copy
Selection.Paste Special Paste:=xlValues , Operation:=xlNo ne, SkipBlanks:= _
False, Transpose:=Fals e
Sheets("MTD Summary").Range ("A1").Selec t
Application.Cut CopyMode = False

Sheets("Data"). Select
Sheets("Data"). Cells.Select
Selection.Copy
Selection.Paste Special Paste:=xlValues , Operation:=xlNo ne, SkipBlanks:= _
False, Transpose:=Fals e
Sheets("Data"). Range("A1").Sel ect
Application.Cut CopyMode = False

Sheets("Macro") .Select
ActiveWindow.Se lectedSheets.De lete

End Sub

Sub Create_File()

ActiveWorkbook. Save
ActiveWorkbook. Save
ActiveSheet.Sha pes("Button 1").Select
Selection.OnAct ion = "Create_Fil e"
Range("L14").Se lect

Sheets("Weekly Summary").Selec t
Sheets("Weekly Summary").Cells .Select
Selection.Copy
Selection.Paste Special Paste:=xlValues , Operation:=xlNo ne, SkipBlanks:= _
False, Transpose:=Fals e
Sheets("Weekly Summary").Range ("A1").Selec t
Application.Cut CopyMode = False

Sheets("MTD Summary").Selec t
Sheets("MTD Summary").Cells .Select
Selection.Copy
Selection.Paste Special Paste:=xlValues , Operation:=xlNo ne, SkipBlanks:= _
False, Transpose:=Fals e
Sheets("MTD Summary").Range ("A1").Selec t
Application.Cut CopyMode = False

Sheets("Data"). Select
Sheets("Data"). Cells.Select
Selection.Copy
Selection.Paste Special Paste:=xlValues , Operation:=xlNo ne, SkipBlanks:= _
False, Transpose:=Fals e
Sheets("Data"). Range("A1").Sel ect
Application.Cut CopyMode = False

Sheets("Macro") .Select
ActiveWindow.Se lectedSheets.De lete

End Sub

[Guido Geurs]

Please, if possible, attach the workbook in Bytes.
To attach files in Bytes: see attached DOC or PDF for help.
It's much easier for us when we see the workbook and his structure than to figure out the code with names who have no meaning if you don't see the sheets.
If it's to large, just a part of the data so we can see what your layout is and we can work on the same sheets with data.
If the information on the sheets is confidential, please enter fictive data!

[msquared99]

OK here it is...

I am trying to get the program to copy the data from the Weekly Summary into the Data tab. I need to be able to select the column for each days worth of data.

Thanks,

Mike

[Guido Geurs]

Some questions:
- If you put the weeks in columns, have you enough columns?
last col= IV => 9*26 + 21 = 255 cols
52 weeks with 6 cols/week= 312 cols + total and title ...???= +-315 cols needed!!!
- normally the attributes (titles are in the columns) and the records (data) are in the rows (max.= 65536).
- must it not be: the data from the "DAILY Phone Stats Summary" to the "Data" = collection of data in WEEKS (= weekly)?
- when you enter the data in the "Daily" in rows the the only thing to do is copy the row to the "Weekly" on the right row (=> no need to select the row: calculation of the row with the date is possible)
- Why all those empty rows?
If you want to automate and work with data, please keep the lay-out simple!

[msquared99]

What I do is at the beginning of the new month I delete all the data in the data tab and start over. So I will always be using columns B thru AD in the data tab.

I do not need the entire years data in the spreadsheet.

[Killer42]

Just a note, I believe the latest versions of Excel have removed (or greatly enlarged) these old limits on the number of rows and columns.

[Guido Geurs]

this is how you can transfer data from one sheet to an other with an array:

(COLTO) is the selected column letter to transfer to in the weekly sheet .

the ranges to transfer are=
Payroll Svc. Today= from "AN9:AN34" => to "(COLTO)4:(COLT O)29"
Roanoke Lg Accounts= from "K9:K34" => to "(COLTO)32:(COL TO)57"
Data Support= from "M9:M34" => to "(COLTO)59:(COL TO)85"
FLEX= .......

Sub Fill_Weekly()
Dim ARRDATA() As Variant '§ array with data
Dim COLTO As String '§ col letter for dump
    COLTO = Split(ActiveCell.Address, "$")(1)  'For Column Letter
'§ Payroll Svc. Today
    ReDim ARRDATA(1 To 26, 1 To 1) '§ clear array
    Worksheets("Daily Summary").Activate
    ARRDATA = Range("AN9:AN34") '§ fill array with data
    Worksheets("Data").Activate
    Range(COLTO & "4:" & COLTO & "29") = ARRDATA '§ dump data
'§Roanoke Lg Accounts
    ReDim ARRDATA(1 To 26, 1 To 1)
    Worksheets("Daily Summary").Activate
    ARRDATA = Range("K9:K34")
    Worksheets("Data").Activate
    Range(COLTO & "32:" & COLTO & "57") = ARRDATA
'§ ....


End Sub

see also attachment

[msquared99]

OK. I'll give it a try tomorrow.

Thanks for your help!

[Guido Geurs]

Or if the size of the cells in the FROM and the TO sheets are identical, you can use "Copy - Paste special" like:

Sub Fill_Weekly()
Dim ARRDATA() As Variant '§ array with data
Dim COLTO As String '§ col letter for dump
    COLTO = Split(ActiveCell.Address, "$")(1)  'For Column Letter
'§ Payroll Svc. Today
    Sheets("Daily Summary").Select
    Range("AN9:AN34").Select
    Selection.Copy
    Sheets("Data").Select
    Range(COLTO & "4").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
'§Roanoke Lg Accounts
    Sheets("Daily Summary").Select
    Range("K9:K34").Select
    Selection.Copy
    Sheets("Data").Select
    Range(COLTO & "32").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
'§ ....


End Sub

[msquared99]

What I am going to try and do is use a loop and have the program ask me what column to paste the data in.