Data extraction between worksheets

[Deanuy]

Hi all,

Been struggling with this problem for the past few weeks, and although I've found a few solutions which are similar in nature, I have tried and cannot adapt them to what I need

- I'm trying to copy data from specific columns from a worksheet that will always be named the same, and will always be in the same location (e.g. C:/Docs/filename.xls). The data will always be in columns A,C,F&G, but the number of rows required (ie. entries per column) will vary.

If possible, i would like the macro to check how many entries there are, and only copy that number of rows (the rows that do not have entries will just be blank

- Once copied, I would like the data to be pasted into the ACTIVE CELL in my current open workbook (ie. the cell that is selected when I run the macro as pictured)[TABLE="width: 500"]
<tbody>[TR]
[TD]Data *selected Cell*[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
</tbody>[/TABLE]

Thankyou so much (hopefully)! Any help would be appreciated

 

[Deanuy]

Sub Monday()


'Assign variable name to Target workbook
Var1 = ActiveWorkbook.Name
Var1S = ActiveWorksheet.Name
'Assign variable name to Target range
Var1R = ActiveCell.Name

'Open Source WorkBook
Application.Workbooks.Open ("C:\*DIRECTORY*")

'Assign variable name to Source workbook
Var2 = ActiveWorkbook.Name
Var2R = "WORKSHEET-1"

'Copy from Source to Target
Sheets(Var2R).Range("E19:H27").Copy _
Destination:=Workbooks(Var1).Sheets(Var1S).Range(Var1R)

'Close Source WorkBook wo/Save
Workbooks(Var2).Close False

End Sub

This is the code I have so far, but I cannot figure out how to reference the active cell and active sheet properly (and then get the paste function to work)

 

[Deanuy]

Sorry guys, have Figured out the copying bit - still trying to sort out how to only copy the bits with data in it (and not the blank cells)