AutoFill One Column In Destination Workbook

Jun 15, 2006

I have the following VBA code that will copy data from one workbook to another.

Sub copytoanotherworkbook()
Dim smallrng As Range
Dim destrange As Range
Dim destWB As Workbook
Dim Lr As Long
Application. ScreenUpdating = False
If bIsBookOpen("P&WM Estimate Tracking Sheet.xls") Then
Set destWB = Workbooks("P&WM Estimate Tracking Sheet.xls")
Else
Set destWB = Workbooks.Open("N:Estimate SheetP&WM Estimate Tracking Sheet.xls")
End If
Lr = LastRow(destWB.Worksheets("Tracking Sheet")) + 1
Set sourceRange = ThisWorkbook.Worksheets("Links").Range("A1:L1")
Set destrange = destWB.Worksheets("Tracking Sheet").Range("A" & Lr)
sourceRange.Copy
destrange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

The problem I am having is that when it copies the data to the last line of the destination workbook, I have a formula in column I of the destination workbook that calculates days remaining. How would I go about creating an autofill to fill the formula to only the last row of data? I've read some posts on this, but I don't think they deal with what I need.

View 7 Replies


ADVERTISEMENT

AutoFill Destination - General Way To Specify Destination Range

Apr 14, 2013

I want to fill all a column bottom-up but without specify cells in format "A1500:A2" like

VB:
Range("H1578").Select
Selection.AutoFill Destination:=Range("H2:H1578")

{H1578 have a formula so...}

I have data in the first 7 columns, with a variable(Range) for the first 5 columns

VB:
Dim oRange As Range
Set oRange = ActiveSheet.Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(89, 4))

{The first cell in it is A1}

My question is : How to fill Range("H2:H1578") using my ''oRange'' variable?

I try with OffSet but my code didn't work. I need to do the same with the 6 columns to the right and then plot in a chart.

View 4 Replies View Related

Autofill To Variable Destination

Aug 30, 2009

I have the following code that was achieved using the macro recorder. The only problem I am running into is that during the autofill command, there are never the same amount of rows to autofill. It could be as few as 5 rows and as many as there are rows on the spreadsheet. When I recorded the macro there were 953 lines, and unfortunately I cannot figure out how to change the specific destination of 953 to a variable destination determined by when data ends. Here is the code I currently have:

View 5 Replies View Related

Autofill Destination By Columns

Dec 20, 2009

How exactly do I autofill by columns? In this example I recorded a macro and cleaned it up autofilling through E1 but it could be a number up to 50.

View 2 Replies View Related

Find Specific Value In Column Of Another Sheet Then Copy Range Of That Row Onto Destination Workbook

Jun 19, 2014

I have data that is on a separate .txt file (the source file) that resembles this;

A
B
C
D
E
F

1
Case
District
Pct
Division
Level1
Level2

2
305035
0
20
72

[Code] ..........

Using VBA from within the destination workbook, I would like to use an input box that allows the user to request/specify what numerical number (0 through 55) in Column B of the txt file (the source file) will be used to determine what data to copy to the destination file. The cells to copy to the destination file would be from Column A through Column G of the source file onto Sheet1, E2 of the destination file.

Column B of the Source file will only contain numbers. However there will be numerous identical numbers in Column B. All of which will need to be copied onto the destination workbook.

From the sample above, if the user were to enter '15' in the input box, the desired result on the destination sheet beginning at E2 would look like this;

E
F
G
H
I
J

1
Case
District
Pct
Division
Level1
Level2

2
502046
15

1
3

[Code] ............

View 9 Replies View Related

Formula In Destination Workbook Will Not Update When Change Tab Name In Source Workbook

Jul 17, 2014

I am working with multiple workbooks with several tabs in each one. I need the forumula to update the "sheet name" from the source workbook even if the destination workbook is closed.

this is a portion of the forumula I am working with:

