Transfer Data from one Workbook to Another

[2ks]

I need a macro that will transfer lines of data information form one sheet (in a workbook = Workbook1) to another sheet (in another workbook- Workbook2).

The headings in each workbook are mirror images.

I could do it easily enough if I was guaranteed the same number of lines every day in Workbook 1 but that will not be the case. SOme days 10 lines, the next could be 30 lines.

I need a macro that will count the the exact number of lines of data and then insert that same number of lines in the other workbox, shifting old lines down accordingly.

At the same time the new lines should be allocated a sequential reference number.

Any help please and thanks

 

[GlennUK]

What have you tried so far?

Have you looked at these faqs to help you:
How can I identify UsedRange OR last/first column/row etc. faq707-2112 Loomah
How do I find the REAL last used cell faq707-2115 xlbo




Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[2ks]

Glenn.

Sorry tok a while to get back but not had chance to look at this.

I have followed the links and produced this

Sub FindUsedRange()
Dim LastRow As Long
Dim FirstRow As Long
Dim LastCol As Integer
Dim FirstCol As Integer

' Find the FIRST real row
FirstRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlNext, _
SearchOrder:=xlByRows).Row

' Find the FIRST real column
FirstRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlNext, _
SearchOrder:=xlByRows).Column

' Find the LAST real row
LastRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row

' Find the LAST real column
LastCol = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column

'Select the ACTUAL Used Range as identified by the
'variables identified above
ActiveSheet.Range(Cells(FirstRow, FirstCol), _
Cells(LastRow, LastCol)).Select



Application.WindowState = xlMinimized
Application.WindowState = xlNormal
ActiveWindow.WindowState = xlMinimized
Windows("AMF2.xls").Activate
ActiveWindow.WindowState = xlMaximized
Sheets("AMF").Select

Dim lRow As Long
' Find the FIRST EMPTY row by adding 1 to the last row
Sheets("AMF").Select
lRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row + 1

'Paste the data into the first
'COMPLETELY empty row
ActiveSheet.Paste Destination:=Cells(lRow, 1)

End Sub

It goes no futher than this command in the middle

ActiveSheet.Range(Cells(FirstRow, FirstCol), _
Cells(LastRow, LastCol)).Select

If i take it out it actually drops information into the new workbook but its not the information I need. Instead its the last thing I physically cut out.

What am I doing wrong

 

[GlennUK]

There's a typo in the code ( the typo is in the FAQ ... which I've reported ). Try changing

  ' Find the FIRST real column
    FirstRow = Active....

to

  ' Find the FIRST real column
    FirstCol = Active....

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[2ks]

Glenn

Many thanks..All sorted and working like a dream.

Til the next time eh!! Big project working on