VB Code

R

RP121

Board Regular
Joined
Apr 22, 2005
Messages
128
Having some minor problems with some code, the code below when input a date into a input box, picks out the whole line of data within a workbook and copies it over into a other.

What I require is that currently when the data is copied pastes over to cell A2, I want it to start at M2 instead and move therefore in column M, also there seems to be a problem with the loop, it only picks out one single line of data when a date is inputted when I know there are around another 100 lines to be copied, don't think its lopping through all the data.


Sub Update_Button()
Dim x, y As Range, rThis As Long, rPrev As Long
x = InputBox("Enter Date of Update")
x = CDate(x)
ChDir "S:\Broker"
Workbooks.Open Filename:= _
"\\24\opsales\Broker\Total.xls"
With Workbooks("Records_File")
Set y = .Sheets("sheet1").Cells.Find(x)
rThis = y.Row
rPrev = rThis - 1
Do While (rPrev < rThis)
Windows("Summary 2005.xls").Activate
With .Sheets("Sheet1")
y.EntireRow.Copy .Cells(.[A1].CurrentRegion.Rows.Count + 1, 1)
End With
Set y = Cells.FindNext(y)
rPrev = rThis
rThis = y.Row
Loop
End With
MsgBox ("Update Complete")
End Sub
Code: 
Hope you can help
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
Hi, RP121,

this cannot loop !!
Do While (rPrev < rThis)
Windows("Summary 2005.xls").Activate 'don't put this within the loop
With .Sheets("Sheet1")
y.EntireRow.Copy .Cells(.[A1].CurrentRegion.Rows.Count + 1, 1)
End With
Set y = Cells.FindNext(y)
rPrev = rThis
rThis = y.Row
Loop

y.EntireRow.Copy .Cells(.[A1].CurrentRegion.Rows.Count + 1, 1)
to copy to column M you need to change the last 1 into 13
but you will need to change also EntireRow to something else like
y.Resize(1,12).Copy, because you can only paste an entire row to column A

are you getting further with this ?

kind regards,
Erik
 
Upvote 0
I haven't got the sample data to test the code yet, but will get back to you, I just wanted to check as you had said not to include the code:

Code: 
Windows("Summary 2005.xls").Activate 'don't put this within the loop 

[code]

Will the code still run through and copy the data into this workbook, this is the same workbook where the button starts off

Many Thanks
 
Upvote 0
RP121,

Windows("Summary 2005.xls").Activate
you don't need to put this in the loop if the window is not changed
you can put it just before
(it doesn't make a visible difference, but it's just good practice)

I don't know if you were asking something else? Then it was not clear to me.

kind regards,
Erik
 
Upvote 0
Sorry for the delay in replying I've tired many ways to amend this code so it will work properly, but I'm still unsucessful even after I changed the code the way recemmended it, my first problem is that it's still not picking up all the lines of data which I require after I entered a date into a input box, it still only finds the first line and copys it, and also the problem which copying into cell M works okay but as I have formuales in columns A to L the data seems to copy after the formuales, so for example I require it to copy over to M1 but as columns A1 to L1 have formulas it copies over to M2.

Hope you can please help
 
Upvote 0
RP121,

please post the code you're working with now (please use codebutton) and a simple sample sheet. (suppose you can use the HTMLmaker)

kind regards,
Erik
 
Upvote 0
Below is the code which I'm using, not sure how to send a sample sheet please can you explain

Code: 
Dim x, y As Range, rThis As Long, rPrev As Long
    x = InputBox("Enter Date Of OTB Update")
    x = CDate(x)
    
    ChDir "S:\OT Brokerage"
    Workbooks.Open FileName:= _
    "\\w2k6024\n105opsadmin\OT Brokerage\OT Records_Total.xls"
    With Workbooks("OT Records_Total")
    Set y = .Sheets("sheet1").Cells.Find(x)
    rThis = y.Row
    rPrev = rThis - 1
    Do While (rPrev < rThis)
    If y.Column = 14 Then
    With Workbooks("OT Brokerage Summary 2005.xls").Sheets  ("OT_Sales")
    y.Resize(1, 13).Copy .Cells(.[A1].CurrentRegion.Rows.Count + 1, 13)
    End With
    End If
    Set y = Cells.FindNext(y)
    rPrev = rThis
    rThis = y.Row
    Loop
    End With
    ActiveWorkbook.Close savechanges = False
    
    'Windows("OT Brokerage Summary 2005.xls").Activate
    MsgBox ("Update Complete")
    End Sub
 
Upvote 0
Upvote 0
Below is the source data requested, I you can see what I'm trying to isonce the date is inputted in the box, it should pick up this date form column N and copy the whole line of data and paste it into my spreadsheet at column M2 and loop until no further data it availble.

*Removed by Admin*

Many Thanks
 
Upvote 0
Edit by NateO: I had to remove the html to get this thread functional again, can you repost. Perhaps a smaller snippet? Thanks. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Using Sheets().Select in a Worksheet_Activate() macro
Replies
5
Views
226
RoryA
RoryA
K
  • Solved
Next Blank Cell - Table
Replies
3
Views
349
Alex Blakenburg
A
A
  • Question
What is wrong with this VBA code: userForm get hand ico for pointer on hover
2
Replies
15
Views
1K
RoryA
RoryA
E
  • Question
Interest Due Log
Replies
7
Views
385
ExcelNew7
E
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
991
AndyEd
A

Forum statistics

Threads
1,221,638
Messages
6,160,994
Members
451,682
Latest member
ogoreo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top