Thank you so much for the help!
Since it uses the offset to copy and not paste, how can I modify it so when it pastes it only pastes the value and not the format?
Wow, thank you. It works.
So you used Autofilter instead and then copied those over? Is that correct?
Thank you again.
This seems to work (it pastes it into the second row)Code
Dim c As Range Dim j As Integer Dim Source As Worksheet Dim Target As Worksheet ' Change worksheet designations as needed Set Source = ActiveWorkbook.Worksheets("Source") Set Target = ActiveWorkbook.Worksheets("Dest") j = 2 For Each c In Source.Range("A1:A200") If c = "Task Header" Then Source.Rows(c.Row).Copy Target.Rows(j).PasteSpecial Paste:=xlPasteValues j = j + 1 End If Next c
I just have to figure out why it is not expanding the table when it pastes the data.
Also, right now it's pasting columns A and B still.
Thanks for your suggestions. Wouldn't it need to loop through each row in the Source sheet to find that keyword and then copy though?
I'd like it to copy the cell in B that matches a keyword in column A, but there could be 1 or many of those keywords in column A.
And the keyword = "Hi"
In Destination sheet it should have copied:
(And destination sheet has a table).
Thanks for the help!
It's late and I can't think anymore.
I am trying to look for a keyword in Sheet1 Column A and if found, I need it to copy that row and paste it into a table in Sheet2.
The problem is it keeps pasting it outside the table in sheet 2. I'm wondering if someone can help me troubleshoot why.
So what it is doing is, if I have 2 rows in Sheet 2 (row 1 being the header row and row 2 the first row of the table), it pastes it into Row 3 which isn't a part of the table. I need it to paste into row 2.
Here is the code:Code
Sub Testmacro1() Dim c As Range Dim Source As Worksheet Dim Dest As Worksheet Set Source = ActiveWorkbook.Worksheets("SourceSht") Set Dest = ActiveWorkbook.Worksheets("DestSht") For Each c In Source.Range("A1:A" & Source.Cells(Rows.Count, 1).End(xlUp).Row) If c = "Header" Then c.EntireRow.Copy Dest.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues End If Next c Application.CutCopyMode = False End Sub
Also, is there a way to only get it to copy Column B and not the entire row?
Thanks in advance,
How do I delete this post? I do not need this vba anymore. I ended up just recording the steps in a macro.
I need some help. I have a sheet (Sheet 1) that will have a list of ids and items in them.
In another sheet (sheet 2), I have a list of 5 steps that need to be done to each of the ids/items in sheet 1.
In sheet 3, I want to copy the steps (it's a set number) however many times an unique id is present in sheet 1.
Sheet 1 table:
ID Items 1A Chair 1B Table
Sheet 2 table:
Step Details Number Category Enter Description here 1.0 A Enter Manufacturer here 2.0 B Enter Requester here 3.0 C
Sheet 3 should end up looking like this: (Sheet 3 can start with a blank table with just the header)
ID Items Step Details Number Category 1A Chair Enter Description here 1.0 A 1A Chair Enter Manufacturer here 2.0 B 1A Chair Enter Requester here 3.0 C 1B Table Enter Description here 1.0 A 1B Table Enter Manufacturer here 2.0 B 1B Table Enter Requester here 3.0 C
Is this possible? Can anyone please help?
Thanks in advance!
I'll use DateValue on them. Thanks everyone for the help!
No, it's supposed to be January 1, 2018 so the 1st of each month.
It's converting the dates incorrectly
The actual formula actually looks like this:
where in N$2 is where the Date header is.
So I have to put DateValue everytime I reference the header field?
It's the formula that's not evaluating correctly. So maybe it's not the header?
Here's one with a simple formula on dates. It's evaluating to "Yes" even though it should be "No". ?
What am I doing wrong?
We have 2010
Here are the headers for the original file - these ones work as normal range.
They are reading as dates.
When I converted it to a table, the dates read weird. You said January 2, 2018. It's actually supposed to be January 1, 2018.
For some reason, I think it's reading the headers as text, not dates, even though it looks like dates. No matter what I did, it wouldn't convert.
I need to do date comparisons with it but it's not calculating properly because of that :(.
Thanks in advance, Carim.
I'm losing my mind here.
I had a normal range I converted to an Excel table with the headers. Most of the headers are dates.
No matter what I try, I cannot seem to convert them to read as dates!
I'm not sure why. I need them as dates as they are used in formulas.
I've attached the table (with the data cleared). I just need the headers from K onwards to read as dates.
Can someone please tell me what I'm doing wrong? I've been googling for a while now and gave up.
hi Carim and KjBox,
Thank you for your quick replies!
I really, really appreciate it!
I can confirm they work.
In the end, I went with the UDF as it is more flexible. Because it's in a table that keeps on 'changing' (its data refreshes from an external source).
Thank you again and I hope you both have a great day!