Pull Multiple Column Data from One Sheet to Another

[Katterman]

Hello Excel Gurus

I've searched many similar threads and sites for help on this but even though some similar questions have been posted and answers given, i can't seem to find a scenario that works best or that my limited VBA experience has been able to adapt. I Have tried the record macro approach as well but since not copying and pasting Column to Column (see below) it fails. I always do my best to research before posting to try and avoid wasting peoples time if the answer is already here.

I have a sheet (CleanIt) that contains cleaned up data (done daily so the "end Rows" would not always be the same) that i want to pull the data from 4 columns and paste into different columns (starting in row 2 to avoid header overwrites) on a different sheet (For this example the sheet is called "Work In Progress" but each day would be a different sheet due to data is gathered Daily and the Last Rows would always be different on a daily basis)



[TABLE="class: grid, width: 900, align: left"]
<tbody>[TR]
[TD]Sheets("CleanIt")[/TD]
[TD][/TD]
[TD]Sheets("Work In Progress") [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]*** This is a Table (Table3) That Ranges J1-> AF5000 ***[/TD]
[/TR]
[TR]
[TD]Column "A" up to Last Row[/TD]
[TD]Copy to[/TD]
[TD]Column "AD" Staring in Row 2 (To Leave headers untouched)[/TD]
[/TR]
[TR]
[TD]Column "B" up to Last Row[/TD]
[TD]Copy to[/TD]
[TD]Column "M" Staring in Row 2 (To Leave headers untouched)[/TD]
[/TR]
[TR]
[TD]Column "C" up to Last Row[/TD]
[TD]Copy to[/TD]
[TD]Column "J" Staring in Row 2 (To Leave headers untouched)[/TD]
[/TR]
[TR]
[TD]Column "D" up to Last Row Value of A, B or C[/TD]
[TD]Copy to[/TD]
[TD]Column "N" Staring in Row 2 (To Leave headers untouched)[/TD]
[/TR]
</tbody>[/TABLE]





** Note, The "Last rows" of Data would all be the same for Columns A, B and C with No Blank Cells but Column D would have some blank cells within the column (and want to be kept) and would "technically" end at the same Last row as the other columns albeit there many not be data in the last row(s) of column (D).

The sheet i have can't be uploaded due to sensitive data but If required, i could build a sample workbook but was hoping the above would be OK. If Not, I understand

Thanks in Advance for any and all replies.
Your teachings are most Valued

Scott

 

[bschwartz]

Why not simply reference those cells?

For instance, these formulas on the sheet Work In Progress, and fill all the way down:

J2 =CleanIt!C2
M2 =CleanIt!B2
N2 =CleanIt!D2
AD2 =CleanIt!A2

 

[Katterman]

Hello Ben. Thanks for your reply.

I did think if that but the sheets the data is ending up on would be different each day as there will be a data sheet for each day of a monthly workbook. The "cleanit" sheet would have different data pasted in daily that us cleaned up by other macros and then pulled onto the sheet for that day.

Thanks again for replying

Scott

 

[bschwartz]

How about this macro?

Sub Copy_Data()
'
' Copy_Data Macro
'

'
Range("J2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FormulaR1C1 = "=CleanIt!RC[-7]"
Range("M2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FormulaR1C1 = "=CleanIt!RC[-11]"
Range("N2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FormulaR1C1 = "=CleanIt!RC[-10]"
Range("AD2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FormulaR1C1 = "=CleanIt!RC[-29]"
End Sub

 

[Katterman]

That Works Perfectly Ben

Thank You So Much!

Scott

 

[Katterman]

Opps, Spoke to Soon.

Upon Further Testing, This is Not working how i need it to.

This code still leave the formulas on the sheet that references the "CleanIt" sheet.
When The Data changes on the "CleanIt" sheet the next day of reporting, the
original data that was pulled is replaced.

Just to clarify how this workbook will function. There is a new workbook for each Month.
Each Monthly workbook will have 30 or 31 "Sheets" (Each Labeled as a Date of that month. I.E. Sept 1, Sept 2 etc)
There will also be the single "CleanIt" sheet that data from another CSV file (Different Each Day) is pasted in and "cleaned up" to remove non essential stuff.
Back on the sheet for that particular date (Say Sept 1), i want to pull the data from the columns noted above in "CleanIt" and paste in in that particular dates sheet (Sept 1).
The Next day (Sept 2), the "CleanIt" sheet will be cleared and new data put in to eventually be pulled into the sheet for that new date (Sept 2).

Hope that helps.

Thanks Again

Scott

 

[bschwartz]

How about this?

Sub Copy_Data()
'
' Copy_Data Macro
'

'
    Sheets("CleanIt").Select
    Range("C2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Work In Progress").Select
    Range("J2").Select
    ActiveSheet.Paste
    Sheets("CleanIt").Select
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Work In Progress").Select
    Range("M2").Select
    ActiveSheet.Paste
    Sheets("CleanIt").Select
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Work In Progress").Select
    Range("N2").Select
    ActiveSheet.Paste
    Sheets("CleanIt").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Work In Progress").Select
    Range("AD2").Select
    ActiveSheet.Paste
    Range("J1").Select
End Sub

 

[Katterman]

Thanks again Ben

The "work in progress" sheet will have a different name each day of the month (Sept 1, Sept 2 etc).
The current name "work in progress" in just a temp name while I build up the workbook's code.
The "Cleanit" sheet will be static.

Thanks again

Scott

 

[bschwartz]

I was just recording macros, but this is getting beyond my limited VBA skills. At this point, I think you need help of a real VBA user.

 

[Katterman]

No Worries Ben

Thanks for your Input as it was helpful just not the final result i needed.

Hopefully someone else will jump in here.

Thanks again for you time and efforts.

Scott