Insert Magibon Mririan into Speardsheet!

Hi guys,

How do you get data from other spread sheet?
What is the function?

For example,
on my 1st Spreadsheet (call it A.xls)
I have value in say column A will have code number and the second row will have some number. I will looks like this:

Code
1111 1112
Qty Qty
1.1 (Formula) (Formula)
1.2

Then in ther spreadsheet (B.xls) will have qty in column A, code in column B and Number in column C. It looks like:

Qty Code Number
1 1.1 1111
1 1.1 1111
1 1.2 1111
2 1.2 1112
1 1.2 1112

Then I want to get the formula in A.xls to fill the cell so that

1.1 & 1111 will have Qty of 2,
1.1 & 1112 will gave Qty of 1 and so on.

Can anyone help me pls. What should I do?

Thank you so much for any help.

I want to insert 20 sheets at a time and rename them as per my own choice. like Ram, Sham, Neel etc. What is the macro code for that.

I have an electronic phone account, that i need to sort by person, then sum each person calls and when i print it i need each person on a seperate page.

So i need to be able to:

insert a blank row between different text (like below)
1
1
1

2
2

Then auto sum on the blank line of whats above in a certain column (ie H)
then insert a page break.


Hope this makes sense

I have a good one here. I have a Table of (GetPivotData items). However, to update the Table to include (new) Freeport & Baton Rouge, I must first:

1) Add the new site info (col. A), With the Years to insert (col. C and along with the Quarters (col. F - I: see pattern)
2)Tell it how many additional rows needed (col. E) in the Mstr Table.
3) Insert # of Rows, Copy/Paste Special Values in the Columns A, C & D) of the Mstr_GPD_Table. Has a Pattern of each Consignee per # of Year(s) (listed in Table) w/ 4 Qtrs to each year.

Harder to explain...Easier to show.


My Input Table (Consignee Data Table):

Consignee Macro Data Table A B C D E F G H I 1 Consignee Number Consignee Name Year Start Year End Rows Needed Quarter1 Quarter2 Quarter3 Quarter4 2 75-2778918CK Cookeville 2006 2016 44 Q1 Q2 Q3 Q4 3 75-2778918LB Lynchburg 2006 2016 44 Q1 Q2 Q3 Q4 4 75-2778918LV Louisville 2006 2016 44 Q1 Q2 Q3 Q4 5 75-2778918PT Pittsburgh 2006 2016 44 Q1 Q2 Q3 Q4 6 75-2778918RL Raleigh 2006 2016 44 Q1 Q2 Q3 Q4 7 75-2778918SP Springville 2006 2016 44 Q1 Q2 Q3 Q4 8 75-2778918SS Sulphur Springs 2006 2016 44 Q1 Q2 Q3 Q4 9 75-2778918WV Woodlands 2006 2016 44 Q1 Q2 Q3 Q4 10 75-2779018FV Freeport 2006 2016 44 Q1 Q2 Q3 Q4 11 75-2778918BR Baton Rouge 2007 2009 12 Q1 Q2 Q3 Q4 Excel 2007