=SUM(IF(AND([L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet25!$I$4>=182,[L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet25!$I$4=182,
[L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet26!$I$4=182,
[L2_EI_Phase_II_monitoring_workbook.xlsx]Sheet27!$I$4

View 9 Replies View Related

Reference A Source Workbook By Having The Title In Destination Workbook?

Jun 6, 2014

I want see if it's possible to take the name of the source workbook and put it in a cell in my destination workbook and then use that as the reference in formulas. The source document title includes a date that changes yearly, or in some instances changes entirely, and I want to keep the formulas in the destination workbook intact. I don't want to have to go to every instance where I have used the source workbook and change the title. Instead I would like to copy the source workbook title, paste it into the destination in the same cell and have that propogate the needed changes into the destination workbook formulas.

View 2 Replies View Related

Macro To Open Workbook - In Selected Destination

Oct 9, 2009

I currently have a macro that goes into a specified folder and opens all the files within that folder. This works great for me, but if i have to change the location of the folder i need go into the macro and change the destination code.

I was hoping to let other people use this, but not necessarily people who would be able to go in and manually change the folder location. What i was wondering is if there is a function out there which would pop up a browse box which would allow the user to specify the folder location in which the files are kept? This would mean any user could use the tool to open the files without having to have the spreadsheet setup up on their machine.

View 9 Replies View Related

Check If Sheet Exists In Destination Workbook Before Copying

Nov 26, 2012

I am trying to write some code that will copy the worksheets from one workbook (wkbSource) to another (wkbTarget), but I need it to maker sure the worksheets being copied from wkbSource don't already exist in wkbTarget. If they do exist, it just skips and moves to the next worksheet. Here is the code I have already, I thought that by adding the On Error Resume Next to the code it would just skip it, but for some reason it is still copying the first duplicate workbook, then it skips.

Code:
Dim wkbSource As Workbook
Dim wkbTarget As Workbook
Dim WorkbookName As String
WorkbookName = ThisWorkbook.Name

[Code] .........

View 3 Replies View Related

Search In A Column & List In Destination A Column

Feb 5, 2010

I like to search in a columns(1) for specific words say “Don” and then write in another column (say column 6) as “one”. Then it should keep doing until the last cell in that column. It should do nothing if "Don" is not found.

How can I do it?

View 5 Replies View Related

Excel 2007 :: Autofill Column B With Sequential Values Based On Value In Column A

Nov 27, 2011

Autofill column B with sequential values based on whether value in column A changes its value.

I would like to autofill column 'B' with sequential values (i.e. GenoMap1, Genomap2, Genomap3,... GenoMap10, GenoMap11, GenoMap12,... GenoMap104, GenoMap105, etc...), but changing to the sequential GenoMap# only when the value in column A changes.

This is what I imagine.

A1 "Alfiero", B1 "GenoMap1"
A2 "Alfiero", B2 "GenoMap1"
A3 "Alfiero", B3 "GenoMap1"
A4 "Allocati", B4 "GenoMap2"
A5 "Amaranto", B5 "GenoMap3"
A6 "Amaranto", B6 "GenoMap3"
A7 "Amaranto", B7 "GenoMap3"
A8 "Ambrosiano", B8 "GenoMap4"
A9 "Ambrosiano", B9 "GenoMap4"
A10 "Ambrosiano", B10 "GenoMap4"
A11 "Ambrosiano", B11 "GenoMap4"

I listed examples above of GenoMaps higher than 10 and 100 to show how I need them numbered.

I'm using MS Excel 2007 in Windows 7.

View 2 Replies View Related

Copy Multi Cell Range To Destination Workbook Range

Dec 23, 2008

Trying to copy a range from Sender.xls (sheet) Lists backstage
onto Userform.xls (sheet) Behind the Scenes

When trying to copy the values within a multicell range, the destination cell range (same size) becomes blank.

View 2 Replies View Related

Autofill From Another Workbook By Matching Cell Contents

Jan 12, 2007

In the attachment, I have two different workbooks named 1099 and SJC. What I would like to automate if possible is to have Column F in 'SJC' autofill by matching the Vendor # (Column A) values to the '1099' Account Number (Column A). The values that need to be copied cover are located in Column C. Hope that is clear enough.

As an example:
I've highlighted one row in yellow to show what I need done. For Vendor No. A1W01 from '1099', the value I need copied over to Column F of SJC is 920.61. I need this automated because I have a collection of files like this that I need to link in this way and they are all quite large (the attached versions are stripped in order to meet the size requirements at this forum).

View 3 Replies View Related

Insert Column, Move Another Column Into It And Autofill

Feb 3, 2009

I have attached a sample spreadsheet – what I want to do is insert a column before the current column A and move the current column D in to it and auto fill for every non blank cell in column B. Also, there are different values in column D such as “B03” ,”B41” etc – so for example, it should auto fill B03 until it comes across either a blank cell in column B or it comes across a “B41” and then it should do the same until it comes across a “B42” or a blank cell again. This needs to continue till the end of the report.

View 2 Replies View Related

Autofill Column B Based On Column A Entries

Oct 9, 2009

I have a Listbox that outputs data to Column A. Is there a way to auto populate Column B with 4 items for each Entry in Column A?
The 4 items that will populate in Column B will always be the same.

I have attached an example to better explain what I am trying to describe.

View 6 Replies View Related

Autofill Column To Repeat Set Of Data From Another Column

Aug 10, 2013

I want to Autofill a Column C to repeat a set of data in Column A, if Column B contains an integer, as shown below. Being able to Autofill this data allows me to have a dynamic table for use on multiple projects that may have a different amount of rows in Column B.

Outflow % Table
25
50

[Code]......

View 6 Replies View Related

Autofill Row Instead Of Column?

Jul 11, 2014

I want to change the following code so that it fills a row instead of a column

[Code] ......

This fills column "A" top down but I now would like to fill row "1" left to right.

View 2 Replies View Related

AutoFill More Than One Column

Sep 17, 2008

I have code to fill down to the last row with data for 1 column Can I adapt the code to fill down more than 1 column. current code for 1 column

View 2 Replies View Related

AutoFill To Last Row In Column A

Feb 10, 2010

the code as shown below so that in column A of worksheet: Data to Text I need an AutoFill from cell A1 to the last row. The value in cell A1 needs to be: 1

If there are more than 1 records then I need an AutoFill from cell A1 to the last row. However, the value in cell A1 needs to be 1, value in cell A2 needs to be 2, value in cell A3 needs to be 3 etc...

View 2 Replies View Related

Autofill Formula To End Column VBA?

Aug 1, 2012

The code below inserts a formula into E2, copies it across to column O and then down to the last row. It works fine but if more columns are added obviously it would need to be changed to go up to column P for example.

How can I change this so that the formula will be copied across to the last column automatically and therefore the code will not need to be changed?

Code:
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("E2").Select

[Code]....

View 2 Replies View Related

AutoFill Till Last Used Column

Jan 8, 2009

I have a spreadsheet with a varying number of columns with data.

Now I like to copy the header over it till the last column that contains data.

If I used the recorded VBA the range is stated in letters (In the sample below column K but this can be any column).

What is the best way to code it in such a way that one gets the headers till the last column used?

Range("A1:B1").Select
Selection.AutoFill Destination:=Range("A1:K1"), Type:=xlFillDefault

View 9 Replies View Related

Autofill Data In A Column From One Sheet To Another

May 29, 2014

I'm trying to autofill data in a column from one sheet to another in the same workbook. My goal is to be able to paste data in one sheet and have it appear in another sheet.

View 2 Replies View Related

AutoFill Column With Repeating Numbers

Dec 26, 2012

I have a spreadsheet that lists numbers in a column. They increase 1 number at a time, however the same number is listed 24 times before increasing to the next. Scaled down to 3 times it would look like this: 111, 222, 333, 444, 555, each one of those individual numbers would have their own cell. When I highlight the column and drag down, it starts doing decimals..how do I get it to recognize it? sample.xlsx

I want to drag it down to continue the pattern. Right now, I type a couple of numbers and drag to finish the section.

View 3 Replies View Related

VBA - Autofill To First Blank Adjacent Column?

Mar 5, 2014

I want to write some VBA code that simulates the double click of the square in the lower right of a selected cell. When you double click it, it fills every cell in column A until the first blank in column B. I was able to find this code searching around:

Code:
Selection.AutoFill Destination:=Range("A10:A" & Range("B10" & Rows.Count).End(xlUp).Row)
Selection.Copy

The issue with the above is that it copies the data all the way down to the LAST row in column B with data.

I want to have the macro stop when it finds the FIRST blank cell in column B.

Just as an example, the last row of data in column B is 100, but in row 67 the cell is blank, so I want it to stop at row 66.

View 2 Replies View Related

Autofill Data In A Column - Assignment

Feb 27, 2009

On Sheet1 I have a small list of names in column A and a number next to the name in column B. This number may vary.

What I want to do is on Sheet2 in column A repeat the name based on the number from sheet1 in column B.

Now the sheet is shared among all of us and macro are out. Is this something that is possible with a function?

View 9 Replies View Related

Use Column To Autofill New Column With The Correct Value?

Apr 14, 2014

I have a colum full of durations in 00:00:00 format. I need a forumla to look at these durations, count the seconds and then multiply those seconds by a predfined rate. In effect giving me a second colum full of costs.

I have attached a screenshot f the data, and the rate should be 0.0073p (.73 of a penny)

View 5 Replies View Related

Excel 2013 :: AutoFill Formula Down A Column

Dec 9, 2013

I am trying to auto fill this formula down a column but it doesn't keep the C4,D4,E4...ETC to stay constant

I manually did these two correct ones

=SUM(C5*C4+D5*D4+E5*E4+F5*F4+G5*G4+H5*H4+I5*I4+J5*J4+K5*K4+L5*L4+M5*M4+N5*N4
+O5*O4+P5*P4+Q5*Q4+R5*R4+S5*S4+T5*T4+U5*U4+V5*V4+W5*W4+X5*X4+Y5*Y4+Z5*Z4+AA5*AA4)

=SUM(C6*C4+D6*D4+E6*E4+F6*F4+G6*G4+H6*H4+I6*I4+J6*J4+K6*K4+L6*L4+M6*M4+N6*N4
+O6*O4+P6*P4+Q6*Q4+R6*R4+S6*S4+T6*T4+U6*U4+V6*V4+W6*W4+X6*X4+Y6*Y4+Z6*Z4+AA6*AA4)

when I drag it down it incorrectly looks like this:

=SUM(C7*C6+D7*D6+E7*E6+F7*F6+G7*G6+H7*H6+I7*I6+J7*J6+K7*K6+L7*L6+M7*M6+N7*N6
+O7*O6+P7*P6+Q7*Q6+R7*R6+S7*S6+T7*T6+U7*U6+V7*V6+W7*W6+X7*X6+Y7*Y6+Z7*Z6+AA7*AA6)

I want C6,D6,E6 to be C4,D4,E4 ETC

View 3 Replies View Related

Find Last Populated Cell And Autofill Column - VBA

Jul 3, 2012

DATA
DATE
PRICE

DATA
20120621
118.1000

[Code] .......

I have 3 columns (data, date & price). What I would like to do is find the last populated cell in 'date' column and autofill it based on column 'data'. The tricky thing is that the date form is text and while autofilling manually it changes.

View 6 Replies View Related

How To Autofill 9,000 Rows With Changing Reference In Same Column

Aug 9, 2012

I have over 9,000 rows of data. In column A, I have different values that I need to populate down to associate with values in other columns. I can't simply autofill all 9,000 cells in column A at once, because the values that need to be filled down change at irregular intervals.

My end goal is to be able to filter out values in column B to show their association with the value in column A, but I need column A fully populated.

So I need a way to fill A2:A7 with value from A1 (I don't care about B7 being empty, I can still have Martha in A7 with no adverse affects). But I need the fill to continue through 9,000+ rows where the number of rows to fill is inconsistent between value changes in column A (Martha-5, Sarah-3, Beth-4, Donna 3), and there are over 400 unique values in column A.

This is definitely more involved than I am familiar with, but any simple way for me to identify and list which of the 400 bakers made scones..

A
B
1
Martha

[Code]....

View 5 Replies View Related

AutoFill Column To New Sheet Based On Value - Autoupdate

Mar 31, 2014

Master Template
Project 1
Project 2

Task 1
1 - Mar
28 - Dec

Task 2
2 - Mar
2 - Jan

Project Due
5 - Mar
4 - Jan

Above is a table we have for high level tracking on specific tasks due for projects.

What I want to do is: If the last cell in a column = Jan then it will automatically copy the whole column and insert it into a Sheet named January and have it automatically update. Below is the end result.

January Template
Project 2

Task 1
28 - Dec

Task 2
2 - Jan

Project Due
4 - Jan

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved