I am attempting to create a macro that opens a file named "shrinkage-billing.xls", searches for a variable "PTOSH" in column A, copies the adjacent cell and pastes the data in another worksheet named "Shrinkage Report 2009.xls"
The code follows ...
I tried searching for code for each part of the task separately and trying to piece together multiple macros, that did something similar, but I’m not have a lot of luck, so I’ll break it down into two treads, I hope this will follow the rule of one question per post, but if I should have separated this request even farther as two treads, please let me know and I’ll be happy to comply.
I was given a spread sheet with a macro that loads data from a SQL Data base (not mine), it returns anywhere from one row to 100’s of rows data. Since it’s not my spread sheet and macro, I copied this sheet to my destination workbook. The Data loads in 7 columns starting in B4:H4, with the column headings titles: Acct#, Id#, Name, Qtr1 Totals, Qtr2 Totals, Qtr3 Totals, and Qtr4 Totals. Data cells in spreadsheet not formatted (general)
What I’m trying to do:
part 1:
I need a macro to copy the data only for columns Id# thru Qtr4 Totals (C5:H5 down thru the last row with data) to another sheet. The destination sheet range starts with rows B13:G13, with formulas below and to the right in columns H13, and J13:N13
(column I is blank).
The formulas below the range are a subtotal of the copied columns (D:G). They could be remove and totaled after they’re copied, so there's nothing below the copied range? Since I need the column totals as a subtotals, my thought was to have the row heading and formulas already on the destinations sheet and just insert a blank rows between as each row was copied?
part 2:
As each row of data is copied from the source sheet, I need the macro to copy the formulas to the right of the copied range (H13, and J13:N13) on the destination sheet down for each new row of data copied from the source sheet
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.
Since you can copy to someplace with one line of code like this:
Sheets(1).Range("A1:Z5000").Copy Destination:=Sheets(2).Range("A1")
is there any way of doing this with formatting, or are things like paste special features only possible by selecting something?
Ie: Values, column widths, formats, etc. Does the copy destination argument only allow simple copying?
Is there any way to use the formula below and send only the values?
FY07.Copy Destination:=Sheets("Age Roll Up").Range("A1")
I know that I can copy and paste special, but was wondering if I could tweak the above line in any way to?
I have the following code to copy a range to another destination. I would like only the value to go to the destination. How to implement this?
Set rngtocopy = Nothing
On Error Resume Next
Set rngtocopy = Range(rngdata(2, 9), rngdata(lngrows - 2, 9)).SpecialCells(xlCellTypeVisible)
On Error Goto 0
If Not rngtocopy Is Nothing Then
rngtocopy.Copy Destination:=Cells(5, 14 + cc + (t - 1) * 3 + (i - 1) * 17)
I am sure this is simple but cant get me head around it (tried paste & paste link).. I am wanting to copy formulas in file (A) to file (B) with data source linked in the file (A).
i want is when i copy the formulas to file (B) sheet 2 and it should retain the original path say H:excelfile A[sheet1]$G$1+....xls and source from which the data is being used.
ws5 and ws7 are defined and working due to other parts of the code already using them. ive copied the syntax from some of my other copy destination codes the only difference here is instead of ("A2:J10") ive got "(Cells(2,1):Cells(iRow, 10)" as the row number for this is variable, and it is calculating the correct value for iRow and iRow2. its probably something stupid im missing but i cant figure it.
View 7 Replies View RelatedI have a NAMED RANGE of data consisting of 4 columns and as many rows as is selected. Name it CompTable1.
I loop through all of the 4 columns of data to format it (as shown below). This snippet is for column 1 of CompTable1 (a public variable as string) and I basically have the same situation for each column of 4 but replacing each respective x in cells.(n,X) & offset(0,-x) to take into account of where I wish the data to go as I loop through. Ultimately 4 (For Next) loops.
[Code] ......
The new range with 5 columns is as follows:
Column 1 from CompTable1 = Column 1 of new range
Column 2 of new range is blank for something later
Column 2 from CompTable1 = Column 3 of new range
Column 3 from CompTable1 = Column 4 of new range
Column 4 from CompTable1 is not used in the new range but is formatted
Column 5 of new range is blank for something later
This certainly works very well and with my amateur VBA status am quite proud of how far I have come in a few months. Is it efficient? Is it free of possible errors? Likely not. I wish to rid myself of the "Select" each cell plaque though maybe using a resize, offset or copy destination operation but can't seem to get it working. Once I do get the data in the new range, I format everything without selecting quite well with my methods described above.
I'm currently working on a problem that I'm having with Hyperlinks. Basically I want to copy the destination data that the hyperlink points to onto another sheet.
The main problem I'm having here is that I cant seem to find a suitable method to do this....I had the idea of using the hyperlink.follow function and then copying the data once I'm there and then moving back to the source hyperlink. My only problem with that is that I dont know how to find the cell address that the hyperlink is in, once I have found that hyperlink.
Therefore my question is two-fold:
- Is there a way to copy the destination data using some sort of hyperlink function?
- If not, is there a way to find the cell address within a worksheet, once a hyperlink is found on that worksheet?
I've attached the spreadsheet below to give a better idea of what Im doing, along with the "findHyperlinks" macro,within that workbook, that I am currently working on.
I think I am closer but how do I get all of the data from A2 in the origination worksheet and then copy it to the last blank cell in column A in the destination sheet?
Code:
Sub test4()
Dim lastrow As Long
Set StartSheet = ActiveSheet
'MFG PART NUMBER
StartSheet.Range("R2").Copy
Workbooks.Open Filename:="D:\_DoxaMobiusServtrax Import Template"
[Code]...
I have this code that copies existing folders to a new destination path. How do I change the code so that the From path and To path is based on cell value rather than writing it into the code?
Code:
Sub Copy_Folders()Set Fobj = CreateObject("Scripting.FileSystemObject")
Fobj.CopyFolder "C:UsersHengDocuments1243-01234photos", "C:UsersHengDocumentsCasework1243-01234photos"
End Sub
i am writing a custom copy/paste routine which will paste data from a selection if the cell being pasted to is not locked.
The user will select the text for coping and then launch the routine.
The routine will query for destination cell and then process the paste.
This is my routine so far:
Sub testCopy()
Dim c As Range
Dim MyRange As Range
Dim Dest As Range
Set MyRange = Selection
Set Dest = Application.InputBox(prompt:="Select a cell", _
Title:="Paste Destination", Type:=8)
For Each c In MyRange
If Dest.Locked = False Then
Dest.Value = c.Value
End If
'dest = ?
Next c
End Sub
What I need to know is how to increment the dest reference so it is in the same 'relative' position from the initial dest that c (from the selection) is in or is there a better way to do this?
I have a source data sheet that has around 50 columns. I want to copy and past selected columns to a destination sheet.
View 1 Replies View RelatedI´m writting a macro. It works find until a certain point. When I want to change some outputs of the macro without changing the syntax, it display an error mesage while runing the macro. It says Else without If. Which is quite disturbing because the Else was not creating any problem before. Here is my macro before I changed the conditions (this one work nicely)
Sub Copy_Sheet_Beta()
Set wba = ActiveWorkbook
On Error Resume Next
If IsWorkbookOpened("Projekt.xls", "C:Documents and SettingsfrederikSkrivebordRedd Barna") Then
Workbooks("Projekt.xls").Activate 'In case open, just activate "Projekt"
Else
Workbooks.Open Filename:="C:Documents and SettingsfrederikSkrivebordRedd Barnaprojekt.xls"
End If
Set wb = Workbooks("Projekt.xls")
wb.Activate
If Not SheetExists(wba.ActiveSheet. Range("C1").Value) Then
MsgBox "overall doesn't exist!"
Else........................................
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] ............
Got a wee problem with some syntax for a copy function. At the moment the code i have copies the range and pastes to another range which is defined by a cell number. But i need it to paste special (values only).
View 5 Replies View RelatedI thought I had this cracked last month when I ran a set of reports using some code dependent on using advanced filters to first create a unique list to loop through and then to create a list of all entries in a list for each unique entry. The idea being that I extracted all the data for each unique entry in the list and saved that to a new workbook, named after the unique entry.
However, this month I'm totally flummoxed again. I can't seem to get even the first part, creating a unique list, to run.
I have a sheet with several thousand rows of data with maybe 25 columns. I have the following code to try to extract the unique entries in the 2nd column, column B and store them in the same worksheet in column AD, to use when looping through the list.
VB:
With ActiveSheet
.Range("AD1").EntireColumn.Delete
.Range("A1").CurrentRegion.Columns(2).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet1.Range( _
[Code].....
I am wondering if using an "if" statement or a case statement would be good ways to check two worksheets in two different workbooks to see if their names match? I could use some help correcting my syntax too:
For 1 to ws.count
If Worksheet("one").Name = Worksheet("two").Name
Worksheet("one").Range("A1:G84").Copy
Worksheet("two").Range("A1:G84").Paste
Endif
Next ws.Count
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.
My goal would be to click on a cell within a range of cells (in a column) and have the value copied to a specified destination cell. There would be a few different columns with source cells and two destination cells. Each column would to copy to a specific cell. My ojective is basically to deal with different processing times (days, weeks, months).
Here's a little visual, clicking on C4 for example would copy the value "5" to cell B2. Clicking on D4 would copy "12" to A2.
row/col
A
B
C
D
E
1
Days
Weeks
Processing time X (weeks)
Processing time Y (in days)
Processing time Z (in weeks)
2
3
10
110
3
4
11
111
4
5
12
112
5
6
13
113
I would like to Copy the cell value from A1 into the next available empty cell in a column (in this case sheet2 A) so that I can create a list of values from A1 over time and graph it.
View 6 Replies View RelatedI am attempting to copy the contents of one workbook (Project) to another (NotesName)
Windows(Project).Activate
Sheets("Matrix Calx").Copy Destination:=Workbooks(NotesName).Sheets("Matrix Calx")
I can't get Destination: or Before: to get the desired result and can't find any info on the variables.
What would be the best way to copy from one workbook to another when the data has to be transfered to the correct/corresponding worksheet?
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 RelatedHow 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 RelatedHow do I change the destination Cell in a workbook from data entered in a macro?
As an example If I press the command button the first time the Textbox1.value is sent to Cells(11, 5).
The next time I press the Same command button the data Textbox1.value is sent to Cells (12, 5)
Mainly is the rows I want to change not the coloums!
Struggled with this for weeks now which I am sure is a simple issue to code.
I am struggling to make people understand the problem though so I hope this works!
I am trying to do is when a certain criteria is met I want to pull some information from the net using a web query, and then pasting it into a variable location. I have this code -
Sub Check_Matches()
Dim XLoop As Integer, XPlay_Row As Integer, Loop_Length As Integer
Dim Match_Ref As String, Team_Addr As String, Match_ID As String, Import_Addr As String
XLoop = 1
XPlay_Row = 5
Loop_Length = Range("j1").Value + 1
Do While XLoop < Loop_Length
Team_Addr = "S" & XPlay_Row 'A variable that stores the reference to the cell containing the team ID.....................
I would like a user of my program to choose the destination folder where a .txt file will be saved. I have code below which allows the chooser to browse through folders and choose a file:
Dim f1 As Variant, s1 As Variant
s1 = "#1: Choose the .txt file which contains montly data for the stock price."
f1 = Application _
. GetOpenFilename("TextFiles(*.txt),*.txt", , s1)
txt2.Text = f1
I want the user to browse folders and subfolders like they can with the above code. However the user will select a file path not a particular file. In the case of the code above, the text of Text Box txt2 would be set to the file path (i.e. "C:Documents and SettingsAdministratorDesktop")
How to create a hyperlink that when copied refers to a new cell?
View 3 Replies View RelatedIm trying to adapt a formula destination to cell.
I have a formula on cell B2 that is directed do other excel file, It appears like [1.xlsx] 1 being the name of the other file.
What I am trying to is make something like:
cell A2 = 1
and ["A2".xlsx]
so whenever I change the A2 cell, the destination also changes.