Worksheet Formulas Cell Formula A2 =IF( ISBLANK( ConsigneeInfoTable[[#This Row],[Consignee Number]] ),"",ConsigneeInfoTable[[#This Row],[Consignee Number]] ) B2 =IFERROR( VLOOKUP( A2,ConsigneeInfoTable,2,0 ),"" ) A3 =IF( ISBLANK( ConsigneeInfoTable[[#This Row],[Consignee Number]] ),"",ConsigneeInfoTable[[#This Row],[Consignee Number]] ) B3 =IFERROR( VLOOKUP( A3,ConsigneeInfoTable,2,0 ),"" ) A4 =IF( ISBLANK( ConsigneeInfoTable[[#This Row],[Consignee Number]] ),"",ConsigneeInfoTable[[#This Row],[Consignee Number]] ) B4 =IFERROR( VLOOKUP( A4,ConsigneeInfoTable,2,0 ),"" ) A5 =IF( ISBLANK( ConsigneeInfoTable[[#This Row],[Consignee Number]] ),"",ConsigneeInfoTable[[#This Row],[Consignee Number]] ) B5 =IFERROR( VLOOKUP( A5,ConsigneeInfoTable,2,0 ),"" ) A6 =IF( ISBLANK( ConsigneeInfoTable[[#This Row],[Consignee Number]] ),"",ConsigneeInfoTable[[#This Row],[Consignee Number]] ) B6 =IFERROR( VLOOKUP( A6,ConsigneeInfoTable,2,0 ),"" ) A7 =IF( ISBLANK( ConsigneeInfoTable[[#This Row],[Consignee Number]] ),"",ConsigneeInfoTable[[#This Row],[Consignee Number]] ) B7 =IFERROR( VLOOKUP( A7,ConsigneeInfoTable,2,0 ),"" ) A8 =IF( ISBLANK( ConsigneeInfoTable[[#This Row],[Consignee Number]] ),"",ConsigneeInfoTable[[#This Row],[Consignee Number]] ) B8 =IFERROR( VLOOKUP( A8,ConsigneeInfoTable,2,0 ),"" ) A9 =IF( ISBLANK( ConsigneeInfoTable[[#This Row],[Consignee Number]] ),"",ConsigneeInfoTable[[#This Row],[Consignee Number]] ) B9 =IFERROR( VLOOKUP( A9,ConsigneeInfoTable,2,0 ),"" ) A10 =IF( ISBLANK( ConsigneeInfoTable[[#This Row],[Consignee Number]] ),"",ConsigneeInfoTable[[#This Row],[Consignee Number]] ) B10 =IFERROR( VLOOKUP( A10,ConsigneeInfoTable,2,0 ),"" ) A11 =IF( ISBLANK( ConsigneeInfoTable[[#This Row],[Consignee Number]] ),"",ConsigneeInfoTable[[#This Row],[Consignee Number]] ) B11 =IFERROR( VLOOKUP( A11,ConsigneeInfoTable,2,0 ),"" ) E2 =( ( D2-C2 )*4 )+4 E3 =( ( D3-C3 )*4 )+4 E4 =( ( D4-C4 )*4 )+4 E5 =( ( D5-C5 )*4 )+4 E6 =( ( D6-C6 )*4 )+4 E7 =( ( D7-C7 )*4 )+4 E8 =( ( D8-C8 )*4 )+4 E9 =( ( D9-C9 )*4 )+4 E10 =( ( D10-C10 )*4 )+4 E11 =( ( D11-C11 )*4 )+4



My Input Table (Mstr_GPD_Table): Must INSERT the # of rows per the above table first and add the info in colums A, C and D. Column B is a VLookup formula.

Mstr_GPD_Table A B C D 1 Consignee Number Consignee Name Year Quarter 2 75-2778918WV Woodlands 2006 Q1 3 75-2778918WV Woodlands 2006 Q2 4 75-2778918WV Woodlands 2006 Q3 5 75-2778918WV Woodlands 2006 Q4 6 75-2778918WV Woodlands 2007 Q1 7 75-2778918WV Woodlands 2007 Q2 8 75-2778918WV Woodlands 2007 Q3 9 75-2778918WV Woodlands 2007 Q4 10 75-2778918WV Woodlands 2008 Q1 11 75-2778918WV Woodlands 2008 Q2 12 75-2778918WV Woodlands 2008 Q3 13 75-2778918WV Woodlands 2008 Q4 14 75-2778918WV Woodlands 2009 Q1 15 75-2778918WV Woodlands 2009 Q2 16 75-2778918WV Woodlands 2009 Q3 17 75-2778918WV Woodlands 2009 Q4 18 19 Excel 2007

Worksheet Formulas Cell Formula B2 =VLOOKUP( $A2,'[2007 - MASTER REPORT - EntryRpt.ACE.FCD.xlsm]Consignee Info'!A:B,2 ) B3 =VLOOKUP( $A3,'[2007 - MASTER REPORT - EntryRpt.ACE.FCD.xlsm]Consignee Info'!A:B,2 ) B4 =VLOOKUP( $A4,'[2007 - MASTER REPORT - EntryRpt.ACE.FCD.xlsm]Consignee Info'!A:B,2 ) B5 =VLOOKUP( $A5,'[2007 - MASTER REPORT - EntryRpt.ACE.FCD.xlsm]Consignee Info'!A:B,2 ) B6 =VLOOKUP( $A6,'[2007 - MASTER REPORT - EntryRpt.ACE.FCD.xlsm]Consignee Info'!A:B,2 ) B7 =VLOOKUP( $A7,'[2007 - MASTER REPORT - EntryRpt.ACE.FCD.xlsm]Consignee Info'!A:B,2 ) B8 =VLOOKUP( $A8,'[2007 - MASTER REPORT - EntryRpt.ACE.FCD.xlsm]Consignee Info'!A:B,2 ) B9 =VLOOKUP( $A9,'[2007 - MASTER REPORT - EntryRpt.ACE.FCD.xlsm]Consignee Info'!A:B,2 ) B10 =VLOOKUP( $A10,'[2007 - MASTER REPORT - EntryRpt.ACE.FCD.xlsm]Consignee Info'!A:B,2 ) B11 =VLOOKUP( $A11,'[2007 - MASTER REPORT - EntryRpt.ACE.FCD.xlsm]Consignee Info'!A:B,2 ) B12 =VLOOKUP( $A12,'[2007 - MASTER REPORT - EntryRpt.ACE.FCD.xlsm]Consignee Info'!A:B,2 ) B13 =VLOOKUP( $A13,'[2007 - MASTER REPORT - EntryRpt.ACE.FCD.xlsm]Consignee Info'!A:B,2 ) B14 =VLOOKUP( $A14,'[2007 - MASTER REPORT - EntryRpt.ACE.FCD.xlsm]Consignee Info'!A:B,2 ) B15 =VLOOKUP( $A15,'[2007 - MASTER REPORT - EntryRpt.ACE.FCD.xlsm]Consignee Info'!A:B,2 ) B16 =VLOOKUP( $A16,'[2007 - MASTER REPORT - EntryRpt.ACE.FCD.xlsm]Consignee Info'!A:B,2 ) B17 =VLOOKUP( $A17,'[2007 - MASTER REPORT - EntryRpt.ACE.FCD.xlsm]Consignee Info'!A:B,2 )



Thanks For Your Help In Advance,

I have a form with data on ws2 that is transferred to ws1 with
completed. When copied to ws1, it is copied to first empty row based
on the content of column 3.


To accomplish, I have used the following code to find first empty row
and then begin to copy data to that row.


===========================


'find first empty row in database
CellRow = ws1.Cells(Rows.Count, 3) _
.End(xlUp).Offset(1, 0).Row


'copy the data to the ws1
ws1.Cells(CellRow, 3).Value = ws2.[E4].Value
ws1.Cells(CellRow, 4).Value = ws2.[E15].Value
etc..............

==============================

Problem:

Rows 1 thru 255 of ws1 have data in them and row 256 is found by code
to be the "first empty row." Also have a few formulas in several
columns of ws1 in the 255 rows.

Before copying from ws2 to ws1, I would then want to copy row 255,
formulas only, to row 256. Row 256 would still be chosen as first
empty row because column 3 of 256 would still be blank.
I am at row 255 with formulas because of typical copy/paste, but would
like to do automatically with macro.


Thanks

Hi,

I can use the MATCH function when i am in excel but i am having trouble getting it to work when i am trying to insert the formula using VBA.

Here is the code i have but it wont even compile........not sure whats wrong.


Code:

Set junkrange = Workbooks(File_Name).Worksheets(sht_name).Range(1 & ":" & 1)
junk1 = "%_Grade"

Workbooks(Gradebook_Filename).Worksheets(Gradebook_Sht_Nm_Roster).Range("G" & Z).Formula = _
                        "=Application.Match(" & junk1 & "," & junkrange & ", 0)"

Anytime I insert a hyperlink, I want to insert a period "." in the cell beside it so that the hyperlink doesn't run across the screen. Any idea how to do this automatically?


Or can I format the cells so that the hyperlink doesn't extend into the next cell somehow?

Hi there,

I have data/time data and speed data in columns A and B as below.
Code:

 Date 	              Speed
28/03/2011 08:08:23	0
28/03/2011 08:08:26	12
28/03/2011 08:08:31	43
28/03/2011 08:08:37	50
28/03/2011 08:08:41	51
28/03/2011 08:08:47	51
28/03/2011 08:08:51	49
28/03/2011 08:08:57	45
28/03/2011 08:09:01	45
28/03/2011 08:09:07	50
28/03/2011 08:09:11	52
28/03/2011 08:09:17	24
28/03/2011 08:09:21	0

You will see that the speed is logged every few seconds approx 3-6 secs. I would like to insert rows so that there is a row for every second. I have a macro that does this (see below). However I also need to insert speed values in the new blank rows also based. I need the new values to be evenly spread between the actual values that were recorded. So it would probably have to look at the difference between the two original speed values and divide it by the number of rows that were inserted plus one. Here an example of what I hope it can do

Code:

 
 Date 	               Speed
28/03/2011 08:08:23	0
28/03/2011 08:08:24	4
28/03/2011 08:08:25	8
28/03/2011 08:08:26	12
28/03/2011 08:08:27	18.2
28/03/2011 08:08:28	24.4
28/03/2011 08:08:29	30.6
28/03/2011 08:08:30	36.8
28/03/2011 08:08:31	43

Heres the macro that inserts the new rows for each second. It would probably best to modify it.

Code:

Sub Insertrows()
Dim i As Long, j As Long
Dim FR As Long: FR = 2
Dim LR As Long: LR = ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
Dim Str As String
Dim Tm1 As Date
Dim Tm2 As Date
Dim Tm3 As Integer
Dim Dif As Double
Dif = 1 / 24 / 60 / 60
For i = LR To (FR + 1) Step -1
      Tm1 = Cells(i - 1, 1)
      Tm2 = Cells(i, 1)
      Tm3 = DateDiff("s", Tm1, Tm2)
      
      If Tm3 > 1 Then
            For j = (Tm3 - 1) To 1 Step -1
                  Rows(i).Insert Shift:=xlDown
                  Cells(i, 1) = Tm1 + (Dif * j)
                  Cells(i, 1).NumberFormat = "dd/mm/yyyy hh:mm:ss"
            Next j
      End If
Next i
End Sub

I hope you can understand.

All help appreciated

John

I am currently attempting to create a macro that does the following:

-Delete first 6 rows in the spreadsheet
-Delete all of the rows following the last name entry
-Insert a column
-Title the column "Week"
-Allow the user to identify the week number
-Copy that number down the column

While I can record simple macors, the needs of this particular macro are a bit beyond my understanding. Any help with this is greatly appreciated.