Loop that changes paste destination

[Jacek]

Hello all,

I come to you with a puzzle I cannot seem to solve. My current macro requests the user to pick a file and then copies and pastes columns from the selected file to the main workbook from which the macro is run. It does this 7 times (for each day of the week). I use no loops; all I did was copied and pasted the first "section" (1/7) of my macro and added the remaining 6. All I had to do was alter the paste destinations manually.

I was wondering if it is possible for me to create a loop that basically assigns the paste destinations a variable or counter of some sort, which would then change at the end of the loop procedure, and go through it again. This would obviously eliminate me from manually editing the code yet would not overwrite any data.

I know the exact paste destinations.

Thanks!

 

[Blade Hunter]

There are a number of ways to do this, what are your destinations? if the increment is the same numeric each time then its super easy, if not we can just bundle the row numbers into an array and poll that for the iteration .

Post back with the ranges and the code and I will check it out for you .

Cheers

Dan

Hello all,

I come to you with a puzzle I cannot seem to solve. My current macro requests the user to pick a file and then copies and pastes columns from the selected file to the main workbook from which the macro is run. It does this 7 times (for each day of the week). I use no loops; all I did was copied and pasted the first "section" (1/7) of my macro and added the remaining 6. All I had to do was alter the paste destinations manually.

I was wondering if it is possible for me to create a loop that basically assigns the paste destinations a variable or counter of some sort, which would then change at the end of the loop procedure, and go through it again. This would obviously eliminate me from manually editing the code yet would not overwrite any data.

I know the exact paste destinations.

Thanks!

 

[Jacek]

There are a number of ways to do this, what are your destinations? if the increment is the same numeric each time then its super easy, if not we can just bundle the row numbers into an array and poll that for the iteration .

Post back with the ranges and the code and I will check it out for you .

Cheers

Dan

Thanks Dan! Hopefully I include enough information for you here...:

I am always copying the same columns from all 7 files that I open. For example I open File 1, copy rows A to E. Open File 2, copy rows A to E. So the copying portion of the code will remain constant in the loop, if that makes sense.



As for the paste destinations of those 5 columns PER file, the pattern is as follows:

Columns A-E File 1 - Paste into the following columns of main workbook: B2, J2, R2, Z2, AH2

Columns A-E File 2 - Paste into the following columns of the main workbook: C2, K2, S2, AA2, AI2

So as you can see, for every succeeding file, the offset for each paste destination is 1 column (i.e. copying column A from File 1 into column B2 of main workbook, copying column A from File 2 into column C2 of main workbook, etc.).

The A-E columns are just an example, but hopefully this is clear enough.

Thanks in advance!

 

[Jacek]

Ok so here is my loop which doesn't seem to work. By that I mean its not copying the ranges from the selected file to the corresponding paste destinations. I can't seem to find any logical error...any help would be greatly appreciated.

I'd like to note that when I run this loop, the first TWO paste procedures go to the proper paste destination, the remaining 5 don't (they still get pasted, but not to the specified paste destinations I have using the offset).

If it loops more than once, all 5 columns of data are pasted but not to their respective paste destinations either.

Please note the changing offset column value in which I use a multiplication.

Again, I can't seem to find a problem in my logic, please help! Thanks!

counter = 1
 
' selecting first paste reference
Range("B2").Select
 
 
' define offset var
Dim offsetvar As Variant
offsetvar = confignumb + 1
 
' confignumb is the number of days specified by the user. So if there are 2 days selected, the offsetvar will begin at a value of 3. For example on loop 1, it will paste into col B, E, H, K and N. There's an extra column in between the 5 paste destinations, that is why I add 1
 
' day loops
 
Do
    ' Message prompt to pick the next DAY
    MsgBox ("Please select day " & counter & " of the week")
    ' Variable declaration
    Dim Day1 As String
 
    ' Assigning filename to variable
    Day1 = Application.GetOpenFilename("Excel Files (*.xls),*.xls", , , , False)
    ' Opening the file
    Workbooks.OpenText Filename:=Day1
 
    ' Set a new variable for this workbook to reference when opening
    Dim day1WB As String
    day1WB = ActiveWorkbook.Name
 
    'Select main worksheet
    Sheets(1).Select
 
    ' IM CREATING COLUMNS IN THE SELECTED FILE FOR COPYING
 
    Range("Z1").Value = "AvgTC12"
    Range("Z2").Formula = "=median(E2:F2)"
    Range("Z2").Select
    Selection.AutoFill Destination:=Range("Z2:Z1442")
 
    Range("AA1").Value = "AvgTC34"
    Range("AA2").Formula = "=median(G2:H2)"
    Range("AA2").Select
    Selection.AutoFill Destination:=Range("AA2:AA1442")
 
    Range("AB1").Value = "AvgTC56"
    Range("AB2").Formula = "=median(I2:J2)"
    Range("AB2").Select
    Selection.AutoFill Destination:=Range("AB2:AB1442")
 
' Copying the first column
    Range("Z2:Z1442").Copy
 
    'Activating this workbook
    Application.Workbooks("Book1").Activate
 
    ' Pasting first column into main book
    ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
    'Activating the specified day workbook to get average columns
    Windows(day1WB).Activate
 
    ' Copying the second column
    Range("AA2:AA1442").Copy
 
    'Activating this workbook
    Application.Workbooks("Book1").Activate
 
    ' Pasting second column into main book
    ActiveCell.Offset(0, offsetvar).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
 
 
    'Activating the specified day workbook to get average columns
    Windows(day1WB).Activate
 
    ' Copying the third column
    Range("AB2:AB1442").Copy
 
    'Activating this workbook
    Application.Workbooks("Book1").Activate
 
    ' Pasting third column into main book
    ActiveCell.Offset(0, offsetvar * 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
 
 
    'Activating the specified day workbook to get TC7 indoor for this day
    Windows(day1WB).Activate
 
    ' Copying the fourth column
    Range("K2:K1442").Copy
 
    'Activating this workbook
    Application.Workbooks("Book1").Activate
 
    ' Pasting fourth column into main book
    ActiveCell.Offset(0, offsetvar * 3).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
 
 
 
    'Activating the specified day workbook to get HFD1 indoor for this day
    Windows(day1WB).Activate
 
    ' Copying the 5th column
    Range("P2:P1442").Copy
 
    'Activating this workbook
    Application.Workbooks("Book1").Activate
 
    ' Pasting 5th column into main book
    ActiveCell.Offset(0, offsetvar * 4).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
    'redefine offsetvar
    offsetvar = confignumb + 1
 
    ' Closing day workbook
    Windows(day1WB).Activate
    ActiveWorkbook.Saved = True
    ActiveWorkbook.Close
'Select next start location, so from b2 we wil now start at c2
ActiveCell.Offset(0, 1).Select
'counter addition
counter = counter + 1
Loop While counter < (confignumb + 1)

 

[Jacek]

I'm assuming Dan is currently busy...anyone else got a clue?

Thanks!